?
Solved

query based on two queries

Posted on 2010-11-22
4
Medium Priority
?
269 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

578 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