Query returns too many entries

[add39]

I created a query based on 2 tables: Projected and Actual. Both tables are exactly the same, having all the same fields, except one is for projected values and the other is for actual values. The query is set to calculate the difference between actual and projected for all the fields (ie: Actual Revenue- Proj. Rev). The problem I am running into is that the query always returns the square of the number of table entries I have. For example, I currently have sixteen items in each table, and the query is listing 256 entries; when I have 4 table entries the query returns 16. Why is this happening and how can I fix it?

I thought it might have something to do with the way the tables are joined. Initially I had an autonumber field in each table and linked the two tables based on that. Since then, I've tried all sorts of ways to join the table and nothing has changed the query problem.

Thanks,
Av

 

[ALEKSJAY]

Do you have another unique ID, not the autonumber.
Anothjer option, if the tables are almost the same, you may consider modifying the design, and having the Actual Revenue and the Projected Revenue in the same table.

 

[add39]

Should the ID be the same on each table? Basically, I'm just trying to relate the initiative in the projected table to the exact same initiative in the actual table. So I assigned autonumber, hoping that initiative 1 would relate to initiative 1, 2 to 2, etc. I could very well assign some sort of code for each initiative.

Also, what benefits are there to consolidating projected and actual onto one table?

 

[ALEKSJAY]

The ID should be the same on both tables. If this is the case, then the problem might be in how are you joining those two tables togehter. About the benefits of consolidating both tables, if the information is going to be basically the same, then you don't have to enter the data twice, you just modify the actual and projected values. Also, if you have only one table, then you don't need this query that is causing you problems.
Check the design of the tables to see if that could be a good option. If you leave the tables apart, then check how are you joining them in the query

 

[add39]

How should I join the tables to avoid this problem?

 

[ALEKSJAY]

if both tables have the same ID, and the query is returning the square, it is because they are not joined. When you design the query, did you join those ID fields ( If you are doing this query in the query designer, Drag the ID of one Table and Drop it over the ID of the other table)

 

[add39]

Thanks...that worked. I didn't realized I had to join them In the design query.

 

[GingerR]

i agree with Alek--if the data is one-for-one, which it seems it is, why not put it all in one table? otherwise you are duplicating information (huge possibility for error, plus a waste of time). in one table, have both Projected and Actual fields. is this doable?

 

[ALEKSJAY]

I'm glad I helped