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

[TylerBennett]

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:

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?

[TylerBennett]

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!

[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.

[NeoPa]

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.