Please help me with Access Query

Posted on 2011-03-11
Last Modified: 2012-05-11
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

Sum(AM.Count) AS Total, AM.Date
WHERE (((AM.Date)=Format(Now(),"mm/dd/yyyy")))
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
Sum(PM.Count) AS Total,
WHERE (((PM.Date)=Format(Now(),"mm/dd/yyyy")))
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"
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
Question by:rfedorov
LVL 51

Assisted Solution

HainKurt earned 250 total points
ID: 35112053

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
SELECT 0, p.Total from CountForPMPartofDay p

AM Part      PM Part      Grand Total
7      0      7
LVL 26

Accepted Solution

Nick67 earned 250 total points
ID: 35112203

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.

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


Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now