SubQuery or SubReport??

[petdominic via AccessMonster.com]

I've created two separate queries, as they pull different date ranges.
Problem is I want to combine the results of these two queries in a report.
When viewing the report I want all the information from both queries to read
left to right by customer. I don't want anyone to scroll to the bottom to
see different date range data but for the same customer. Is there a place I
can put the subreport so that it groups by customer with the other detail of
the main report?

Or do I create a subquery or join query?

Not sure if you need the detail of my queries, but here it is.
Query 1:
SELECT DISTINCTROW [GAM Metric Totals].ID, [GAM Metric Totals].GGP, [GAM
Metric Totals].[Company Name], [GAM Metric Totals].[Headquarter Country],
[GAM Metric Totals].Month, Sum([GAM Metric Totals].GAAP) AS [Sum Of GAAP],
Sum([GAM Metric Totals].Direct) AS [Sum Of Direct], Sum([GAM Metric Totals].
GP) AS [Sum Of GP], Avg([GAM Metric Totals].[Revenue at HQ]) AS [Avg Of
Revenue at HQ]
FROM [GAM Metric Totals]
GROUP BY [GAM Metric Totals].ID, [GAM Metric Totals].GGP, [GAM Metric Totals].
[Company Name], [GAM Metric Totals].[Headquarter Country], [GAM Metric Totals]
..Month
HAVING ((([GAM Metric Totals].Month)>=#9/1/2006# And ([GAM Metric Totals].
Month)<=#8/31/2007#));

Query 2:
SELECT DISTINCTROW [GAM Metric Totals].ID, [GAM Metric Totals].GGP, [GAM
Metric Totals].[Company Name], [GAM Metric Totals].[Headquarter Country], Sum
([GAM Metric Totals].GAAP) AS [Sum Of GAAP], Sum([GAM Metric Totals].Direct)
AS [Sum Of Direct], Sum([GAM Metric Totals].GP) AS [Sum Of GP], Avg([GAM
Metric Totals].[Revenue at HQ]) AS [Avg Of Revenue at HQ], [GAM Program
Details].[Account Plan], [GAM Program Details].[Procurement Guide], [GAM
Program Details].[GAT Meeting Notes], [GAM Program Details].[Client Business
Review], [GAM Program Details].[License Contracts Current], [GAM Program
Details].[GAM Site Exists], [GAM Program Details].[Last Checked]
FROM [GAM Metric Totals] INNER JOIN [GAM Program Details] ON [GAM Metric
Totals].GGP = [GAM Program Details].GGP
GROUP BY [GAM Metric Totals].ID, [GAM Metric Totals].GGP, [GAM Metric Totals].
[Company Name], [GAM Metric Totals].[Headquarter Country], [GAM Program
Details].[Account Plan], [GAM Program Details].[Procurement Guide], [GAM
Program Details].[GAT Meeting Notes], [GAM Program Details].[Client Business
Review], [GAM Program Details].[License Contracts Current], [GAM Program
Details].[GAM Site Exists], [GAM Program Details].[Last Checked], [GAM Metric
Totals].Month
HAVING ((([GAM Metric Totals].Month)>DateAdd("m",-13,Date())));

Thank you all as always for your assistance and patience.

 

[Michel Walsh]

Make a query to join the first two queries.


Assuming you join on the companies names:
Query3:

SELECT CompanyName FROM query1
UNION
SELECT CompanyName FROM query2



And then, the final query:

SELECT query3.CompanyName, query1.*, query2.*
FROM (query3 LEFT JOIN query1 ON query3.CompanyName=query1.CompanyName)
LEFT JOIN query2 ON query3.CompanyName = query2.companyName




If you are using MS SQL Server instead of Jet, you can use just one new
query:

SELECT COALESCE(query1.CompanyName, query2.CompanyName), query1.*, query2.*
FROM query1 FULL OUTER JOIN query2 ON query1.CompanyName =
query2.CompanyName



Hoping it may help,
Vanderghast, Access MVP

I've created two separate queries, as they pull different date ranges.
Problem is I want to combine the results of these two queries in a report.
When viewing the report I want all the information from both queries to
read
left to right by customer. I don't want anyone to scroll to the bottom to
see different date range data but for the same customer. Is there a place
I
can put the subreport so that it groups by customer with the other detail
of
the main report?

Or do I create a subquery or join query?

Not sure if you need the detail of my queries, but here it is.
Query 1:
SELECT DISTINCTROW [GAM Metric Totals].ID, [GAM Metric Totals].GGP, [GAM
Metric Totals].[Company Name], [GAM Metric Totals].[Headquarter Country],
[GAM Metric Totals].Month, Sum([GAM Metric Totals].GAAP) AS [Sum Of GAAP],
Sum([GAM Metric Totals].Direct) AS [Sum Of Direct], Sum([GAM Metric
Totals].
GP) AS [Sum Of GP], Avg([GAM Metric Totals].[Revenue at HQ]) AS [Avg Of
Revenue at HQ]
FROM [GAM Metric Totals]
GROUP BY [GAM Metric Totals].ID, [GAM Metric Totals].GGP, [GAM Metric
Totals].
[Company Name], [GAM Metric Totals].[Headquarter Country], [GAM Metric
Totals]
Month
HAVING ((([GAM Metric Totals].Month)>=#9/1/2006# And ([GAM Metric Totals].
Month)<=#8/31/2007#));

Query 2:
SELECT DISTINCTROW [GAM Metric Totals].ID, [GAM Metric Totals].GGP, [GAM
Metric Totals].[Company Name], [GAM Metric Totals].[Headquarter Country],
Sum
([GAM Metric Totals].GAAP) AS [Sum Of GAAP], Sum([GAM Metric
Totals].Direct)
AS [Sum Of Direct], Sum([GAM Metric Totals].GP) AS [Sum Of GP], Avg([GAM
Metric Totals].[Revenue at HQ]) AS [Avg Of Revenue at HQ], [GAM Program
Details].[Account Plan], [GAM Program Details].[Procurement Guide], [GAM
Program Details].[GAT Meeting Notes], [GAM Program Details].[Client
Business
Review], [GAM Program Details].[License Contracts Current], [GAM Program
Details].[GAM Site Exists], [GAM Program Details].[Last Checked]
FROM [GAM Metric Totals] INNER JOIN [GAM Program Details] ON [GAM Metric
Totals].GGP = [GAM Program Details].GGP
GROUP BY [GAM Metric Totals].ID, [GAM Metric Totals].GGP, [GAM Metric
Totals].
[Company Name], [GAM Metric Totals].[Headquarter Country], [GAM Program
Details].[Account Plan], [GAM Program Details].[Procurement Guide], [GAM
Program Details].[GAT Meeting Notes], [GAM Program Details].[Client
Business
Review], [GAM Program Details].[License Contracts Current], [GAM Program
Details].[GAM Site Exists], [GAM Program Details].[Last Checked], [GAM
Metric
Totals].Month
HAVING ((([GAM Metric Totals].Month)>DateAdd("m",-13,Date())));

Thank you all as always for your assistance and patience.

 

[petdominic via AccessMonster.com]

Okay, I decided to do SQL View and type in the code per your second
suggestion....how do I tell if it's Jet or MS SQL Server??? Oh and I joined
on the GGP column.


SELECT COALESCE([GAM Metric Totals Query1].GGP, [GAM Metric Totals Previous
Year Rolling Query].GGP), [GAM Metric Totals Query1].*, [GAM Metric Totals
Previous Year Rolling Query].*
FROM [GAM Metric Totals Query1] FULL OUTER JOIN [GAM Metric Totals Previous
Year Rolling Query] ON [GAM Metric Totals Query1].GGP =
[GAM Metric Totals Previous Year Rolling Query].GGP

I get this following error message "Syntax error in FROM Clause".
Can you assist?

Also, what does COALESCE do? I'm trying to learn as well so I thought I
would ask so in case I need to use it in the future, I will know its purpose.


Thanks again

Make a query to join the first two queries.

Assuming you join on the companies names:
Query3:

SELECT CompanyName FROM query1
UNION
SELECT CompanyName FROM query2

And then, the final query:

SELECT query3.CompanyName, query1.*, query2.*
FROM (query3 LEFT JOIN query1 ON query3.CompanyName=query1.CompanyName)
LEFT JOIN query2 ON query3.CompanyName = query2.companyName

If you are using MS SQL Server instead of Jet, you can use just one new
query:

SELECT COALESCE(query1.CompanyName, query2.CompanyName), query1.*, query2.*
FROM query1 FULL OUTER JOIN query2 ON query1.CompanyName =
query2.CompanyName

Hoping it may help,
Vanderghast, Access MVP

I've created two separate queries, as they pull different date ranges.
Problem is I want to combine the results of these two queries in a report.

[quoted text clipped - 55 lines]

Thank you all as always for your assistance and patience.

 

[Michel Walsh]

You probably use Jet. Jet does not support FULL OUTER JOIN and, for Jet, it
would be Nz rather than COALESCE, if needed.

Since you join on GPP, not on CompanyName, query3 should be:


SELECT gpp FROM query1
UNION
SELECT gpp FROM query2



and base your report on :


SELECT query3.gpp, query1.*, query2.*
FROM (query3 LEFT JOIN query1 ON query3.gpp=query1.gpp)
LEFT JOIN query2 ON query3.gpp= query2.gpp




Vanderghast, Access MVP

Okay, I decided to do SQL View and type in the code per your second
suggestion....how do I tell if it's Jet or MS SQL Server??? Oh and I
joined
on the GGP column.


SELECT COALESCE([GAM Metric Totals Query1].GGP, [GAM Metric Totals
Previous
Year Rolling Query].GGP), [GAM Metric Totals Query1].*, [GAM Metric Totals
Previous Year Rolling Query].*
FROM [GAM Metric Totals Query1] FULL OUTER JOIN [GAM Metric Totals
Previous
Year Rolling Query] ON [GAM Metric Totals Query1].GGP =
[GAM Metric Totals Previous Year Rolling Query].GGP

I get this following error message "Syntax error in FROM Clause".
Can you assist?

Also, what does COALESCE do? I'm trying to learn as well so I thought I
would ask so in case I need to use it in the future, I will know its
purpose.


Thanks again

Make a query to join the first two queries.

Assuming you join on the companies names:
Query3:

SELECT CompanyName FROM query1
UNION
SELECT CompanyName FROM query2

And then, the final query:

SELECT query3.CompanyName, query1.*, query2.*
FROM (query3 LEFT JOIN query1 ON query3.CompanyName=query1.CompanyName)
LEFT JOIN query2 ON query3.CompanyName = query2.companyName

If you are using MS SQL Server instead of Jet, you can use just one new
query:

SELECT COALESCE(query1.CompanyName, query2.CompanyName), query1.*,
query2.*
FROM query1 FULL OUTER JOIN query2 ON query1.CompanyName =
query2.CompanyName

Hoping it may help,
Vanderghast, Access MVP

I've created two separate queries, as they pull different date ranges.
Problem is I want to combine the results of these two queries in a
report.

[quoted text clipped - 55 lines]

Thank you all as always for your assistance and patience.

 

[petdominic via AccessMonster.com]

Thanks for your patience.

I have done as follows, but get tons of duplicate rows, and then get an error
message (Teh specified filed [GAM Metric Totals Query1].[Sum of GAAP] could
refer to more than one table listed in the FROM clause of your SQL Statement
when running the report.

My final query results are (which i see is pulling in every colum from both
reports):
SELECT [Union Query].GGP, [GAM Metric Totals Query1].[Company Name], [GAM
Metric Totals Query1].[Headquarter Country], [GAM Metric Totals Query1].[Sum
Of GAAP], [GAM Metric Totals Query1].[Sum Of Direct], [GAM Metric Totals
Query1].[Sum Of GP], [GAM Metric Totals Query1].[Avg Of Revenue at HQ], [GAM
Metric Totals Previous Year Rolling Query].[Sum Of GAAP], [GAM Metric Totals
Previous Year Rolling Query].[Sum Of Direct], [GAM Metric Totals Previous
Year Rolling Query].[Sum Of GP], [GAM Metric Totals Previous Year Rolling
Query].[Avg Of Revenue at HQ]
FROM ([Union Query] LEFT JOIN [GAM Metric Totals Query1] ON [Union Query].
GGP=[GAM Metric Totals Query1].GGP) LEFT JOIN [GAM Metric Totals Previous
Year Rolling Query] ON [Union Query].GGP=[GAM Metric Totals Previous Year
Rolling Query].GGP;

Do you think there might be a problem with the way my original queries are
built??
Thanks for not giving up on this yet

You probably use Jet. Jet does not support FULL OUTER JOIN and, for Jet, it
would be Nz rather than COALESCE, if needed.

Since you join on GPP, not on CompanyName, query3 should be:

SELECT gpp FROM query1
UNION
SELECT gpp FROM query2

and base your report on :

SELECT query3.gpp, query1.*, query2.*
FROM (query3 LEFT JOIN query1 ON query3.gpp=query1.gpp)
LEFT JOIN query2 ON query3.gpp= query2.gpp

Vanderghast, Access MVP

Okay, I decided to do SQL View and type in the code per your second
suggestion....how do I tell if it's Jet or MS SQL Server??? Oh and I

[quoted text clipped - 51 lines]

 

[petdominic via AccessMonster.com]

Okay so I took another stab at it and the query works!!! Hallelujah! Of
course, now I'm trying to do a report through Report Wizard but I keep
getting similar error messages when selecting different fields to pull in
"The specified field [GAM Metric Totals Query1].GGP could refer to more than
one table listed in the FROM clause of your SQL Statement." Any ideas?? So
close to being done!

Thanks for your patience.

I have done as follows, but get tons of duplicate rows, and then get an error
message (Teh specified filed [GAM Metric Totals Query1].[Sum of GAAP] could
refer to more than one table listed in the FROM clause of your SQL Statement
when running the report.

My final query results are (which i see is pulling in every colum from both
reports):
SELECT [Union Query].GGP, [GAM Metric Totals Query1].[Company Name], [GAM
Metric Totals Query1].[Headquarter Country], [GAM Metric Totals Query1].[Sum
Of GAAP], [GAM Metric Totals Query1].[Sum Of Direct], [GAM Metric Totals
Query1].[Sum Of GP], [GAM Metric Totals Query1].[Avg Of Revenue at HQ], [GAM
Metric Totals Previous Year Rolling Query].[Sum Of GAAP], [GAM Metric Totals
Previous Year Rolling Query].[Sum Of Direct], [GAM Metric Totals Previous
Year Rolling Query].[Sum Of GP], [GAM Metric Totals Previous Year Rolling
Query].[Avg Of Revenue at HQ]
FROM ([Union Query] LEFT JOIN [GAM Metric Totals Query1] ON [Union Query].
GGP=[GAM Metric Totals Query1].GGP) LEFT JOIN [GAM Metric Totals Previous
Year Rolling Query] ON [Union Query].GGP=[GAM Metric Totals Previous Year
Rolling Query].GGP;

Do you think there might be a problem with the way my original queries are
built??
Thanks for not giving up on this yet

You probably use Jet. Jet does not support FULL OUTER JOIN and, for Jet, it
would be Nz rather than COALESCE, if needed.

[quoted text clipped - 18 lines]

 

[Michel Walsh]

Your query states, in its SELECT clause:


[GAM Metric Totals Query1].*,



Remove that. If you need other fields, explicitly mentioned them (and do it
just ONCE).


Hoping it may help,
Vanderghast, Access MVP

Okay so I took another stab at it and the query works!!! Hallelujah! Of
course, now I'm trying to do a report through Report Wizard but I keep
getting similar error messages when selecting different fields to pull in
"The specified field [GAM Metric Totals Query1].GGP could refer to more
than
one table listed in the FROM clause of your SQL Statement." Any ideas??
So
close to being done!

Thanks for your patience.

I have done as follows, but get tons of duplicate rows, and then get an
error
message (Teh specified filed [GAM Metric Totals Query1].[Sum of GAAP]
could
refer to more than one table listed in the FROM clause of your SQL
Statement
when running the report.

My final query results are (which i see is pulling in every colum from
both
reports):
SELECT [Union Query].GGP, [GAM Metric Totals Query1].[Company Name], [GAM
Metric Totals Query1].[Headquarter Country], [GAM Metric Totals
Query1].[Sum
Of GAAP], [GAM Metric Totals Query1].[Sum Of Direct], [GAM Metric Totals
Query1].[Sum Of GP], [GAM Metric Totals Query1].[Avg Of Revenue at HQ],
[GAM
Metric Totals Previous Year Rolling Query].[Sum Of GAAP], [GAM Metric
Totals
Previous Year Rolling Query].[Sum Of Direct], [GAM Metric Totals Previous
Year Rolling Query].[Sum Of GP], [GAM Metric Totals Previous Year Rolling
Query].[Avg Of Revenue at HQ]
FROM ([Union Query] LEFT JOIN [GAM Metric Totals Query1] ON [Union Query].
GGP=[GAM Metric Totals Query1].GGP) LEFT JOIN [GAM Metric Totals Previous
Year Rolling Query] ON [Union Query].GGP=[GAM Metric Totals Previous Year
Rolling Query].GGP;

Do you think there might be a problem with the way my original queries are
built??
Thanks for not giving up on this yet

You probably use Jet. Jet does not support FULL OUTER JOIN and, for Jet,
it
would be Nz rather than COALESCE, if needed.

[quoted text clipped - 18 lines]

Thank you all as always for your assistance and patience.

 

[petdominic via AccessMonster.com]

So I had done the union query
SELECT ID FROM [GAM Metric Totals Query1]
UNION ALL SELECT ID FROM [GAM Metric Totals Previous Year Rolling Query];

Then I did the Final Query
SELECT [Union Query].ID, [GAM Metric Totals Query1].*, [GAM Metric Totals
Previous Year Rolling Query].*
FROM ([Union Query] LEFT JOIN [GAM Metric Totals Query1] ON [Union Query].ID=
[GAM Metric Totals Query1].ID) LEFT JOIN [GAM Metric Totals Previous Year
Rolling Query] ON [Union Query].ID=[GAM Metric Totals Previous Year Rolling
Query].ID;


So I'm assuming I should list the fields needed in that Final query which I
did...But lately, as you may have seen, my assumptions are never right.. So
I tried the below.
SELECT [Union Query].ID, [GAM Metric Totals Query1].GGP, [GAM Metric Totals
Query1].[Company], [GAM Metric Totals Query1].[AE in HQ Country], [GAM Metric
Totals Query1].[Headquarter Country], [GAM Metric Totals Query1].[Sum Of GAAP]
, [GAM Metric Totals Query1].[Sum Of Direct], [GAM Metric Totals Query1].[Sum
Of GP], [GAM Metric Totals Query1].[Avg Of Revenue at HQ], [GAM Metric Totals
Previous Year Rolling Query].*
FROM ([Union Query] LEFT JOIN [GAM Metric Totals Query1] ON [Union Query].ID
= [GAM Metric Totals Query1].ID) LEFT JOIN [GAM Metric Totals Previous Year
Rolling Query] ON [Union Query].ID = [GAM Metric Totals Previous Year Rolling
Query].ID;

I'm just not sure where the root of the problem is but I still get an error
message when building a report off this query. I use report wizard to pull
in all the fields, says unable to create report and then will populate with
that error message (The specified field [GAM Metric Totals Query1].GGP could
refer to more than one table listed in the FROM clause of your SQL Statement).
I will remove that field with the control source but then it will happen
again with the next field....Sum of GAAP, Sum of Direct, etc.

And you've been really helpful. I've made great progess and couldn't have
without your help. Thanks!

Your query states, in its SELECT clause:

[GAM Metric Totals Query1].*,

Remove that. If you need other fields, explicitly mentioned them (and do it
just ONCE).

Hoping it may help,
Vanderghast, Access MVP

Okay so I took another stab at it and the query works!!! Hallelujah! Of
course, now I'm trying to do a report through Report Wizard but I keep

[quoted text clipped - 44 lines]

 

[Michel Walsh]

As you can see, you still have the infamous * at the end of the SELECT
clause (probably you check the option to add it automatically! uncheck that!
It is under Tools | Options... [Table/Queries] [ ] Output All Fields )

Of GP], [GAM Metric Totals Query1].[Avg Of Revenue at HQ], [GAM Metric
Totals
Previous Year Rolling Query].*
'<-------------here -------------------
FROM ([Union Query] LEFT JOIN

and manually remove that ugly

, [GAM Metric Totals Previous Year Rolling Query].*



That makes a field listed TWICE, which become confusing, from what it seems.



Vanderghast, Access MVP

So I had done the union query
SELECT ID FROM [GAM Metric Totals Query1]
UNION ALL SELECT ID FROM [GAM Metric Totals Previous Year Rolling Query];

Then I did the Final Query
SELECT [Union Query].ID, [GAM Metric Totals Query1].*, [GAM Metric Totals
Previous Year Rolling Query].*
FROM ([Union Query] LEFT JOIN [GAM Metric Totals Query1] ON [Union
Query].ID=
[GAM Metric Totals Query1].ID) LEFT JOIN [GAM Metric Totals Previous Year
Rolling Query] ON [Union Query].ID=[GAM Metric Totals Previous Year
Rolling
Query].ID;


So I'm assuming I should list the fields needed in that Final query which
I
did...But lately, as you may have seen, my assumptions are never right..
So
I tried the below.
SELECT [Union Query].ID, [GAM Metric Totals Query1].GGP, [GAM Metric
Totals
Query1].[Company], [GAM Metric Totals Query1].[AE in HQ Country], [GAM
Metric
Totals Query1].[Headquarter Country], [GAM Metric Totals Query1].[Sum Of
GAAP]
, [GAM Metric Totals Query1].[Sum Of Direct], [GAM Metric Totals
Query1].[Sum
Of GP], [GAM Metric Totals Query1].[Avg Of Revenue at HQ], [GAM Metric
Totals
Previous Year Rolling Query].*
FROM ([Union Query] LEFT JOIN [GAM Metric Totals Query1] ON [Union
Query].ID
= [GAM Metric Totals Query1].ID) LEFT JOIN [GAM Metric Totals Previous
Year
Rolling Query] ON [Union Query].ID = [GAM Metric Totals Previous Year
Rolling
Query].ID;

I'm just not sure where the root of the problem is but I still get an
error
message when building a report off this query. I use report wizard to
pull
in all the fields, says unable to create report and then will populate
with
that error message (The specified field [GAM Metric Totals Query1].GGP
could
refer to more than one table listed in the FROM clause of your SQL
Statement).
I will remove that field with the control source but then it will happen
again with the next field....Sum of GAAP, Sum of Direct, etc.

And you've been really helpful. I've made great progess and couldn't have
without your help. Thanks!

Your query states, in its SELECT clause:

[GAM Metric Totals Query1].*,

Remove that. If you need other fields, explicitly mentioned them (and do
it
just ONCE).

Hoping it may help,
Vanderghast, Access MVP

Okay so I took another stab at it and the query works!!! Hallelujah!
Of
course, now I'm trying to do a report through Report Wizard but I keep

[quoted text clipped - 44 lines]

Thank you all as always for your assistance and patience.

 

[petdominic via AccessMonster.com]

So my issue is probably the join still??

Here is how I started trying to accomplish the end result:
I have that one query that pulls in based on one date range (prior 12 months)
My other query pulls in another date range as inputted into the design field.

The queries pull the same fields just sum differently since based on two
different date ranges...thus why I did two separate queries, as I didn't
think there was a way to do it all in one query.

So you kindly showed me how to do a Union Query. I kept getting duplicates
rows when pulling in on GGP or Company Name so I did ID (Access assigned
Primary Key). In my Final Query I get all the results of both queries, but
if I remove the [GAM Metric Totals Previous Year Rolling Query].* then I
won't get the results of that second query I want. I even did as I did on
the other, listing all the fields needed but that's the same thing as doing *
at the end, right?

The only end result I want to see is a report that shows the results of both
queries grouped by GGP..
GGP Company Sum of GAAP(1st date range) Sum of GAAP (2nd date
range), etc.

Does this make it more complicated or is there really no way to get the final
result in one report?

As you can see, you still have the infamous * at the end of the SELECT
clause (probably you check the option to add it automatically! uncheck that!
It is under Tools | Options... [Table/Queries] [ ] Output All Fields )

Of GP], [GAM Metric Totals Query1].[Avg Of Revenue at HQ], [GAM Metric
Totals
Previous Year Rolling Query].*
'<-------------here -------------------
FROM ([Union Query] LEFT JOIN

and manually remove that ugly

, [GAM Metric Totals Previous Year Rolling Query].*

That makes a field listed TWICE, which become confusing, from what it seems.

Vanderghast, Access MVP

So I had done the union query
SELECT ID FROM [GAM Metric Totals Query1]

[quoted text clipped - 67 lines]

 

[Michel Walsh]

The goal of the first query, the one with union, is to get an exhaustive
list of all the 'values' we will join over. It should only involve GGP
field, and NO OTHER.


SELECT gpp FROM [first Query]
UNION
SELECT gpp FROM [second Query]



Since you use an UNION, NOT an UNION ALL, there should be no duplicate in
the result of that query.

Note that there is no *, it is not:

SELECT gpp, * FROM [first query] UNION ...


Hoping it may help,
Vanderghast, Access MVP

So my issue is probably the join still??

Here is how I started trying to accomplish the end result:
I have that one query that pulls in based on one date range (prior 12
months)
My other query pulls in another date range as inputted into the design
field.

The queries pull the same fields just sum differently since based on two
different date ranges...thus why I did two separate queries, as I didn't
think there was a way to do it all in one query.

So you kindly showed me how to do a Union Query. I kept getting
duplicates
rows when pulling in on GGP or Company Name so I did ID (Access assigned
Primary Key). In my Final Query I get all the results of both queries,
but
if I remove the [GAM Metric Totals Previous Year Rolling Query].* then I
won't get the results of that second query I want. I even did as I did on
the other, listing all the fields needed but that's the same thing as
doing *
at the end, right?

The only end result I want to see is a report that shows the results of
both
queries grouped by GGP..
GGP Company Sum of GAAP(1st date range) Sum of GAAP (2nd
date
range), etc.

Does this make it more complicated or is there really no way to get the
final
result in one report?

As you can see, you still have the infamous * at the end of the SELECT
clause (probably you check the option to add it automatically! uncheck
that!
It is under Tools | Options... [Table/Queries] [ ] Output All Fields )

Of GP], [GAM Metric Totals Query1].[Avg Of Revenue at HQ], [GAM Metric
Totals
Previous Year Rolling Query].*
'<-------------here -------------------
FROM ([Union Query] LEFT JOIN

and manually remove that ugly

, [GAM Metric Totals Previous Year Rolling Query].*

That makes a field listed TWICE, which become confusing, from what it
seems.

Vanderghast, Access MVP

So I had done the union query
SELECT ID FROM [GAM Metric Totals Query1]

[quoted text clipped - 67 lines]

Thank you all as always for your assistance and patience.

 

[petdominic via AccessMonster.com]

Not trying to be difficult. I was trying to get rid of that error message
when trying to do a report. I read through threads and realized I had to
rename the fields in one of the queries I was joining (so it didn't have the
same field names as the other query) and take off the summing functions.

So the join pulls in all records from one query and lists them.
Then the join query pulls in all the records from the other query.

But I want a report to group it by the GGP but it can't because the query has
two fields for everything..
GGP
Company
GAAP
Number2_GGP
Number3_Company
Number4_GAAP

I really just want the report to show all the detail left to right per
customer. How can I accomplish that?

Do I need to start over from the beginning b/c it's gotten so confusing?

The goal of the first query, the one with union, is to get an exhaustive
list of all the 'values' we will join over. It should only involve GGP
field, and NO OTHER.

SELECT gpp FROM [first Query]
UNION
SELECT gpp FROM [second Query]

Since you use an UNION, NOT an UNION ALL, there should be no duplicate in
the result of that query.

Note that there is no *, it is not:

SELECT gpp, * FROM [first query] UNION ...

Hoping it may help,
Vanderghast, Access MVP

So my issue is probably the join still??

[quoted text clipped - 55 lines]

 

[Michel Walsh]

Your original query join only on GCP :

FROM [GAM Metric Totals] INNER JOIN [GAM Program Details] ON [GAM Metric
Totals].GGP = [GAM Program Details].GGP


Sure, if we rewrite it:

FROM a INNER JOIN b ON a.f1=b.f1


If both tables have all the possible values for f1, there should be no
problem. But if some values are in table a but not in table b, and some
values are in table b but not in table a, a possible solution is to make a
'table' c, with all the possible values (the UNION query, in our case was
planned to do that, that is:


SELECT a.f1 FROM a
UNION
SELECT b.f1 FROM b


), to get our 'table' c, then, we would use as last query:


==================
SELECT c.f1, a.*, b.*
FROM ( c LEFT JOIN a ON c.f1=a.f1) LEFT JOIN b ON c.f1=b.f1
==================



Example.


If table a has data like:

f1 f2 f3 ' fields
------------------------
mary 10 hello
ann 11 hi 'data


and table b data like

f1 g2 g3 ' fields
-------------------------
mary m one
john j two 'date



then, the union query return:

f1 ' field name
---------
mary
ann
john ' data



and the last query:


c.f1 a.f2 a.f2 b.g2 b.g3 'fields
--------------------------------------------------------
mary 10 hello m one
ann 11 hi (null) (null)
john (null) (null) j two 'data




Now, if the 'matching row' criteria needs two fields, rather than just one,
the UNION query has to incorporate these two fields:


SELECT a.f1, a.f2 FROM a
UNION
SELECT b.f1, b.f2 FROM b 'saved as query c

' note there is no * in the SELECT statements for the union query.



and the last query have to join on these two fields,

SELECT c.f1, c.f2, a.*, b.*
FROM ( c LEFT JOIN a ON c.f1=a.f1 AND c.f2=a.f2)
LEFT JOIN b ON c.f1=b.f1 AND c.f2=b.f2





Is that better?


Vanderghast, Access MVP

Not trying to be difficult. I was trying to get rid of that error message
when trying to do a report. I read through threads and realized I had to
rename the fields in one of the queries I was joining (so it didn't have
the
same field names as the other query) and take off the summing functions.

So the join pulls in all records from one query and lists them.
Then the join query pulls in all the records from the other query.

But I want a report to group it by the GGP but it can't because the query
has
two fields for everything..
GGP
Company
GAAP
Number2_GGP
Number3_Company
Number4_GAAP

I really just want the report to show all the detail left to right per
customer. How can I accomplish that?

Do I need to start over from the beginning b/c it's gotten so confusing?

The goal of the first query, the one with union, is to get an exhaustive
list of all the 'values' we will join over. It should only involve GGP
field, and NO OTHER.

SELECT gpp FROM [first Query]
UNION
SELECT gpp FROM [second Query]

Since you use an UNION, NOT an UNION ALL, there should be no duplicate in
the result of that query.

Note that there is no *, it is not:

SELECT gpp, * FROM [first query] UNION ...

Hoping it may help,
Vanderghast, Access MVP

So my issue is probably the join still??

[quoted text clipped - 55 lines]

Thank you all as always for your assistance and patience.

 

[petdominic via AccessMonster.com]

Believe it or not, I think I've got it...it all appears right so far without
actually running the final numbers. That's exactly what I needed to do, pull
in additional shared fields such as Company Name, etc. in the Union Query!
Thanks for your patience. You've helped a great deal.

Your original query join only on GCP :

FROM [GAM Metric Totals] INNER JOIN [GAM Program Details] ON [GAM Metric
Totals].GGP = [GAM Program Details].GGP

Sure, if we rewrite it:

FROM a INNER JOIN b ON a.f1=b.f1

If both tables have all the possible values for f1, there should be no
problem. But if some values are in table a but not in table b, and some
values are in table b but not in table a, a possible solution is to make a
'table' c, with all the possible values (the UNION query, in our case was
planned to do that, that is:

SELECT a.f1 FROM a
UNION
SELECT b.f1 FROM b

), to get our 'table' c, then, we would use as last query:

==================
SELECT c.f1, a.*, b.*
FROM ( c LEFT JOIN a ON c.f1=a.f1) LEFT JOIN b ON c.f1=b.f1
==================

Example.

If table a has data like:

f1 f2 f3 ' fields
------------------------
mary 10 hello
ann 11 hi 'data

and table b data like

f1 g2 g3 ' fields
-------------------------
mary m one
john j two 'date

then, the union query return:

f1 ' field name
---------
mary
ann
john ' data

and the last query:

c.f1 a.f2 a.f2 b.g2 b.g3 'fields
--------------------------------------------------------
mary 10 hello m one
ann 11 hi (null) (null)
john (null) (null) j two 'data

Now, if the 'matching row' criteria needs two fields, rather than just one,
the UNION query has to incorporate these two fields:

SELECT a.f1, a.f2 FROM a
UNION
SELECT b.f1, b.f2 FROM b 'saved as query c

' note there is no * in the SELECT statements for the union query.

and the last query have to join on these two fields,

SELECT c.f1, c.f2, a.*, b.*
FROM ( c LEFT JOIN a ON c.f1=a.f1 AND c.f2=a.f2)
LEFT JOIN b ON c.f1=b.f1 AND c.f2=b.f2

Is that better?

Vanderghast, Access MVP

Not trying to be difficult. I was trying to get rid of that error message
when trying to do a report. I read through threads and realized I had to

[quoted text clipped - 43 lines]