Need qry to show 2 records

[CEV]

Hi, I have what I beleive to be a difficult one. At least for me anyways. I
have a database that tracks Employees and EmployeePositions. Our agency
provides services for people with disabilties so we will have people that
will change positions sometimes to a different group home. I am trying to
create a qry that will show these changes. The qry I have so far is shown
below. It is pretty basic right now because any criteria I thought to add
would still only show 1 record. An Employee will show multiple records in
tblEmployeePositions as they change a position. Therefore, one postion
number for that Employee will have an DateEnded and then the next record
will have a DateStarted. These two dates will be the same and I would like
to create a report that shows which Position they were in and which Position
they went to. I'm hoping someone here can lend a helping hand.

SELECT tblEmployees.LastName, tblEmployees.FirstName,
tblEmployeePositions.PositionNumber, tblEmployeePositions.DateStarted,
tblEmployeePositions.DateEnded, tblEmployeePositions.Title,
tblEmployeePositions.Status, tblEmployeePositions.Hours,
tblEmployeePositions.Schedule
FROM tblEmployees INNER JOIN tblEmployeePositions ON
tblEmployees.EmployeeNumber = tblEmployeePositions.EmployeeNumber;

Thanks,

Chad

 

[Smartin]

Hi, I have what I beleive to be a difficult one. At least for me anyways. I
have a database that tracks Employees and EmployeePositions. Our agency
provides services for people with disabilties so we will have people that
will change positions sometimes to a different group home. I am trying to
create a qry that will show these changes. The qry I have so far is shown
below. It is pretty basic right now because any criteria I thought to add
would still only show 1 record. An Employee will show multiple records in
tblEmployeePositions as they change a position. Therefore, one postion
number for that Employee will have an DateEnded and then the next record
will have a DateStarted. These two dates will be the same and I would like
to create a report that shows which Position they were in and which Position
they went to. I'm hoping someone here can lend a helping hand.

SELECT tblEmployees.LastName, tblEmployees.FirstName,
tblEmployeePositions.PositionNumber, tblEmployeePositions.DateStarted,
tblEmployeePositions.DateEnded, tblEmployeePositions.Title,
tblEmployeePositions.Status, tblEmployeePositions.Hours,
tblEmployeePositions.Schedule
FROM tblEmployees INNER JOIN tblEmployeePositions ON
tblEmployees.EmployeeNumber = tblEmployeePositions.EmployeeNumber;

Thanks,

Chad

This query should show two records (or more) if the EmployeeNumber
exists more than once on the Positions table. Is this not what you want?

Or were you thinking of something like

Employee Name..old position..date changed..new position
?

 

[Charles Wang[MSFT]]

Hi Chad,
If you could give us a simple table definition with some data and tell us
what your expected result is, it will be great for us to better
understand your issue and find a resolution as soon as possible.


Sincerely,
Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[John Spencer]

What result do you want? Do you want one record with both the old and new
positions? Or do you want two records for each employee move?

One record with both old and new positions might look like the following
UNTESTED query
SELECT tblEmployees.LastName, tblEmployees.FirstName,
tblEmployeePositions.PositionNumber, tblEmployeePositions.DateStarted,
tblEmployeePositions.DateEnded, tblEmployeePositions.Title,
tblEmployeePositions.Status, tblEmployeePositions.Hours,
tblEmployeePositions.Schedule
, P2.PositionNumber as NewPosition
, P2.DateStarted as NewStart
, P2.DateEnded as NewEnd
, P2.Title as NewTitle
, P2.Status as NewStatus
, P2.Hours as NewHours
, P2.Schedule as NewSched
FROM (tblEmployees INNER JOIN tblEmployeePositions ON
tblEmployees.EmployeeNumber = tblEmployeePositions.EmployeeNumber)
INNER JOIN tblEmployeePositions as P2 ON
P2.EmployeeNumber = tblEmployeePositions.EmployeeNumber
AND P2.DateStarted= tblEmployeePositions.DateEnded

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[CEV]

John, this is simply amazing!!! I beleive this is exactly what I am looking
for. I'll need to play with it, create a report based on it, adjust to what
I need, and check it against some data to know for sure but as of now it
looks very good. If can spare another minute, please explain what the P2
references. Is that to create another table?

Thank You,

Chad

 

[John Spencer]

The P2 "creates" a separate instance of tblEmployeePositions. Basically,
think of it as two references to the same table - one reference uses the
original name and the second uses the P2 name.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[CEV]

If I could get your help again John, I would greatly appreciate it. I tried
this on my own and I have pasted the before and after qry below. The first
one gives me the total number of people that have seperated and sorted by
department. I also want to show the total number of active positions next to
the seperated number in the report and then show the % difference. But I
first have to get this part taken care of. I figured I could have the qry
create the second table to show all the active positions and have them
totaled and categorized by department.

Original QRY:
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, tblPositions.Inactive
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;

Attempted QRY:
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,

P2.PositionNumber as NewPositionNumber,

P2.Department as NewDepartment,

P2.Inactive as NewInactive,

FROM tblPositions INNER JOIN (tblEmployees INNER JOIN tblEmployeePositions
ON tblEmployees.EmployeeNumber = tblEmployeePositions.EmployeeNumber) ON
tblPositions.PositionNumber = tblEmployeePositions.PositionNumber AND
tblPositions as P2 ON P2.PositionNumber = tblPositions.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;



I thought I did this right, but it is not working.



Thanks,



Chad

 

[Charles Wang[MSFT]]

Hi,
For the attempted query, the issue came from the statement "AND
tblPositions as P2 ". I think it might be a minor writing mistake.
Single table reference cannot be used before/after conditional
conjunctions. You can use "INNER JOIN tblPositions as P2".

However this modification just ensures your SQL statement passing the
syntax examination. Since table definitions and your expected results are
not opened to us, we cannot assure that the statement can bring your
expected result.
Also, after the modification, the statement "INNER JOIN tblPositions as P2
ON P2.PositionNumber = tblPositions.PositionNumber" seems not sensible.

If you could post your tables simple definitions (not all of your columns
definition, only key columns referenced here) and tell us what is your
expected result, we can take an more efficient manner on this issue.
Appreciate your understanding on this.

Please feel free to let us know if you need further assistance.

Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[CEV]

Tables are as follows:

tblEmployees
LastName
FirstName
EmployeeNumber (Primary Key)
DateofHire
DateLeftAgency

tblPositions
PositionNumber (Primary Key)
Department
DatePositionCreated
Inactive
Open

tblEmployeePositions
EmplPosID (Primary Key)
EmployeeNumber
PositionNumber
DateStarted
DateEnded
Title
etc.

My original qry gives me the number of employees that have left the agency
within a given time frame and it is categorized by department. I want my new
qry to also show next to that number, the total number of active positions
in that department. This way I should be able to have another text box next
to those that will calculate the % of turnover for that department.

Thank You for any info you can provide. This is a seperate qry from what my
original post was but since it was referencing the P2 table as before.

Thanks,

Chad

 

[CEV]

Well, I got the query to work by showing me results, but I'm thinking I'm
going in the wrong direction here because it is only showing me records for
people that have seperated. I have the footer of my report adding up the
total number of people that have seperated per department. How can I also
have it add up how many total active positions are in each department so I
can get a turnover percentage? My qry is shown below. My tables are in my
previous response.

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;

Thanks,

Chad

 

[Charles Wang[MSFT]]

Hi Chad,
Thanks for your response.

I am not clear of your meaning active position, however from your
description of separated position, I think that the active position seems
the old position that belongs to the man who was just transferred.
If I have misunderstood, please let me know.

I think that you would like the following SQL statement:
/* e1: tblEmployees; p1: tblPositions; ep1, ep2: tblEmployeePositions */
SELECT e1.LastName, e1.FirstName,
p1.Department, e1.DateLeftAgency
FROM tblPositions p1 INNER JOIN
(
(tblEmployeePositions ep1 INNER JOIN tblEmployees e1 on ep1.EmployeeNumber
= e1.EmployeeNumber)
INNER JOIN tblEmployeePositions ep2 ON (ep1.EmployeeNumber =
ep2.EmployeeNumber and ep1.DateEnded=ep2.DateStarted and ep2.DateEnded is
NULL)
) ON p1.PositionNumber=ep2.PositionNumber
WHERE e1.DateLeftAgency between [Forms]![frmReports]![txtBeginDate] And
[Forms]![frmReports]![txtEndDate]
Order by p1.Department

I assume that the end date of a new transferred employee's new position is
NULL in the database.

Hope this helps.
If you have any other questions or concerns, please feel free to contact us.

Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================