Solved

query based on two queries

Posted on 2010-11-22
4
246 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

733 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