By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,494 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Access multiplies my query count by 38... why?

P: 23
ID - InputID

1 - data1
2 - data2
3 - data2
4 - data2
5 - data6
If I run a query that produces the count of each data input:
Expand|Select|Wrap|Line Numbers
  1. SELECT First(InventoryData2.InputID) AS FirstOfInputID, Count(InventoryData2.InputID) AS InputItemCount FROM InventoryInputT, InventoryData2 GROUP BY InventoryData2.InputID HAVING (((Count(InventoryData2.InputID))>=1)); 
Which, based on the data, SHOULD provide something along the lines of

data1: 1

data2: 3

data3: 1

HOWEVER, access shows my count totals as:

data1: = 38

data2: = 114

data3 = 38

ANY IDEA WHY?
May 2 '17 #1

✓ answered by Seth Schrock

When there is no join specified between the tables, then it ends up multiplying the record counts, so I'm guessing that you had 19 records in one table and 2 in the other. If you specify a join field (primary key/foreign key match), then it will match records together.

Share this Question
Share on Google+
3 Replies


P: 23
I was informed on another forum that the issue arrised from the SQL code that contained "FROM InventoryInputT, InventoryData2"... that effectively compared ALL rows from both tables and presented the data.

Removing the InventoryInputT from that FROM search fixed the issue!
May 2 '17 #2

Seth Schrock
Expert 2.5K+
P: 2,930
When there is no join specified between the tables, then it ends up multiplying the record counts, so I'm guessing that you had 19 records in one table and 2 in the other. If you specify a join field (primary key/foreign key match), then it will match records together.
May 2 '17 #3

NeoPa
Expert Mod 15k+
P: 31,186
For a fuller explanation look up "Cartesian Product" as that's the result when including two tables without specifying any type of JOIN.

NB. It's well worth remembering that you can have the same issue, that you refer to as multiplying, when multiple tables are joined together with specific JOIN types. How this area works should be properly understood before use if you hope to avoid such confusion in future.
May 3 '17 #4

Post your reply

Sign in to post your reply or Sign up for a free account.