Group same type of field from 3 different queries

[Andy]

Can anyone help me with this?

I have 3 different queries each summing up a value for a given month.
Past sales, present sales, future sales, these only have two fields Month
and value.

What I want to do is create something like

Month Past Present Future
-------------------------------------
Jun 5278 3424 3454
Jul 5625 6254 5462
Jun 2562 5264 5262

Where it will merge the two values for Jun, this will account for more than
one product showing a summary of all sales.

Ive tried using crosstabs queries, nested queries, even writing SQL
statements but alas I seem to have hit a brick wall.

 

[John Spencer]

It would help if you showed us the original queries.

I might try adding another field to the original queries identifying the
type of information "Past", "Present", "Future" and then combine the three
queries with a union query and then use that to create the crosstab. On the
other hand it could be possible to do this all in one query, depending on
your table structure.

You have not given us much information to work with, but here is a GUESS

SELECT "Past" as TimeFrame, MonthSum, MonthName
FROM Past
UNION ALL
SELECT "Present" as TimeFrame, MonthSum, MonthName
FROM Present
UNION ALL
SELECT "Future" as TimeFrame, MonthSum, MonthName
FROM Future

Save that and use it as the source for a crosstab query, that maight look
something like the following
TRANSFORM Sum(MonthSum) as TheValue
SELECT MonthName
FROM qUnionQuery
GROUP BY MonthName
PIVOT TimeFrame

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Andy]

The 3 queries show

Past
Month Past total
----------------------
Jun 5645
Jul 4523
Aug 4533


Present
Month Present total
--------------------------
Jun 9349
Jul 3893

Future
Month Future
--------------------------
Jun 3875
Jul 9248
Aug 2493
Sep 9238


With these queries I want something like below, grouping the month and
creating totals for reoccuring months.

Month Past Present Future
-----------------------------------------
Jun 9349 9349 3875
Jul 4523 3893 9248
Aug 4533 0 2493
Sep 0 0 9238

Ive tried using joins, unions, sub queries but I keep hitting a brick wall,
when it does select the right fields it doesnt group correctly.
--
Thank you soo much

Andy

It would help if you showed us the original queries.

I might try adding another field to the original queries identifying the
type of information "Past", "Present", "Future" and then combine the three
queries with a union query and then use that to create the crosstab. On the
other hand it could be possible to do this all in one query, depending on
your table structure.

You have not given us much information to work with, but here is a GUESS

SELECT "Past" as TimeFrame, MonthSum, MonthName
FROM Past
UNION ALL
SELECT "Present" as TimeFrame, MonthSum, MonthName
FROM Present
UNION ALL
SELECT "Future" as TimeFrame, MonthSum, MonthName
FROM Future

Save that and use it as the source for a crosstab query, that maight look
something like the following
TRANSFORM Sum(MonthSum) as TheValue
SELECT MonthName
FROM qUnionQuery
GROUP BY MonthName
PIVOT TimeFrame

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John Spencer]

Andy,
You posted the RESULTS of the queries. We need to see the SQL statement of
the query. Open the queries in design view, select View: SQL from the menu,
post the SQL text.

You should be able to follow the posted example I gave you to get what you
want. You may have to change the TRANSFORM line to get the zero values.
Also, you may want to use First or Last instead of Sum.

TRANSFORM NZ((Sum(MonthSum),0) as TheValue
....


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

The 3 queries show

Past
Month Past total
----------------------
Jun 5645
Jul 4523
Aug 4533


Present
Month Present total
--------------------------
Jun 9349
Jul 3893

Future
Month Future
--------------------------
Jun 3875
Jul 9248
Aug 2493
Sep 9238


With these queries I want something like below, grouping the month and
creating totals for reoccuring months.

Month Past Present Future
-----------------------------------------
Jun 9349 9349 3875
Jul 4523 3893 9248
Aug 4533 0 2493
Sep 0 0 9238

Ive tried using joins, unions, sub queries but I keep hitting a brick
wall,
when it does select the right fields it doesnt group correctly.