Report based on 2 queries?

  • Thread starter Thread starter CEV
  • Start date Start date
C

CEV

I currently have a report that gives me varies info about employees that
have seperated and the positions they held. I'm using this to track
seperation info per department. I have a form that I enter a date range to
get this info for peoples records that contain a Seperation Date. I also
however want to get a total of how many positions there are in every
department that are not checked as Inactive and then also of how many that
are checked as Open. The current query I have though will only be able to
count the positions that are between the dates I enter into the form. How
can I accomplish what I am trying to do?

Thanks,

Chad
 
You can put some code in the FORMAT event of the report that does a DCOUNT
from a table or query to supply the figures.
Your question was rather vague...

Dorian
 
Hello Chat,

To understand the issue better, will you provide us a sample database, and
then send it to me? You could remove "online" from my displayed email
address. Since the issue is related to the schema of the tables, we might
get information from that. You may want to describe the exact requirment
according to the sample database. Thank you for your time!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
These are the tables I have with the fields listed below each:



tblEmployees

LastName

FirstName

EmployeeNumber (Primary Key)

DateofHire

DateLeftAgency

Terminated (Yes/No)

Age

DateofBirth

Gender

Student (Yes/No)



tblPositions

PositionNumber (Primary Key)

Department

DatePositionCreated

Inactive (Yes/No)

Open (Yes/No)



tblEmployeePositions

EmplPosID (Autonumber)(Primary Key)

EmployeeNumber

PositionNumber

DateStarted

DateEnded

Transfer (Yes/No)(Checked when an employee transfers from one position to
another)



My report is based on the following query:



SELECT tblEmployees.LastName, tblEmployees.FirstName,
tblPositions.Department, tblEmployeePositions.Status,
tblEmployeePositions.Hours, tblEmployeePositions.PositionNumber,
tblEmployees.DateofHire, tblEmployees.DateLeftAgency,
Diff2Dates("ymd",[tblEmployees]![DateofHire],[tblEmployees]![DateLeftAgency])
AS Expr1, tblEmployees.Terminated, tblEmployeePositions.Schedule,
tblEmployees.Student

FROM tblPositions INNER JOIN (tblEmployees INNER JOIN tblEmployeePositions
ON tblEmployees.EmployeeNumber = tblEmployeePositions.EmployeeNumber) ON
tblPositions.PositionNumber = tblEmployeePositions.PositionNumber

WHERE (((tblEmployees.DateLeftAgency) Between
[Forms]![frmReports]![txtBeginDate] And [Forms]![frmReports]![txtEndDate])
AND ((tblEmployeePositions.DateStarted)=(SELECT Max(Temp.DateStarted) FROM
tblEmployeePositions as Temp WHERE Temp.EmployeeNumber =
tblEmployeePositions.EmployeeNumber)))

ORDER BY tblPositions.Department;



In the report I want to add how many total current positions there are per
Department that are not Inactive. I would like to find out how many
positions in each Department that are Open. The current query only finds
Employees with DateLeftAgency between the dates I specify. Is there a way to
accomplish this? How?



Thanks,



Chad
 
Hello Chad,

Thank you for the detailed information provided. Based on the current
situation, you may want to use 2 subquery to get the information and then
use a parent query to get information form these 2 subquery.


1. First subquery is what you have lised

2. Second subquery is to get the number of open postition of one department

select department, count(Inactive) as activepostions from tblposition
where inactive='No' group by department

3. Third subquery is similar but for Open posistions

4. Parent query is to inner join this three table on department.

Then you could the the parent query in the report, and the result is
something like:

Employee department activeposistions openpositions

a 1 3 3
b 1 3 3
c 2 1 1
d 2 1 1

If this does not meet your requrirement, please feel free to let's know. I
look forward to your reply.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 
I actually already have one of the other queries.

SELECT tblPositions.Department, Sum(Abs([Open])) AS CountOfYes,
tblPositions.Inactive
FROM tblPositions
WHERE (((tblPositions.Open)=Yes))
GROUP BY tblPositions.Department, tblPositions.Inactive
HAVING (((tblPositions.Inactive)=No));

How would I join that query and this one to create the third query? I do not
yet know how to create queries in the SQL view. I always use the design
view.

SELECT tblEmployees.LastName, tblEmployees.FirstName,
tblPositions.Department, tblEmployeePositions.Status,
tblEmployeePositions.Hours, tblEmployeePositions.PositionNumber,
tblEmployees.DateofHire, tblEmployees.DateLeftAgency,
Diff2Dates("ymd",[tblEmployees]![DateofHire],[tblEmployees]![DateLeftAgency])
AS Expr1, tblEmployees.Terminated, tblEmployeePositions.Schedule,
tblEmployees.Student
FROM tblPositions INNER JOIN (tblEmployees INNER JOIN tblEmployeePositions
ON tblEmployees.EmployeeNumber = tblEmployeePositions.EmployeeNumber) ON
tblPositions.PositionNumber = tblEmployeePositions.PositionNumber
WHERE (((tblEmployees.DateLeftAgency) Between
[Forms]![frmReports]![txtBeginDate] And [Forms]![frmReports]![txtEndDate])
AND ((tblEmployeePositions.DateStarted)=(SELECT Max(Temp.DateStarted) FROM
tblEmployeePositions as Temp WHERE Temp.EmployeeNumber =
tblEmployeePositions.EmployeeNumber)))
ORDER BY tblPositions.Department;

And then would I just base my report on this third query?

Thanks for the help!!

Chad
 
Hello chad,

I assume you use mdb other than adp since you could use Stored procedure to
do this with temp tables.

You could use the following method to create the parent query based on the
2 queries:

1. Select Query on left panel, click New, select Design View

2. In Show Table dialog, click to select Queries tab, and check the 2
quries you want.

3. Click View->SQL View, and you could use 2 quires as 2 tables and join
them as you want. For example:

select sub1.departement, sub1.Countofyes, sub2.lastname, sub2.firstname
from sub1 inner join sub2 on sub1.department=sub2.department

4. You could run the query by clicking "!" to see the result.

5. After that, you could use the parent query as the data source of the
report directly as that when using the original query.

If anything is unclear, please feel free to let's know. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 
I beleive I did what you suggested:

SELECT qrySeperationReport.Department, qrySeperationReport.countofyes AS
Expr2, qryOpenPositionCount.LastName AS Expr3,
qryOpenPositionCount.FirstName AS Expr4, qrySeperationReport.LastName,
qrySeperationReport.FirstName, qrySeperationReport.Status,
qrySeperationReport.Hours, qrySeperationReport.PositionNumber,
qrySeperationReport.DateofHire, qrySeperationReport.DateLeftAgency,
qrySeperationReport.Expr1, qrySeperationReport.Terminated,
qrySeperationReport.Schedule, qrySeperationReport.Student
FROM qrySeperationReport INNER JOIN qryOpenPositionCount ON
qrySeperationReport.Department = qryOpenPositionCount.Department
WHERE (((qrySeperationReport.DateLeftAgency) Between
[Forms]![frmReports]![txtBeginDate] And [Forms]![frmReports]![txtEndDate]));


But when running it, the query asks me to "Enter Parameter Value" for
qrySeperationReport.countofyes, qryOpenPositionCount.LastName, and
qryOpenPositionCount.FirstName.

Why does it asks for these parameters and how does the countofyes know what
field it should count?

Thanks,

Chad
 
Hello chad,

I understand that you are prompted to input qrySeperationReport.countofyes,
qryOpenPositionCount.LastName, and qryOpenPositionCount.FirstName when you
try to run the parent query.

Usually this issue occurs when the fields do not exist in the corresponding
sub queries. Please make sure the fields are defined properly in the
sub-quires. You can confirm this by create a new parent query but use only
one sub query as source to test.

Based on my test, the query shall work on your side. It seems that you may
have confused sub-queries name. If the query name of the following query is
qryOpenPositionCount (Becasue I found qrySeperationReport.Terminated,
qrySeperationReport.DateofHire etc from the another query), countofyes
should be filed of query qryOpenPositionCount, and Firstname/lastname are
not fileds of qryOpenPositionCount.

SELECT tblPositions.Department, Sum(Abs([Open])) AS CountOfYes,
tblPositions.Inactive
FROM tblPositions
WHERE (((tblPositions.Open)=Yes))
GROUP BY tblPositions.Department, tblPositions.Inactive
HAVING (((tblPositions.Inactive)=No));

I think the query might be

SELECT qrySeperationReport.Department, qryOpenPositionCount.countofyes AS
Expr2, qrySeperationReport.LastName,
qrySeperationReport.FirstName, qrySeperationReport.Status,
qrySeperationReport.Hours, qrySeperationReport.PositionNumber,
qrySeperationReport.DateofHire, qrySeperationReport.DateLeftAgency,
qrySeperationReport.Expr1, qrySeperationReport.Terminated,
qrySeperationReport.Schedule, qrySeperationReport.Student
FROM qrySeperationReport INNER JOIN qryOpenPositionCount ON
qrySeperationReport.Department = qryOpenPositionCount.Department
WHERE (((qrySeperationReport.DateLeftAgency) Between
[Forms]![frmReports]![txtBeginDate] And [Forms]![frmReports]![txtEndDate]));


If the issue persists, will you please extract the data in the database and
send me a copy of sample database included the referenced
tables/forms/reports. You could remove "online" in my displayed email
address. I look forward to your reply. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 
Back
Top