A Complicated Query Question

  • Thread starter Thread starter Michael Conroy
  • Start date Start date
M

Michael Conroy

I have two queries, one a monthly total of gallons used by location, and
another a monthly total of cost by location. So what I want is a report with
the twelve months of the year and two fields underneath each month. Yet a
crosstab allows only one field to be transposed. I can do a crosstab and get
the gallons total 1-12 and cost 1-12, but how do I put them together so that
they are on the same row and alligned under January, February, etc. like the
example:
January February
JFK 50,000 $110,000 40,000 $85,000
LAX 60,000 $135,000 45,000 $90,000

I can guess that there is a union query in the future, but before or after
the crosstab? And if it is after, won't I get two records for each location?
Do I do another sum query on the union of the crosstabs? This seems a little
complicated so I thought I would ask the experts. Any advice would be
appreciated. Thanks
 
Michael

How are you intending to use the results? If you want something you can
print out, one option might be to create a report with a subreport for each
of your two totals.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Jeff,
I want a report and I thought of a sub report. But how? Do I have twelve
(one for each month) for gallons and twelve for cost? The sample I gave you
is how I want the report to appear.
 
I have two queries, one a monthly total of gallons used by location, and
another a monthly total of cost by location. So what I want is a report with
the twelve months of the year and two fields underneath each month. Yet a
crosstab allows only one field to be transposed. I can do a crosstab and get
the gallons total 1-12 and cost 1-12, but how do I put them together so that
they are on the same row and alligned under January, February, etc. like the
example:
January February
JFK 50,000 $110,000 40,000 $85,000
LAX 60,000 $135,000 45,000 $90,000

I can guess that there is a union query in the future, but before or after
the crosstab? And if it is after, won't I get two records for each location?
Do I do another sum query on the union of the crosstabs? This seems a little
complicated so I thought I would ask the experts. Any advice would be
appreciated. Thanks
Click to expand...

You can include a calculated field:

[Gallons] & " " & Format([Cost], "Currency")

and base your crosstab on the query containing this field.
 
Another solution.

Two crosstab queries to get your initial results.
Join the two crosstab queries in a third query.

Assumption: You will have a record for each location in both crosstabs and you
will have all the month columns in both crosstabs. Your queries might look
something like the following.

TRANSFORM Sum(Gallons)
SELECT Location
FROM SomeTable
GROUP BY Location
PIVOT MonthName("January","February",...,"December")

TRANSFORM Sum(Cost)
SELECT Location
FROM SomeTable
GROUP BY Location
PIVOT MonthName("January","February",...,"December")

SELECT G.Location
, G.January, C.January
, G.February, C.February
, ...
, G.December, C.December
FROM qxtabGallons As G INNER JOIN qxtabCost as C
ON G.Location = C.Location

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have two queries, one a monthly total of gallons used by location, and
another a monthly total of cost by location. So what I want is a report with
the twelve months of the year and two fields underneath each month. Yet a
crosstab allows only one field to be transposed. I can do a crosstab and get
the gallons total 1-12 and cost 1-12, but how do I put them together so that
they are on the same row and alligned under January, February, etc. like the
example:
January February
JFK 50,000 $110,000 40,000 $85,000
LAX 60,000 $135,000 45,000 $90,000

I can guess that there is a union query in the future, but before or after
the crosstab? And if it is after, won't I get two records for each location?
Do I do another sum query on the union of the crosstabs? This seems a little
complicated so I thought I would ask the experts. Any advice would be
appreciated. Thanks
Click to expand...

You can include a calculated field:

[Gallons] & " " & Format([Cost], "Currency")

and base your crosstab on the query containing this field.
Click to expand...
 
Back
Top