query and tables together (1 Viewer)

10kz

Registered User.
Local time
Today, 15:50
Joined
Jan 9, 2017
Messages
21
I have 1 table and 2 queries:

tbl_Months
qry_ForecastV
qry_ActualV

I have the ForecastV and ActualV queries linked to the tbl_Months via the month field.

Instead of getting this:


MM/YY Forecast_v Actual_v
11/16 1100
12/16 1000 1200
01/17 600 700
02/17 800
03/17 800


I get:

MM/YY Forecast_v Actual_v
11/16 1100
12/16 480330 3433244
01/17 343242 3456546
02/17 800
03/17 800

Its multiplying the 12/16 and 01/17 values many times. The other months (11/16, 02/17 & 03/17) are fine.

Any ideas?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:50
Joined
Feb 28, 2001
Messages
27,001
When you say "linked" it sounds like you mean some kind of temporary relationship in the form of a JOIN (or JOIN equivalent). Beware of doing this without tightly qualifying the JOIN. Otherwise, you might get something called a Cartesian JOIN (also called a permutation join) that could lead to serious problems in combining fields you didn't want combined.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:50
Joined
Feb 28, 2001
Messages
27,001
What are the data types of the fields in question. Can't tell anything useful from the linkage diagram.
 

plog

Banishment Pending
Local time
Today, 17:50
Joined
May 11, 2011
Messages
11,612
Most likely you are going to need a subquery to solve this. When you JOIN datasources together the number of resulting records is the product of the matching values.

Table1
ID, FieldA
1, 17
1, 12
3, 11
3, 10
4, 13

Table2
ID, FieldB
1, 4
1, 3
2, 1
3, 2
3, 8
3, 5
4, 2

So, when you JOIN Table1.ID to Table2.ID you will receive 4 records for ID=1 (2x2), 0 records for ID=2 (0x1), 6 records for ID=3 (2x3) and 1 record for ID=4. That's what's happening with your data.

If you just want 1 result per ID, then you must make sure the underlying datasources each just have 1 record with that value. This is achieved by creating a subquery to GROUP BY the ID and then determining what value should go with it (e.g. MAX, MIN, etc.). Then using that query instead of the actual datasource with multiple values.
 

Users who are viewing this thread

Top Bottom