Duplicates - Query Joining

G

Guest

Okay, I've posted and tried. I'm not sure if the problem is in my tables.
Anyways I have two queries that produce results based on different date
ranges. I want both of those to pull into one query and then I can build a
report.

FYI - my table is as such
GGP
Customer
Month
GAAP
Direct

As you may know I have several records for one customer hence the month
field.
One query pulls in the records and sums based on the prior 12 months. The
other query pulls in records and sums based on the 12 months prior to the
first query.

Problem is when I run the query that pulls in fields from the other two
queries I get duplicates. I've created a relationship from GGP in the one
query to the GGP in the other. That might be the problem??? Not sure how to
fix. Anyways the results from the below query pull in each record 3 times.
I've had some help but either misunderstood or wasn't clear on what I
have...it's been a challenge for me all day. Thanks in advance for your help
and patience.

So I try to join the queries and here's the SQL Statement:
SELECT DISTINCTROW [GAM Metric Totals Previous Year Rolling Query].GGP, [GAM
Metric Totals Previous Year Rolling Query].[Company Name], [GAM Metric Totals
Query1].Month, [GAM Metric Totals Previous Year Rolling Query].Month, [GAM
Metric Totals Previous Year Rolling Query].[Headquarter Country], [GAM Metric
Totals Previous Year Rolling Query].[Sum Of GAAP] AS [Sum Of GAM Metric
Totals Previous Year Rolling Query_Sum Of GAAP], [GAM Metric Totals Previous
Year Rolling Query].[Sum Of Direct] AS [Sum Of GAM Metric Totals Previous
Year Rolling Query_Sum Of Dire], [GAM Metric Totals Previous Year Rolling
Query].[Sum Of GP] AS [Sum Of GAM Metric Totals Previous Year Rolling
Query_Sum Of GP], [GAM Metric Totals Previous Year Rolling Query].[Avg Of
Revenue at HQ] AS [Avg Of GAM Metric Totals Previous Year Rolling Query_Avg
Of Reve], [GAM Metric Totals Query1].[Sum Of GAAP] AS [Sum Of GAM Metric
Totals Query1_Sum Of GAAP], [GAM Metric Totals Query1].[Sum Of Direct] AS
[Sum Of GAM Metric Totals Query1_Sum Of Direct], [GAM Metric Totals
Query1].[Sum Of GP] AS [Sum Of GAM Metric Totals Query1_Sum Of GP], [GAM
Metric Totals Query1].[Avg Of Revenue at HQ] AS [Avg Of GAM Metric Totals
Query1_Avg Of Revenue at HQ]
FROM [GAM Metric Totals Query1] INNER JOIN [GAM Metric Totals Previous Year
Rolling Query] ON [GAM Metric Totals Query1].GGP = [GAM Metric Totals
Previous Year Rolling Query].GGP;
 
G

Guest

There should not be duplicates if the first queries only pull records from
the correct year. What kind of field is your 'Month'? Is it a datetime
field or text?
Why do you need two queries to pull this data?
'Month' is a reserved word in Access and should not be used as an object name.
--
KARL DEWEY
Build a little - Test a little


jenniferspnc said:
Okay, I've posted and tried. I'm not sure if the problem is in my tables.
Anyways I have two queries that produce results based on different date
ranges. I want both of those to pull into one query and then I can build a
report.

FYI - my table is as such
GGP
Customer
Month
GAAP
Direct

As you may know I have several records for one customer hence the month
field.
One query pulls in the records and sums based on the prior 12 months. The
other query pulls in records and sums based on the 12 months prior to the
first query.

Problem is when I run the query that pulls in fields from the other two
queries I get duplicates. I've created a relationship from GGP in the one
query to the GGP in the other. That might be the problem??? Not sure how to
fix. Anyways the results from the below query pull in each record 3 times.
I've had some help but either misunderstood or wasn't clear on what I
have...it's been a challenge for me all day. Thanks in advance for your help
and patience.

So I try to join the queries and here's the SQL Statement:
SELECT DISTINCTROW [GAM Metric Totals Previous Year Rolling Query].GGP, [GAM
Metric Totals Previous Year Rolling Query].[Company Name], [GAM Metric Totals
Query1].Month, [GAM Metric Totals Previous Year Rolling Query].Month, [GAM
Metric Totals Previous Year Rolling Query].[Headquarter Country], [GAM Metric
Totals Previous Year Rolling Query].[Sum Of GAAP] AS [Sum Of GAM Metric
Totals Previous Year Rolling Query_Sum Of GAAP], [GAM Metric Totals Previous
Year Rolling Query].[Sum Of Direct] AS [Sum Of GAM Metric Totals Previous
Year Rolling Query_Sum Of Dire], [GAM Metric Totals Previous Year Rolling
Query].[Sum Of GP] AS [Sum Of GAM Metric Totals Previous Year Rolling
Query_Sum Of GP], [GAM Metric Totals Previous Year Rolling Query].[Avg Of
Revenue at HQ] AS [Avg Of GAM Metric Totals Previous Year Rolling Query_Avg
Of Reve], [GAM Metric Totals Query1].[Sum Of GAAP] AS [Sum Of GAM Metric
Totals Query1_Sum Of GAAP], [GAM Metric Totals Query1].[Sum Of Direct] AS
[Sum Of GAM Metric Totals Query1_Sum Of Direct], [GAM Metric Totals
Query1].[Sum Of GP] AS [Sum Of GAM Metric Totals Query1_Sum Of GP], [GAM
Metric Totals Query1].[Avg Of Revenue at HQ] AS [Avg Of GAM Metric Totals
Query1_Avg Of Revenue at HQ]
FROM [GAM Metric Totals Query1] INNER JOIN [GAM Metric Totals Previous Year
Rolling Query] ON [GAM Metric Totals Query1].GGP = [GAM Metric Totals
Previous Year Rolling Query].GGP;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top