Solved

query based on two queries

Posted on 2010-11-22
4
239 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
4 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

860 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