comparison between records

[Guest]

Hi,

I'm struggling with a query and could do with some help:

I have a table that looks like this:

Rank EmployeeID Date WorkDuty
CC 1000 14/4/07 FLY
CC 1000 15/4/07 REST
.....
CC 1000 01/5/07 FLY
.....

etc for many different employee IDs and ranks. Data is loaded 1 calendar
month at a time (rosters).

What I need to see is: in each month, by rank, how many people left. The way
I want to assertain whether they have left is if the Employee ID was in the
previous month, but not in the current month. I want to do this in a single
query looking at all months at the same time. Essentially to return crosstab
'Leavers':

Rank Jan Feb Mar....
CC 20 22 31
SCC 7 9 10

Is there any way I can do this other than outer joins, filters and union
queries?

Thanks

B

 

[Michel Walsh]

untested.

TRANSFORM COUNT(*)
SELECT z.rank
FROM (
SELECT DISTINCT a.rank, a.employeeID, DatePart("m", a.Date) As
theMonth
FROM myTable As a LEFT JOIN myTable As b
ON (a.rank = b.rank AND a.employeeID = b.employeeID AND
Datediff("m", a.date, b.date) =1)
WHERE b.rank IS NULL ) As z
GROUP BY z.rank
PIVOT z.theMonth


Note that if an employee moves from CC to SCC, the employee will be listed
as having leaving the CC.

The innermost query keep ONE record per employee, per rank, per month if
that employee-rank does NOT appear anywhere in the data for the next month.
The outermost query makes a crosstab - counting of all these, per rank,
pivoting on the months.


Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Counting the employeeID may give a NET change of 0 employee, while 10 have
left, and 10 new ones get in. That CAN be what the OP wants, though.


Vanderghast, Access MVP

Two stages:

1. Create a query.

Depress the Total button on the toolbar, and set up fields:
- Group by Rank
- Max of Date
- Count of EmployeeID.

Under the date field, you want to eliminate those who are still present in
the current month, so enter criteria of:
<= Date() - Day(Date())

Test. Save. Close.

2. Create another query, using the first one as the input "table."
Change to a Crosstab.

Enter this into the Field row:
The Year: Year([MaxOfDate])
and set it as a Row Heading.

In the next column, choose Rank, and set as Row Heading.

In the next column, enter this in the Field row:
TheMonth: Month([MaxOfDate])
and set as Column Heading.

In the next column, chose CountOfEmployeeID.
Choose Sum.
Set as Value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I'm struggling with a query and could do with some help:

I have a table that looks like this:

Rank EmployeeID Date WorkDuty
CC 1000 14/4/07 FLY
CC 1000 15/4/07 REST
....
CC 1000 01/5/07 FLY
....

etc for many different employee IDs and ranks. Data is loaded 1 calendar
month at a time (rosters).

What I need to see is: in each month, by rank, how many people left. The
way
I want to assertain whether they have left is if the Employee ID was in
the
previous month, but not in the current month. I want to do this in a
single
query looking at all months at the same time. Essentially to return
crosstab
'Leavers':

Rank Jan Feb Mar....
CC 20 22 31
SCC 7 9 10

Is there any way I can do this other than outer joins, filters and union
queries?

Thanks

B

 

[Allen Browne]

Two stages:

1. Create a query.

Depress the Total button on the toolbar, and set up fields:
- Group by Rank
- Max of Date
- Count of EmployeeID.

Under the date field, you want to eliminate those who are still present in
the current month, so enter criteria of:
<= Date() - Day(Date())

Test. Save. Close.

2. Create another query, using the first one as the input "table."
Change to a Crosstab.

Enter this into the Field row:
The Year: Year([MaxOfDate])
and set it as a Row Heading.

In the next column, choose Rank, and set as Row Heading.

In the next column, enter this in the Field row:
TheMonth: Month([MaxOfDate])
and set as Column Heading.

In the next column, chose CountOfEmployeeID.
Choose Sum.
Set as Value.

 

[Michel Walsh]

Note that DateDiff("m", date1, date2) = 1 DOES NOT implies there is exactly
one month of difference between the two dates, NO, it just means there is
ONE CHANGE of MONTH, we have to turn the 'paper page' of most calendar once.

? DateDiff( "m", "30 Apr 2007" , "01 May 2007")
1

while clearly, there is just ONE day of difference between the two dates...
there is also ONE CHANGE OF month. All the DateDiff results should be see as
how many changes of {year, month, day}, rather than the difference in {year,
month, day}, really. I know the 'name' of the function is confusing, but,
well, after all, not everyone can be called Vanderghast... (humpf)


Vanderghast, Access MVP