Solved

Please help me with Access Query

Posted on 2011-03-11
2
206 Views
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

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
Comment
Question by:rfedorov
2 Comments
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 250 total points
ID: 35112053
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
 
LVL 26

Accepted Solution

by:
Nick67 earned 250 total points
ID: 35112203
@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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

813 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

10 Experts available now in Live!

Get 1:1 Help Now