Seniority,Rank,Employee

[gbecker]

Here is my issue. I have a table (emp) that contain fname, lname, DOB (date
of birth), DOH (Date of Hire), Seniority, and SS (last four of Social). The
Seniority field needs to be recalculated whenever the user wants. I need a
query or function (button pressed) that will take into account all employees.
It needs to group the records by DOH, when duplicates then by SS, and when
duplicates there by DOB. Then place the proper seniority number in the
column on the table for other reports. Example of how the table should look.
Last First DOH SS DOB SEN
Malone Sam 8/8/05 1234 5/5/1956 1
Balone Sue 8/12/07 1000 2/3/1976 2
Valone Bob 8/12/07 1002 4/4/1986 3
canny mary 8/12/07 1002 4/4/1987 4


Thank you for your help. I am using access 2000

 

[Michel Walsh]

SELECT a.[Last], a.[First], a.doh, a.ss, a.dob, COUNT(*) AS sen
FROM tableName AS a INNER JOIN tableName AS b
ON a.doh> b.doh OR
(a.doh=b.doh AND a.ss>b.ss OR
(a.ss=b.ss AND a.dob>= b.dob))
GROUP BY a.[Last], a.[First], a.doh, a.ss, a.dob




Hoping it may help,
Vanderghast, Access MVP

 

[gbecker via AccessMonster.com]

Thanks for your help. But I am confused.
I was able to get the results I wanted with this query
select a.lastname,a.firstname,a.doh,a.dob,a.ssnum from a order by a.doh,a.
ssnum,a.dob;

Here is the problem. I still can not get it to count and then save the
results back into the senority column in the table. I tried an insert
statement and a count statement with no luck in access 2000.
The query results need to look like this
Last First DOH SS DOB SEN
Malone Sam 8/8/05 1234 5/5/1956 1
Balone Sue 8/12/07 1000 2/3/1976 2
Valone Bob 8/12/07 1002 4/4/1986 3
canny mary 8/12/07 1002 4/4/1987 4

and I am getting
Last First DOH SS DOB
Malone Sam 8/8/05 1234 5/5/1956
Balone Sue 8/12/07 1000 2/3/1976
Valone Bob 8/12/07 1002 4/4/1986
canny mary 8/12/07 1002 4/4/1987

also after the query runs it needs to update the table with the correct sen
number
current table looks like
Last First DOH SS DOB SEN
Malone Sam 8/8/05 1234 5/5/1956 0
Balone Sue 8/12/07 1000 2/3/1976 0
Valone Bob 8/12/07 1002 4/4/1986 0
canny mary 8/12/07 1002 4/4/1987 0

after the query is ran i needs to look like
Last First DOH SS DOB SEN
Malone Sam 8/8/05 1234 5/5/1956 1
Balone Sue 8/12/07 1000 2/3/1976 2
Valone Bob 8/12/07 1002 4/4/1986 3
canny mary 8/12/07 1002 4/4/1987 4

hopefully this helps.

Thanks
Glen Becker

SELECT a.[Last], a.[First], a.doh, a.ss, a.dob, COUNT(*) AS sen
FROM tableName AS a INNER JOIN tableName AS b
ON a.doh> b.doh OR
(a.doh=b.doh AND a.ss>b.ss OR
(a.ss=b.ss AND a.dob>= b.dob))
GROUP BY a.[Last], a.[First], a.doh, a.ss, a.dob

Hoping it may help,
Vanderghast, Access MVP

Here is my issue. I have a table (emp) that contain fname, lname, DOB
(date

[quoted text clipped - 15 lines]

Thank you for your help. I am using access 2000

 

[Michel Walsh]

I was missing a parenthesis.


SELECT a.[Last], a.[First], a.doh, a.ss, a.dob, COUNT(*) AS sen
FROM tn AS a INNER JOIN tn AS b
ON a.doh> b.doh OR
(a.doh=b.doh AND (a.ss>b.ss OR
(a.ss=b.ss AND a.dob>= b.dob)))
GROUP BY a.[Last], a.[First], a.doh, a.ss, a.dob



I got:


Last First doh ss dob sen
Balone Sue 2007.08.12 1000 1976.02.03 2
Canny Mary 2007.08.12 1002 1987.04.04 4
Malone Sam 2005.08.08 1234 1956.05.05 1
Valone Bob 2007.08.12 1002 1986.04.04 3




My \original table is called tn.


You can use the query rather than the table with the SEN value, so there is no real need to 'update' the table, isn't?


Alternatively, make the query into a temporary table and use the temporary table for whatever use, including to update the original table. You won't be able to update the original table with the proposed query since that query is 'not updatable'.



Vanderghast, Access MVP

Thanks for your help. But I am confused.
I was able to get the results I wanted with this query
select a.lastname,a.firstname,a.doh,a.dob,a.ssnum from a order by a.doh,a.
ssnum,a.dob;

Here is the problem. I still can not get it to count and then save the
results back into the senority column in the table. I tried an insert
statement and a count statement with no luck in access 2000.
The query results need to look like this
Last First DOH SS DOB SEN
Malone Sam 8/8/05 1234 5/5/1956 1
Balone Sue 8/12/07 1000 2/3/1976 2
Valone Bob 8/12/07 1002 4/4/1986 3
canny mary 8/12/07 1002 4/4/1987 4

and I am getting
Last First DOH SS DOB
Malone Sam 8/8/05 1234 5/5/1956
Balone Sue 8/12/07 1000 2/3/1976
Valone Bob 8/12/07 1002 4/4/1986
canny mary 8/12/07 1002 4/4/1987

also after the query runs it needs to update the table with the correct sen
number
current table looks like
Last First DOH SS DOB SEN
Malone Sam 8/8/05 1234 5/5/1956 0
Balone Sue 8/12/07 1000 2/3/1976 0
Valone Bob 8/12/07 1002 4/4/1986 0
canny mary 8/12/07 1002 4/4/1987 0

after the query is ran i needs to look like
Last First DOH SS DOB SEN
Malone Sam 8/8/05 1234 5/5/1956 1
Balone Sue 8/12/07 1000 2/3/1976 2
Valone Bob 8/12/07 1002 4/4/1986 3
canny mary 8/12/07 1002 4/4/1987 4

hopefully this helps.

Thanks
Glen Becker

SELECT a.[Last], a.[First], a.doh, a.ss, a.dob, COUNT(*) AS sen
FROM tableName AS a INNER JOIN tableName AS b
ON a.doh> b.doh OR
(a.doh=b.doh AND a.ss>b.ss OR
(a.ss=b.ss AND a.dob>= b.dob))
GROUP BY a.[Last], a.[First], a.doh, a.ss, a.dob

Hoping it may help,
Vanderghast, Access MVP

Here is my issue. I have a table (emp) that contain fname, lname, DOB
(date

[quoted text clipped - 15 lines]

Thank you for your help. I am using access 2000