Solved

query based on two queries

Posted on 2010-11-22
4
253 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

696 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