Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

Please help me with Access Query

i have a union query, which based on the results of two different queries
let say query called "CountForWholeDay"
as i said, it should combine the results of the "CountForAMPartofDay" and "CountForPMPartofDay"

It always something for the AM part of the day and almost never for the PM part of the day

So i have two tables: AM and PM
AM table:
rec #      Name      Count Date
1                         Paul      6         03/09/2011
2      Steve                  5        03/10/2011
3      Steve                  4        03/11/2011
4      Ann      2         03/11/2011

so: to get the results for AM on today date

SELECT
Sum(AM.Count) AS Total, AM.Date
FROM AM
WHERE (((AM.Date)=Format(Now(),"mm/dd/yyyy")))
GROUP BY AM.Date;
and the results should be an it is :6
it is fine and working
PM table:
rec #      Name      Count   Date
1                        Paul      1           03/09/2011
2      Steve                 1           03/10/2011
as you can see, there is no result for the PM portion of the day
SELECT
Sum(PM.Count) AS Total,
PM.Date
FROM PM
WHERE (((PM.Date)=Format(Now(),"mm/dd/yyyy")))
GROUP BY PM.Date;
and the results should be an it is : 0  
but after running there is no results (only names of the columns)
Total      Date
now i want to run the query "CountForWholeDay"
SELECT
CountForAMPartofDay.Total AS [AM Part],
CountForPMPartofDay.Total AS [PM Part],
[AM Part]+[PM Part] AS [Grand Total]
FROM CountForAMPartofDay, CountForPMPartofDay;
does not work the way i want

I want to get :
[AM Part]  [PM Part] [Grand Total]
6               0              6
ScreenShoot.bmp
db1.mdb
0
rfedorov
Asked:
rfedorov
2 Solutions
 
HainKurtSr. System AnalystCommented:
try

select [AM Part], [PM Part], [AM Part] + [PM Part] as [Grand Total] from
(
SELECT a.Total as [AM Part],0 as [PM Part] from CountForAMPartofDay a
union
SELECT 0, p.Total from CountForPMPartofDay p
)

AM Part      PM Part      Grand Total
7      0      7
0
 
Nick67Commented:
@rfedorov

Looking at your earlier question, and now this one, you don't seem to be using the query editor in the right fashion.
It's akin to using a wrench as a hammer.

Union queries exists
Queries of queries exist too.
Both of them present performance issues if your app gets large.

Nevertheless.
You need a temptable and union query

select  [AM Part], [PM Part], [AM Part]+[PM Part] AS [Grand Total]
from (SELECT CountForAMPartofDay.Total AS [AM Part], 0 AS [PM Part]
FROM CountForAMPartofDay

union all

select 0 AS [AM Part], CountForPMPartofDay.Total AS [PM Part]
FROM CountForPMPartofDay) as temptable

An image and your sample, altered, are attached


query.jpg
db1.mdb
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now