A QUERY - SENIORITY

[Guest]

In need of assistance generating a query that would return results based on
ethnicity of the population of my company.

I need to (i dont know which is best) query/sort/filter/etc...) out the
first person in each designated race by the date they arrived here. I want
the data that is returned to only provide the oldest person by date (not age)
in that race/eth field.

I am trying to use the FIRST function under the Date field which I have
sorted ascendingly.

As it stands now I am trying the following:
Date
first
ascending ----and then----

under my RACE field i have my criteria set to (like "blk" or "whi" or "mex"
or "oth")

 

[Guest]

I recommend you check with your Human Resources/Legal department as race has
no bearing on seniority.

 

[Jeff Boyce]

Your definition of "First" and Access' definition probably don't match. If
you are working with a Date/Time field, consider using Maximum and Minimum
(depends on how you sort).

I don't have a clear picture of the underlying data you are using, and
queries start with data!

Have you looked into using the Totals query, grouping by your ethnicity
fields and using the Max (or Min) of the [some kind of date field]?

By the way, if you have named the field holding your date "Date", you will
only confuse yourself AND Access -- this is a reserved word in Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Van T. Dinh]

Try Min instead of First. First in JET SQL means the first Record found,
not the earliest.

What you after is the earliest but this is translated to MIN for a Date
field ...

 

[Guest]

I work for the Dept of Corrections where race is a classifying factor. I
guess my post should really not be titled SENIORITY, I was just trying to be
politically correct.
Basically my program is as follows:

We have inmates that we train to be firefighters, we have to keep our
training centers racially balanced and therefore only send out certain
quantities of inmates of certain races at a given time to each of our
training locations. However, we like to send them out on a first come first
serve basis therefore I would need to know who has been waiting the longest
by using their arrival date (thats where I came up with the seniority thing).

Some suggeted that I use the MIN/MAX feature but it was returning all my
table entries and just sorting them by date, what i need it to do is return
the earliest mexican, earliest black, earlies, white, and earliest other
inmate on the waiting list.

So the data in my query consists of name, date, race, location, etc. Its
almost like a staff roster, they are considered employee's

Thanks for all of your assistance.
Carla
aka
cornuts

Your definition of "First" and Access' definition probably don't match. If
you are working with a Date/Time field, consider using Maximum and Minimum
(depends on how you sort).

I don't have a clear picture of the underlying data you are using, and
queries start with data!

Have you looked into using the Totals query, grouping by your ethnicity
fields and using the Max (or Min) of the [some kind of date field]?

By the way, if you have named the field holding your date "Date", you will
only confuse yourself AND Access -- this is a reserved word in Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

In need of assistance generating a query that would return results based
on
ethnicity of the population of my company.

I need to (i dont know which is best) query/sort/filter/etc...) out the
first person in each designated race by the date they arrived here. I
want
the data that is returned to only provide the oldest person by date (not
age)
in that race/eth field.

I am trying to use the FIRST function under the Date field which I have
sorted ascendingly.

As it stands now I am trying the following:
Date
first
ascending ----and then----

under my RACE field i have my criteria set to (like "blk" or "whi" or
"mex"
or "oth")

 

[Bill Edwards]

One way, that skirts the issue of writing a query is to just create a report
Use the Report Wizard
Select the appropriate table
Select the fields you want on the report
Group by Race
Sort by Race as the first critiera, ApplicationDate as the second critieria
Open the report in design view and move the detail line into the Race Header
Section.
Still in design view reduce the detail line to nothing so it doesn't show.

Without knowing your table structure it's just about impossible to come up
with a query.

 

[Guest]

First create a query to find the oldest date for each race --
SELECT Inmates.race, Min(Inmates.date) AS MinOfdate
FROM Inmates
GROUP BY Inmates.race;

Then another query to pull records matching the date for the race --
SELECT Inmates.*
FROM Inmates INNER JOIN cornuts_1 ON (Inmates.race = cornuts_1.race) AND
(Inmates.date = cornuts_1.MinOfdate);

NOTE - You should not use field named DATE as that is a reserved word in
Access.
--
KARL DEWEY
Build a little - Test a little

I work for the Dept of Corrections where race is a classifying factor. I
guess my post should really not be titled SENIORITY, I was just trying to be
politically correct.
Basically my program is as follows:

We have inmates that we train to be firefighters, we have to keep our
training centers racially balanced and therefore only send out certain
quantities of inmates of certain races at a given time to each of our
training locations. However, we like to send them out on a first come first
serve basis therefore I would need to know who has been waiting the longest
by using their arrival date (thats where I came up with the seniority thing).

Some suggeted that I use the MIN/MAX feature but it was returning all my
table entries and just sorting them by date, what i need it to do is return
the earliest mexican, earliest black, earlies, white, and earliest other
inmate on the waiting list.

So the data in my query consists of name, date, race, location, etc. Its
almost like a staff roster, they are considered employee's

Thanks for all of your assistance.
Carla
aka
cornuts

Your definition of "First" and Access' definition probably don't match. If
you are working with a Date/Time field, consider using Maximum and Minimum
(depends on how you sort).

I don't have a clear picture of the underlying data you are using, and
queries start with data!

Have you looked into using the Totals query, grouping by your ethnicity
fields and using the Max (or Min) of the [some kind of date field]?

By the way, if you have named the field holding your date "Date", you will
only confuse yourself AND Access -- this is a reserved word in Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

In need of assistance generating a query that would return results based
on
ethnicity of the population of my company.

I need to (i dont know which is best) query/sort/filter/etc...) out the
first person in each designated race by the date they arrived here. I
want
the data that is returned to only provide the oldest person by date (not
age)
in that race/eth field.

I am trying to use the FIRST function under the Date field which I have
sorted ascendingly.

As it stands now I am trying the following:
Date
first
ascending ----and then----

under my RACE field i have my criteria set to (like "blk" or "whi" or
"mex"
or "oth")

 

[David W. Fenton]

Try Min instead of First. First in JET SQL means the first Record
found, not the earliest.

What you after is the earliest but this is translated to MIN for a
Date field ...

I would consider a set of TOP N queries, one for each racial
classification.

I still wonder if this is kosher -- I can see why it's considered
fair, but I'm not sure it would stand up to the kind of scrutiny the
Supreme Court has given affirmative action programs. This is a
strict racial quota system for giving out what could be considered
privileges, and quotas have always been completely rejected.

I know that's not the issue here, but it is the kind of thing that
occurs to me when I find myself in these situations.

 

[Guest]

I work for the Dept of Corrections where race is a qualifying factor in how
we distribute inmates out to locations in order to keep locations racially
balanced.
Hope I didnt offend.

Cornuts