Solved

Please help me with Access Query

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

746 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

11 Experts available now in Live!

Get 1:1 Help Now