JOIN types in a query

[jyadayada]

I have a query of 5 tables, the primary table holds 600k records, the query is to emulate this primary table but adding additional fields from the other 4 tables based on joined fields, SiteID and StockID


my query is returning over 650k records and I'm guessing it's based on the join types?


My query should return the same number of records as the original primary table just with the additional fields

 

[theDBguy]

Hi,

When you join two tables in a query, Access will return "all" matching records. If you're getting more than what you expected, my guess is a (or more) record from the first table is matching more than one record in the second table. Otherwise, the common result is you'll get less records - not more. So for starters, how many records are in the first table and how many records are in the second table?

 

[jyadayada]

you're right, I've just found some duplicates in one of the child tables so it was doubling records, but now I'm getting less records than the primary table, I'm just doing a clash to see where the holes are coming from


thanks for the input, How do I close a thread btw?

 

[theDBguy]

Hi,
As I said earlier, less records is a more common result. Have a look at this blog article to see if it helps clarify any. To "close" the thread, you can go to the "Thread Tools" dropdown at the top menu.

Good luck with your project.