GeXus wrote:Califdon, thanks for your help... that's a good answer... I think I'll just stick to using multiple queries.. I suppose I could get rid of the groups table, and add a group_id to the categories table... but only maybe 5-10% of the categories are in groups... Do you think that would provide faster querying? I don't care much about insert.. this will be primarily all selecting..
Sure, that would simplify the queries, but it would be somewhat clumsier to maintain, such as adding a new grouping--you'd need to modify several category records. If there's much changing going on in the groupings, this could be an issue.
Today I sent out the monthly announcement for our local Access User Group meeting, to be held a week from Friday. I included this in the announcement (just for you!!

):
In an Access forum I follow, someone had a question about a query against three related tables. I tried to provide an answer, but soon found that I couldn't figure it out, either. It seems not too difficult at first, but I still can't quite see my way through it. I suspect the problem is with the schema, but I can't see how to design it any better. Here's the issue, if you have time to think about it, and we may discuss it at the meeting:
We have a table of URLs:
tblLinks:
id (PK)
url
cat_id (FK)
and a table of categories:
tblCats:
cat_id (PK)
cat_name
Easy, so far. Every URL is linked to a named category. Simple to list all URLs in a category.
But he wants to be able to arbitrarily group categories into 'clusters' and query all URLs that are in ANY category that is in the same group. He created a third table:
tblGroups:
grp_id
cat_id
What kind of a SQL statement (if any) could return all URLs that are in any category that is grouped with a selected cat_name?? It's harder than it sounds--at least for me!
So if you can wait that long, maybe someone will come up with something.
Oh, and yesterday at lunch with a couple of colleagues, I discussed your problem, too, and I
just now received this reply from one of them, a DBA who manages a large Access application:
You explained the problem well.
If the URL is given, the statement should be:
SELECT tblLinks_1.url
FROM ((((tblLinks INNER JOIN tblCats ON tblLinks.cat_id = tblCats.cat_id) INNER JOIN tblGroups ON tblCats.cat_id = tblGroups.cat_id) INNER JOIN tblGroups AS tblGroups_1 ON tblGroups.grp_id = tblGroups_1.grp_id) INNER JOIN tblCats AS tblCats_1 ON tblGroups_1.cat_id = tblCats_1.cat_id) INNER JOIN tblLinks AS tblLinks_1 ON tblCats_1.cat_id = tblLinks_1.cat_id
WHERE (((tblLinks.url)=[Given URL:]));
He sent me these two screen shots of his QBE grid. I haven't had a chance to try them, myself, but as we had discussed at lunch, they involve using the same table more than once in the same query, a technique I have rarely had occasion to use. You will see that one of them is for finding by url and the other by category name.
