Duplicates---WHY? (1 Viewer)

[ALewis06]

Why is this query producing duplicates in the ForecastAmount field?

SELECT qryAssumptions.AssumpDate, qryAssumptions.ProdRevTypeMatch, [AssumpAmt]+Nz([RevenueAmt]) AS ForecastAmount FROM (qryAssumptions LEFT JOIN sub_LastRevenueDates ON (qryAssumptions.ProdRevTypeMatch = sub_LastRevenueDates.ProdRevTypeMatch) AND (qryAssumptions.AssumpDate = sub_LastRevenueDates.AssumpDate)) LEFT JOIN qryPastRevenue ON (sub_LastRevenueDates.ProdRevTypeMatch = qryPastRevenue.ProdRevType) AND (sub_LastRevenueDates.LastRevenueDate = qryPastRevenue.RevenueDate);

 

[plog]

Probably because you have duplicated values in a fields used to JOIN one of the tables that is joined.

Run an aggregate query on each of your joined queries:

1. Count the number of ProdRevTypeMatch & AssumpDate permutations in sub_LastRevenueDates

2. Count the number of ProdRevType & RevenueDate permutations in qryPastRevenue

 

[ALewis06]

Yes, that is the case. For instance in the subLastRevenueDates query, one ProdRevTypeMatch-AssumpDate combo has 51 instances. I don't see a way around that though because the query that subLastRevenueDates is built on, is based off a table that is normalized, therefore at its origins fields like AssumpDate and ProdRevType are repeated.

 

[plog]

I'm sure there's a way to produce the results you expect, its just you didn't provide those expectations. If you can give me sample data to demonstrate your expectations I can help. I would need 2 sets of data:

A. Starting sample data from your tables/queries. Using field and table/query names give me what you are starting with. Provide enough data to cover all cases/issues.

B. Expected results based on the data in A. What you expect to end with based on the initial data you provide in A.

 

[The_Doc_Man]

When joining a pair of tables that have a many-to-one relationship, you need to either make the many-side fields be aggregated (SUM, COUNT, AVG, MIN, MAX, etc.) - or you need to "drive" the query off the unique key of the many-side table (assuming it has one) and recognize that the one-side table is just a translator or attribute lookup.

If that isn't the case, your tables don't support your design goal and that is a design error.

 

[ALewis06]

@plog

I have attached samples of the root table data and the queries I created based off that table. qry710_AssumpAddedToPastRev is where I seem to be having the most trouble with duplicates.

B. What I was expecting was for LDMRR CurrFcstAmt to be around $9,816,000

 

[ALewis06]

Attached are the final 2 files I am working with

 

[plog]

You have provided me with too much and not enough data.

Data A: Your initial post dealt with 3 queries. You posted 6 and I don't see one that relates to qryAssumptions. I don't know where I am to begin.

Data B: Your expected results are simply LDMRR CurrFcstAmt ~ $9,816,000. Your initial post has no field named that.

I don't care about ill fated attempts to get where you are trying to go. I want to know where you are starting and where you expect to end.

 

[ALewis06]

Understood