Cannot query if a record doesn't appear in all tables...

[kilou]

Hi,

I'm new to MS Access and I'm trying to setup my first database. I can summarize the situation as follow. The database contains the following tables:
T_Batch: contains ALL the batches and general batch information
T_Analyze: contains all the batch that have been analyzed with analysis info
T_Release: contains all the batches that have been released with release info

These 3 tables all contain BatchNo as primary key and are related with one-to-one relationships through that key.

Although each table contain different information, obviously T_Release is a kind of subset of T_Analyze which itself is a subset of T_Batch in term of BatchNo. It means that batch n° 133 will always appear in T_Batch but will appear in T_Analyze only when it get analyzed, and in T_Release only when it get released. I prefer this setup as having everything bound in the same table because some batches will get analyzed but will never be released etc

I now want to build a query based on these 3 tables to show BatchNo, AnalysisDate and ReleaseDate side by side (so that I can perform a calculation with these dates once my problem is solved). The problem is that the query displays only batches that appear in all 3 tables (basically it will show only released batches). It doesn't display batches that are missing in one table.

What I'd like is that if the batch is not released (ie the batch is not present in T_Release), I get a blank field for ReleaseDate but all other info should appear in the query.

Does anyone know what's the problem here? Is this a table design issue?

Many thanks

 

[Banana]

In query builder, if you click the line connecting the table twice, you'll get an dialog asking you how you want to join the table. The default is to join only when both value from both side are equal, but you can also specify to join all records from one table and only those where there is matching value or vice versa.

Try and experiment, see if this helps.

 

[dkinley]

If I may go a step further for clarification ...

Because of the order of precedence ... what is occuring is you have data in one table (T_Batch) but not in the other tables. When you run your query, this no data is null and those records will not be returned in your query.

In this instance, you need to adjust the default Banana referred to to show the date fields of all of the records (including those that have not been analyzed or batched) regardless if there is something in them or not.

-dK

 

[kilou]

Thanks Guys! I forget these joint settings and I'm now able to get the query working. However I still have 2 questions:

- Should I set the joint settings in the relationship view or only in the query view?

- I don't really know what the best way to bind these 3 tables. Since release batches must first be analyzed, should I bind T_Release to T_Analyze and T_Analyze to T_Batch like this:

or simply bind both T_Release and T_Analyze to T_Batch like this:

Thanks for your help!

 

[kilou]

Basically does this relationship path matter in queries or will it just work the same if all primary keys ae bound at the same table (like on 2nd picture)?

 

[Banana]

How you position your tables has no influence on how you will actually join the data. It may change the default Access assumes for specifying left join vs. right join, but you still override the default when you click on the line.

Both pictures show your tables participating in a INNER JOIN, meaning that query only will show record where it has a match in *both* tables.

LEFT JOIN (or RIGHT JOIN) means you select *all* records from the left-hand table, regardless if there's any match on the right hand table or not. If there weren't any match, Access will fill in the missing record as NULL which is displayed to you as empty fields.

Did that help?

 

[kilou]

Yes the pics did only illustrate how the table were linked but I'll select all links to be left joints. However I'm slightly surprised that the way tables are bound together would have no impact. For me pic1 seems more logical than pic2...

I also saw that after having setup the relationship in the relationship view and then creating a new query, tables were sometimes linked in all possible ways i.e. there were the same relationships as on the pics but also a "new one" on BatchID fields binding T_Batch to T_Release (for the 1st pic) or T_Release to T_Analyze (for 2nd pic). These new relationships in query view were added automatically without referential integrity. Should I keep them or just delete them? In fact if you consider the 1st pic, is there any need to have a relationship on BatchID between T_Release and T_Batch if these 2 tables are already linked through T_Analyze??? No need to bind all BatchID fields together in all possible ways then?

 

[Banana]

Ok, I need to backtrack a bit, now have realized that I was confused. I thought I was looking at the picture as represented in query builder, but now I realize they actually came from the relationship window.

That said, I have to say that one-one relationship are exceedingly rare. Usually, they just can be easily in same table.

Are any of those table supposed to be many side table; that is, for a given record in say, T_Batch, can there be several related T_Analysis, or exactly only one?

If the answer is exactly one, then you need to put all rows from T_Analysis in T_Batch.

If the answer is many, then T_Analysis needs a primary key, keeping BatchID as a foreign key. To do so, redesign your T_Analysis by inserting in a new row, call it AnalysisID then make it the primary key. You will now have a one-many relationship.

Apply same logic to the another table.

Now, said all that, I want to emphasize that relationship you define in relationship window are *not* same as joins you define in query builder, though Access represent them both as a line connecting tables. Think of this way:

Relationship defines constraint of the data.
Join defines how data can be pulled together.

Did it help?

 

[kilou]

Yes it helps a lot! I also thought about putting everything in the same table but in a sense it's also good to have separate tables with one-to-one relationship. Basically the T_Batch table is an import that contains batch information for all batches. Then some people will add information regarding analyzed batches (not all batches in T_Batch get analyzed) and other people will pour in info regarding release in T_Release (again not all analyzed batches will get released but to be released a batch must first be analyzed). Onle batch in T_Batch will have only one related batch in T_Analyzed or T_Released so it's true I could put everything in one table but then I'd have a lot of Null values...

By building separate tables with the same primary key (batchID), I thought it would better represent the different time steps in the process. Also, it would prevent from having to manipulate each time the T_Batch table (which will be huge) to work on analyzed or released batches only.

Does it make sense to separate the tables then or should I really put everything in the same table??? To me it sounds "better" to have each step separated in a different table although all are related to batchID but I still don't have the "Access way to think" So your opinion is more than welcome.

 

[Banana]

I quite understand where you are coming from regarding having a sparse table.

As I understand it, one-one relationship for this reason (sparsity) are pretty rare, and usually for extreme cases (e.g. 80% of data within those columns in question will be blank). If just 50% of rows are blank, it's not really worth splitting out into a one-one table.

Furthermore, you can just put it in one table, then use only queries to show what you need at the moment; you can have three queries that looks exactly same as the three table you have now, and use that as your forms' recordsource. This would be actually faster because it's easier & cheaper (in terms of computer resources) to filter only fields that has a value than to join two table together, especially if you use index correctly.

Did it help?

 

[kilou]

OK, I understand that. I will see if I can put T_Release and T_Analyze together but I'll leave T_Batch alone since it's already a large table and it is an imported table. Probably better to leave it alone.

Thanks a lot for your help Banana! I'm starting enjoying learning Access