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
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
LVL 51

Assisted Solution

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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query assistance 16 46
Requesting help with creating an SQL query with 2 tables 6 31
Comparison query - 4 columns 9 42
SQL Calculation syntax based on a parameter 2 30
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

763 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