Two Different Tables one Query

[Love Buzz]

Hello all.

John has been helping on another issue related to this one. Here is the deal:

1.) When I start a new query and bring in one table everything works fine.
I am adding the number of items processed for a period of time by User ID.

2.) When I introduce another table that contains data about errors by User
ID, the total items processed for those Users with errors is greatly
exaggerated (while those without errors is correct).

3.) I have not connected the tables or used any fields from the second table
in my query.

What would be causing this? Thanks for your help. Here's my SQL for the
query:

SELECT [December 4].UserID, Sum([December 4].Items) AS SumOfItems
FROM [December 4], Errors
WHERE ((([December 4].Date) Between [Start Date] And [End Date]))
GROUP BY [December 4].UserID;

 

[Duane Hookom]

This is by design. There will be one "parent" record for each matching
"child" record.

I have concerns with
-a field name that is a reserved word (Date)
-a table name that stores data (December 4)
-parameter prompts in a query. http://www.tek-tips.com/faqs.cfm?fid=6763

 

[Steve Sanford]

This is called a "Cartesian join".

"A cartesian join is a join of every row of one table to every row of
another table. This normally happens when no matching join columns are
specified. For example, if table A with 100 rows is joined with table B with
1000 rows, a cartesian join will return 100,000 rows! Something to be
avoided!'


A join can be in the FROM clause:

FROM Table1 INNER JOIN Table2 ON Table1.DealerID = Table2.DealerID;


or in the WHERE clause:

WHERE A1.store_name = A2.store_name


Note: A query must have at least (N-1) join conditions to prevent a
cartesian product.

Note 2: A Cartesian join is very cpu intensive, and is usually done by
mistake.


As a side note, you should use a naming convention. You have "Date" as s
field name, which is a reserved word in Access, a function and a command.
Also, "Date' is not very descriptive.... Date of what???


HTH