Stuck on Query with dates

[SteveS]

I hate queries with dates!!

I have a database to keep track of rank promotions in our Karate school. I am
trying to keep a history of the date of promotion.

My tables are:

tblNames
lngNameID (PK AN)
txtLName
txtFName

tblPromotions
lngPromotions (PK AN)
lngNamesID_FK
lngRankID_FK
dtePromotionDate
bytOrder
txtHomeDojo

tblRank
lngRanksID (PK AN)
txtRank


I can write a query to get the highest rank for each person, but when I add in
the date field, I get all promotion ranks and dates. I should have only a
persons name one time in the report with his highest rank and date or promotion.

So if John Smith's promotions are

lngRankID_FK dtePromotionDate txtRank
12 1/10/2006 (4th Kyu)
11 7/1/2006 (3rd Kyu)


and Jim Jones' promotions are:

12 5/16/2004 (4th Kyu)
11 12/03/2004 (3rd Kyu)
10 5/28/2005 (2nd Kyu)



the report should look like:

Date Name Rank Order Home Dojo
------------------------------------------------------
05/28/2005 Jones, Jim 2nd Kyu T
07/01/2006 Smith, John 3rd Kyu TN


The order field is to show who received their belt first if two or more people
were promoted on the same day. Seniority is everywhere...


Any help with the query is greatly appreciated.

 

[Sheila D]

It sounds like you need a totals query which groups on name and shows Max of
date. Can you post the SQL you have so far?

 

[Michel Walsh]

Hi,



Basically, it is related to getting the last borrower of each book in a
library. In your case, borrower == rankID, book == person. If you have a
table with fields:

Person; RankID; PromotionDate


then any of the four methods presented in
http://www.mvps.org/access/queries/qry0020.htm should do the job.

Once you have your query right, it is a matter to order by, in the report
based on that query, to order by RankID AND by PromotionDate (order by on
the two fields).



Hoping it may help,
Vanderghast, Access MVP

 

[SteveS]

I have a totals query "qryMaxRankDate", SQL is (formatted):

SELECT
tblPromotions.lngNamesID_FK,
Max(tblPromotions.lngRankID_FK) AS MaxOflngRankID_FK,
tblPromotions.bytOrder,
tblPromotions.txtHomeDojo

FROM tblPromotions

GROUP BY
tblPromotions.lngNamesID_FK,
tblPromotions.bytOrder,
tblPromotions.txtHomeDojo;


Then I have a query "Query1" with SQL:

SELECT
qryMaxRankDate.lngNamesID_FK,
tblNames.txtLName,
tblNames.txtFName,
tblRank.txtRank,
qryMaxRankDate.MaxOflngRankID_FK,
qryMaxRankDate.bytOrder,
qryMaxRankDate.txtHomeDojo

FROM tblRank INNER JOIN
(tblNames INNER JOIN qryMaxRankDate ON tblNames.lngNameID =
qryMaxRankDate.lngNamesID_FK)
ON tblRank.lngRanksID = qryMaxRankDate.MaxOflngRankID_FK

ORDER BY qryMaxRankDate.MaxOflngRankID_FK DESC;


This gives me the recordset I am looking for... the max rank for each person.

But when I try to add the date field, I get duplicates in the recordset.

In the report, the first sort is by lngRankID_FK (Asc). We have about 50 people
that are 3rd Kyu. the next sort order is by date (Asc). If two or more people
were promoted on the same date, the field bytOrder (Asc) is used.


Steve S.

 

[SteveS]

"The Access Web" is one of my most used bookmarks.

I tried those examples but I couldn't get it to work. If I got the query to
return a single record (max lngRankID_FK), when I added the date field, I had
multiple records per person.


Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Hi,



Basically, it is related to getting the last borrower of each book in a
library. In your case, borrower == rankID, book == person. If you have a
table with fields:

Person; RankID; PromotionDate


then any of the four methods presented in
http://www.mvps.org/access/queries/qry0020.htm should do the job.

Once you have your query right, it is a matter to order by, in the report
based on that query, to order by RankID AND by PromotionDate (order by on
the two fields).



Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

Try the following as your first query.
SELECT
P.lngNamesID_FK,
P.lngRankID_FK,
P.bytOrder,
P.txtHomeDojo,
P.dtePromotionDate
FROM tblPromotions as P
WHERE P.lngRankID_FK =
(SELECT Max(Pa.lngRankId_FK)
FROM tblPromotions as Pa
WHERE Pa.LngNameID_FK = P.LngNameID_FK)

You should be able to join that query to your other tables to get what you
want. Since your table and field names don't require square brackets [] ,
you could probably do this all in one query. IF that is a necessity, then
post back and I will try to help you construct an all in one query.

 

[SteveS]

Thanks John. I kept getting an error in the sub query where clause until I
realized I had defined [ngNameID_FK] with an "s" after Name. Once I fixed
that, the report worked.

I tried to mark this post answered, but my first post was with Thunderbird,
and now the web interface won't/doesn't show the "answered" button.

Thanks again (and also to Sheila and Michel) for your help.

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Try the following as your first query.
SELECT
P.lngNamesID_FK,
P.lngRankID_FK,
P.bytOrder,
P.txtHomeDojo,
P.dtePromotionDate
FROM tblPromotions as P
WHERE P.lngRankID_FK =
(SELECT Max(Pa.lngRankId_FK)
FROM tblPromotions as Pa
WHERE Pa.LngNameID_FK = P.LngNameID_FK)

You should be able to join that query to your other tables to get what you
want. Since your table and field names don't require square brackets [] ,
you could probably do this all in one query. IF that is a necessity, then
post back and I will try to help you construct an all in one query.