Solved

Please help me with Access Query

Posted on 2011-03-11
2
209 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 53

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN 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

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MYSQL responding very slow 3 67
RAISERROR WITH NOWAIT 2 29
SQL Query Grouping Question Record Inserts 9 63
Get data from two MySQL tables 6 65
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

732 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