Unique Combination

[FlashManAB]

I have thought about this for days now (and nights) and I have simplified my problem below. (I even bought a book)
I have three tables: 1, 2 and 3.
Table 1 = a, b and c
Table 2 = x, y and z
Table 3 combines table 1 and table 2; ax, ay, az, bx, by, bz, etc.
Table 1 and Table 2 are both One to Many with Table 3.
The Table 1 field and Table 2 field are a ‘unique’ index combination.
My Problem:
It would be very handy to produce a list of the ‘unique’ index combinations that are not in Table 3. That is, the ‘unique’ index combinations that aren’t used yet.
You could do this by trial and error, but is there a Query you can run that will give you a result that you could then Append?
An Unmatched Query on the Table 3 / Table 1 contents will point out a complete lack of either a, b or c as will the same Query on Table 2 for missing x, y or z.
A query with the two non-linked tables will give every combination, but is this the best way to do it? It works by default when you use it to append table 3, but is it the best way to do it?

 

[Rabbie]

Unable to open your database. What version of Access is it in. A lot of us don't have Access 2007 yet so it would help if you can paste it again in an earlier format A2000 or A2003.

 

[FlashManAB]

Hi Rabbie, I hope this works.

 

[Rabbie]

Thanks for the database.

I am afraid I don't think your relationships are correct. You are linking the Table A id with the full name in table 3. so there will never be a match. I am afraid I am not sure what you are trying to acheive because in the example table 3 already has all possibilities in it. Can you clarify your objective please.

 

[FlashManAB]

Hi Rabbie, the two tables 1 and 2 each have a one to many with table 3 but are unrelated to one another. The fields in table 3 each lookup to table 1 and 2 and combined are a unique key in table3, you just have to find the unused combination.
If I create a query of the fields from table 1 and 2, they appear in every combination and therefore emulate every unique key from table 3.
I am wondering if this query is the best way for the database to determine what combinations remain unused from the unique combination key in table 3.
It works by default.
I know that all combinations already exist in the table 3 that I posted.
Add some data to both table 1 and 2 and extend them by a few records.
Should I simply use this as an answer to my own question, or is there another way of doing it?

 

[Rabbie]

Hi

The relationships linking Table 1 with Table 3 and Table 2 with Table 3 are INCORRECT. They dont mean anything and just cause confusion. You cant match a numeric ID with a text field in a relationship.

I am still confused as to what you are trying to do here.

You are in effect using table 3 as a junction table to create a Many to Many relationship between Table 1 and Table 2. Since you say there is no relationship between these tables it is not easy to understand the the business model behind your design.

 

[PeterF]

I agree with Rabbie and think you explained your question wrong.

To check for possible free unique combinations you need two queries,
one to get te possible unique combinations (almost your query1)

SELECT DISTINCT Table1.FullName, Table1.ID, Table2.Class, Table2.ID
FROM Table1, Table2;

And one to see which combinations are not used in table3:

SELECT Query1.FullName, Query1.Class, Table3.FullName, Table3.Class
FROM Query1 LEFT JOIN Table3 ON (Query1.Table2.ID = Table3.Class) AND (Query1.Table1.ID = Table3.FullName)
WHERE (((Table3.FullName) Is Null)) OR (((Table3.Class) Is Null));

I didn't check for requirement of entering data in your fields so checking for null can be wrong here.


But what purpose have the ID fields, and what's the goal of this?

 

[FlashManAB]

Hi Rabbie and Peter F, I am definitely not good with SQL. Could you please explain how Peter F's Query1 and Query2 works? I can see that they work but I can't quite grasp why...
Also Rabbie, I am not sure why you said:
"The relationships linking Table 1 with Table 3 and Table 2 with Table 3 are INCORRECT. They dont mean anything and just cause confusion. You cant match a numeric ID with a text field in a relationship."
Why are they incorrect and why confusion?

 

[Rabbie]

Hi Rabbie and Peter F, I am definitely not good with SQL. Could you please explain how Peter F's Query1 and Query2 works? I can see that they work but I can't quite grasp why...
Also Rabbie, I am not sure why you said:
"The relationships linking Table 1 with Table 3 and Table 2 with Table 3 are INCORRECT. They dont mean anything and just cause confusion. You cant match a numeric ID with a text field in a relationship."
Why are they incorrect and why confusion?

Because you had linked the table ID field(numeric) in table 1 with the fullname(text) in Table 3. This can never match so it is meaningless. Similar error in link from Table 2.

You should either put a field in table 3 for the table1ID field and link to that or link the Fullname field in table1 to the fullname field in table3.

You might find it helpful to read up on relationships in a book about Access.

 

[neileg]

Rabbie, I think you're up the wrong track here. FlashmanAB has not adopted the usual naming convention for foreign keys, but otherwise it's OK.

FlashmanAB, you have correctly identified how to get all the possible combinations in your query by having no join. You can then use the Unmatched query wizard to compare your data. This will generate a query much like PeterF has already given you.

 

[Rabbie]

Neil
In the attached database fullname in Table 3 contains "a". The ID field in table1 is an autonumber field. The relationship diagram shows a link between these two fields so I still feel what I said was correct. Please let me know where I am wrong.

 

[neileg]

It's because there are table level lookups. When you look at the data in the table, what is displayed is the result of the lookup, not the data that is really stored in the table. It's one of the many reasons why I hate table level lookups.

 

[Rabbie]

Thanks for your reply Neil. I am never too old to learn. It just shows the value of following the advice on this forum.

I knew I had a good reason to never to use table look-ups. It just makes things difficult to follow

 

[neileg]

Happy to help.

There's a sporting chance I'm older than you, mind!

 

[FlashManAB]

Hi Rabbie and Neileg. Thanks for the help. I have another question. "Table-level lookup"? If you don't mind explaining what you mean. I have always used them. What is the alternative? Also, what naming convention should I have adopted? Do you have a link to help me learn the lingo?

 

[RoyVidar]

Table level lookups - lookup fields in the table - check out http://www.mvps.org/access/lookupfields.htm which detail most of the issues with them.

What to use in stead? Since you should never allow any user near your tables, but have them enter/alter information through forms, you should use combo boxes on forms in stead. There's a very nice combo box wizard helping through the process. This gives you all the "power" of easy to use selections, without all the trouble the table level lookups give.

Naming convention.
Traditionally, Access developers use some part of the so called Reddick Leszynski naming convention, see for instance http://www.mvps.org/access/general/gen0012.htm. BasicallyBasically, names of objects of different will usually get a prefix indicating object type. For foreign keys, there are of course several options. I think that using the exact same name as the primary key in the referenced table, is probably the most common. Some would add a prefix or tag signifying that it is indeed a foreign key, other will stuff even more into it.

The important part isn't which naming convention to use, but choose one, and stick to it. That makes it easier when you're going back to maintain it. Choosing a convention close to what other people uses, will make it easier for other developers to maintain the app, too.

 

[Rabbie]

Happy to help.

There's a sporting chance I'm older than you, mind!

Then I as a mature individual must bow to the wisdom that comes with age.

 

[FlashManAB]

Thanks for pointing me in the right direction. It's funny how you become used to doing things even if it is not the right way.
You learn something new every day!