Difficult query

  • Thread starter Thread starter PayeDoc
  • Start date Start date
P

PayeDoc

Hello All

I have a large-ish table [x confirmed] (~80,000 records, 25 fields) which
includes the fields 'employee' (text), 'mthnumber' (integer) and 'nicode'
(text). Most of the records with the same 'employee' value have the same
'nicode' value, but some 'employee' values have more than one corresponding
'nicode' value. I need a query that will return, for each value of
'employee' that has more than one corresponding 'nicode' value, the lowest
value of 'mthnumber' that is associated with the 'nicode' value that has the
highest 'mthnumber' value. To put this into English, 'mthnumber' represents
month numbers (where 1 = January 2008, 2 = February 2008, etc. etc. etc.),
and I need the month number in which the changed value of 'nicode' first
occurs, for each employee.

I'm sure this can be done, but I've been trying and failing to get it right
all day and now my brain hurts!!

Hope someone can help.
Many thanks
Leslie Isaacs
 
Leslie

Are you saying that you want to see the minimum value of [mthnumber] for
each employee's unique [nicode]? If you started with that query, then
created another query that shows each employees [nicode] with the maximum
[mthnumber], you could use a third query to join on employee and nicode.

Hmmm, I don't know your business rules, but is there a possibility you could
have a "tie"? Could one employee have two nicodes, both with mthnumber = ,
say, 10?

By the way, if mthnumber = 1 = January, 2008, does that mean January, 2009
is mthnumber = 13? If you want to uniquely identify the month, do you have
a date/time field in that table that you could use in a query instead of
recording mthnumber?

Good luck!

Jeff Boyce
Microsoft Office/Access MVP
 
Hello Jeff

Many thanks for your reply.
The problem I'm having is in filtering out all those employees who only have
one 'nicode' value (because there are thousands of them!).
I should add that each employee can only have one record in the table with
any given 'mthnumber' value (so no ties).

Does that help?

Thanks again
Les


Jeff Boyce said:
Leslie

Are you saying that you want to see the minimum value of [mthnumber] for
each employee's unique [nicode]? If you started with that query, then
created another query that shows each employees [nicode] with the maximum
[mthnumber], you could use a third query to join on employee and nicode.

Hmmm, I don't know your business rules, but is there a possibility you could
have a "tie"? Could one employee have two nicodes, both with mthnumber = ,
say, 10?

By the way, if mthnumber = 1 = January, 2008, does that mean January, 2009
is mthnumber = 13? If you want to uniquely identify the month, do you have
a date/time field in that table that you could use in a query instead of
recording mthnumber?

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

PayeDoc said:
Hello All

I have a large-ish table [x confirmed] (~80,000 records, 25 fields) which
includes the fields 'employee' (text), 'mthnumber' (integer) and 'nicode'
(text). Most of the records with the same 'employee' value have the same
'nicode' value, but some 'employee' values have more than one
corresponding
'nicode' value. I need a query that will return, for each value of
'employee' that has more than one corresponding 'nicode' value, the lowest
value of 'mthnumber' that is associated with the 'nicode' value that has
the
highest 'mthnumber' value. To put this into English, 'mthnumber'
represents
month numbers (where 1 = January 2008, 2 = February 2008, etc. etc. etc.),
and I need the month number in which the changed value of 'nicode' first
occurs, for each employee.

I'm sure this can be done, but I've been trying and failing to get it
right
all day and now my brain hurts!!

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
 
So, you want Employees with a "Count" of [nicode] > 1? Do THAT in your
first query, then "chain" on the next queries.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


PayeDoc said:
Hello Jeff

Many thanks for your reply.
The problem I'm having is in filtering out all those employees who only
have
one 'nicode' value (because there are thousands of them!).
I should add that each employee can only have one record in the table with
any given 'mthnumber' value (so no ties).

Does that help?

Thanks again
Les


Jeff Boyce said:
Leslie

Are you saying that you want to see the minimum value of [mthnumber] for
each employee's unique [nicode]? If you started with that query, then
created another query that shows each employees [nicode] with the maximum
[mthnumber], you could use a third query to join on employee and nicode.

Hmmm, I don't know your business rules, but is there a possibility you could
have a "tie"? Could one employee have two nicodes, both with mthnumber = ,
say, 10?

By the way, if mthnumber = 1 = January, 2008, does that mean January,
2009
is mthnumber = 13? If you want to uniquely identify the month, do you have
a date/time field in that table that you could use in a query instead of
recording mthnumber?

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

PayeDoc said:
Hello All

I have a large-ish table [x confirmed] (~80,000 records, 25 fields) which
includes the fields 'employee' (text), 'mthnumber' (integer) and 'nicode'
(text). Most of the records with the same 'employee' value have the
same
'nicode' value, but some 'employee' values have more than one
corresponding
'nicode' value. I need a query that will return, for each value of
'employee' that has more than one corresponding 'nicode' value, the lowest
value of 'mthnumber' that is associated with the 'nicode' value that
has
the
highest 'mthnumber' value. To put this into English, 'mthnumber'
represents
month numbers (where 1 = January 2008, 2 = February 2008, etc. etc. etc.),
and I need the month number in which the changed value of 'nicode'
first
occurs, for each employee.

I'm sure this can be done, but I've been trying and failing to get it
right
all day and now my brain hurts!!

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
First query (saved as qBigCode) might look something like the following.

SELECT Employee, Max(Nicode) as BigNicode
FROM [x Confirmed] as X
WHERE Employee in
(SELECT Employee
FROM [X Confirmed]
GROUP BY Employee
HAVING Max(Nicode) <> Min(NiCode)
GROUP BY Employee

Next query uses that query linked to your table.

SELECT X1.Employee
, Q.BigNiCode
, Min(mthNumber) as FirstMonth
FROM [x Confirmed] as x1 INNER JOIN qBigCode as Q
ON X1.Employee = Q.Employee
AND X1.NiCode = Q.BigNiCode
GROUP BY X1.Employee, Q.BigNiCode

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Jeff

Thanks for your further reply.
I tried that: the problem is that a given 'employee' will be in table [x
confirmed] dozens of tiimes, normally with the sane 'nicode' each time (and
those are the cases I need to exclude) but sometimes with one of two (and
occasionally one of three) different 'nicode' value. If I do a Count for
each employee/nicode combination, I get the total number of records with
that employee/nicode - which may be several dozen. I cannot get Count to
give the number of unique employee/nicode combinations.

I did say this was a dificult query (for me!).
Thanks again
Les


Jeff Boyce said:
So, you want Employees with a "Count" of [nicode] > 1? Do THAT in your
first query, then "chain" on the next queries.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


PayeDoc said:
Hello Jeff

Many thanks for your reply.
The problem I'm having is in filtering out all those employees who only
have
one 'nicode' value (because there are thousands of them!).
I should add that each employee can only have one record in the table
with
any given 'mthnumber' value (so no ties).

Does that help?

Thanks again
Les


Jeff Boyce said:
Leslie

Are you saying that you want to see the minimum value of [mthnumber] for
each employee's unique [nicode]? If you started with that query, then
created another query that shows each employees [nicode] with the
maximum
[mthnumber], you could use a third query to join on employee and nicode.

Hmmm, I don't know your business rules, but is there a possibility you could
have a "tie"? Could one employee have two nicodes, both with mthnumber
= ,
say, 10?

By the way, if mthnumber = 1 = January, 2008, does that mean January,
2009
is mthnumber = 13? If you want to uniquely identify the month, do you have
a date/time field in that table that you could use in a query instead of
recording mthnumber?

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

Hello All

I have a large-ish table [x confirmed] (~80,000 records, 25 fields) which
includes the fields 'employee' (text), 'mthnumber' (integer) and 'nicode'
(text). Most of the records with the same 'employee' value have the
same
'nicode' value, but some 'employee' values have more than one
corresponding
'nicode' value. I need a query that will return, for each value of
'employee' that has more than one corresponding 'nicode' value, the lowest
value of 'mthnumber' that is associated with the 'nicode' value that
has
the
highest 'mthnumber' value. To put this into English, 'mthnumber'
represents
month numbers (where 1 = January 2008, 2 = February 2008, etc. etc. etc.),
and I need the month number in which the changed value of 'nicode'
first
occurs, for each employee.

I'm sure this can be done, but I've been trying and failing to get it
right
all day and now my brain hurts!!

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
Hello John

Many thanks for your suggestion. I'm not in the office now (it's Sunday
evening here in the UK as I write this!), but will try your queries in the
morning and let you know how I get on.

Thanks again
Les


John Spencer said:
First query (saved as qBigCode) might look something like the following.

SELECT Employee, Max(Nicode) as BigNicode
FROM [x Confirmed] as X
WHERE Employee in
(SELECT Employee
FROM [X Confirmed]
GROUP BY Employee
HAVING Max(Nicode) <> Min(NiCode)
GROUP BY Employee

Next query uses that query linked to your table.

SELECT X1.Employee
, Q.BigNiCode
, Min(mthNumber) as FirstMonth
FROM [x Confirmed] as x1 INNER JOIN qBigCode as Q
ON X1.Employee = Q.Employee
AND X1.NiCode = Q.BigNiCode
GROUP BY X1.Employee, Q.BigNiCode

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello All

I have a large-ish table [x confirmed] (~80,000 records, 25 fields) which
includes the fields 'employee' (text), 'mthnumber' (integer) and 'nicode'
(text). Most of the records with the same 'employee' value have the same
'nicode' value, but some 'employee' values have more than one
corresponding
'nicode' value. I need a query that will return, for each value of
'employee' that has more than one corresponding 'nicode' value, the
lowest
value of 'mthnumber' that is associated with the 'nicode' value that has
the
highest 'mthnumber' value. To put this into English, 'mthnumber'
represents
month numbers (where 1 = January 2008, 2 = February 2008, etc. etc.
etc.),
and I need the month number in which the changed value of 'nicode' first
occurs, for each employee.

I'm sure this can be done, but I've been trying and failing to get it
right
all day and now my brain hurts!!

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
 
Hello John

Done!!
Many thanks for your help.

Les


Leslie Isaacs said:
Hello John

Many thanks for your suggestion. I'm not in the office now (it's Sunday
evening here in the UK as I write this!), but will try your queries in the
morning and let you know how I get on.

Thanks again
Les


John Spencer said:
First query (saved as qBigCode) might look something like the following.

SELECT Employee, Max(Nicode) as BigNicode
FROM [x Confirmed] as X
WHERE Employee in
(SELECT Employee
FROM [X Confirmed]
GROUP BY Employee
HAVING Max(Nicode) <> Min(NiCode)
GROUP BY Employee

Next query uses that query linked to your table.

SELECT X1.Employee
, Q.BigNiCode
, Min(mthNumber) as FirstMonth
FROM [x Confirmed] as x1 INNER JOIN qBigCode as Q
ON X1.Employee = Q.Employee
AND X1.NiCode = Q.BigNiCode
GROUP BY X1.Employee, Q.BigNiCode

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello All

I have a large-ish table [x confirmed] (~80,000 records, 25 fields) which
includes the fields 'employee' (text), 'mthnumber' (integer) and 'nicode'
(text). Most of the records with the same 'employee' value have the same
'nicode' value, but some 'employee' values have more than one
corresponding
'nicode' value. I need a query that will return, for each value of
'employee' that has more than one corresponding 'nicode' value, the
lowest
value of 'mthnumber' that is associated with the 'nicode' value that has
the
highest 'mthnumber' value. To put this into English, 'mthnumber'
represents
month numbers (where 1 = January 2008, 2 = February 2008, etc. etc.
etc.),
and I need the month number in which the changed value of 'nicode' first
occurs, for each employee.

I'm sure this can be done, but I've been trying and failing to get it
right
all day and now my brain hurts!!

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
Back
Top