?
Solved

query based on two queries

Posted on 2010-11-22
4
Medium Priority
?
274 Views
Last Modified: 2014-06-16
All

I thought this would be easy....

I have two queries that I need to 'join' to give one query to then build a report.

The queries are attached.  What I need is a single query to give me the percentage of SoE Current (Eligible - Past due) / Eligible

I have been going around in circles - tried union, tried basic query and what I get returned is not correct

Any ideas....

rgds
SELECT DeptByArea.School, Z_ActiveEmployee_tblStatementDetails.[Eligibility Group], Count(Z_ActiveEmployee_tblStatementDetails.[Employee ID]) AS TotalCountEligible
FROM ((DeptByArea INNER JOIN ((Z_ActiveEmployee_tblStatementDetails INNER JOIN Z_NotOnLeave ON Z_ActiveEmployee_tblStatementDetails.[Employee ID]=Z_NotOnLeave.[Employee ID]) INNER JOIN [Z_TermDate<90NOT] ON Z_ActiveEmployee_tblStatementDetails.[Employee ID]=[Z_TermDate<90NOT].[Employee ID]) ON DeptByArea.[Department Description]=Z_ActiveEmployee_tblStatementDetails.[Department Description]) INNER JOIN [EmpTerm>12] ON Z_ActiveEmployee_tblStatementDetails.[Employee ID]=[EmpTerm>12].[Employee ID]) INNER JOIN [LastStartDate>2mths] ON Z_ActiveEmployee_tblStatementDetails.[Employee ID]=[LastStartDate>2mths].[Employee ID]
GROUP BY DeptByArea.School, Z_ActiveEmployee_tblStatementDetails.[Eligibility Group];



SELECT Z_CASS_SoEPastDue_SoEDueNow_Count.School, Z_CASS_SoEPastDue_SoEDueNow_Count.[Eligibility Group], Count(Z_CASS_SoEPastDue_SoEDueNow_Count.[SoE Past Due]) AS TotalCountPastDue
FROM Z_CASS_SoEPastDue_SoEDueNow_Count
GROUP BY Z_CASS_SoEPastDue_SoEDueNow_Count.School, Z_CASS_SoEPastDue_SoEDueNow_Count.[Eligibility Group];

Open in new window

0
Comment
Question by:shaz0503
2 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 total points
ID: 34193591
SELECT DeptByArea.School, Z_ActiveEmployee_tblStatementDetails.[Eligibility Group], 
1 - 1.0*Count(Z_ActiveEmployee_tblStatementDetails.[Employee ID])/DCount(
"[SoE Past Due]",
"Z_CASS_SoEPastDue_SoEDueNow_Count",
"School='" & DeptByArea.School & "' AND [Eligibility Group]='" & Z_ActiveEmployee_tblStatementDetails.[Eligibility Group] & "'"
) AS TotalCountEligible
FROM ((DeptByArea INNER JOIN ((Z_ActiveEmployee_tblStatementDetails 
INNER JOIN Z_NotOnLeave ON Z_ActiveEmployee_tblStatementDetails.[Employee ID]=Z_NotOnLeave.[Employee ID]) 
INNER JOIN [Z_TermDate<90NOT] ON Z_ActiveEmployee_tblStatementDetails.[Employee ID]=[Z_TermDate<90NOT].[Employee ID]) 
ON DeptByArea.[Department Description]=Z_ActiveEmployee_tblStatementDetails.[Department Description]) 
INNER JOIN [EmpTerm>12] ON Z_ActiveEmployee_tblStatementDetails.[Employee ID]=[EmpTerm>12].[Employee ID]) 
INNER JOIN [LastStartDate>2mths] ON Z_ActiveEmployee_tblStatementDetails.[Employee ID]=[LastStartDate>2mths].[Employee ID]
GROUP BY DeptByArea.School, Z_ActiveEmployee_tblStatementDetails.[Eligibility Group];

Open in new window


Notice that in DCount, I twice used the pattern '" & <field> & "'
Those single quotes assume that <field> is TEXT.
If field (any/either) of those two are NUMBERs, then drop the single quotes from both sides, e.g. if School is a numeric ID

"School=" & DeptByArea.School & " AND
0
 

Author Comment

by:shaz0503
ID: 34828441
Haven't forgot this - just seems to move up and down my priority list...

will hopefully get to it soon

rgds
0

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

589 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