Query doesn't seen to MAX 1

[alphadude]

Table is tblReview
Fields are Pln#
PN
CND
Date Rcvd

SELECT tblReview.[Pln#], Last(tblReview.PN) AS LastOfPN, Max(tblReview.CND)AS MaxOfCND,Last(tblReview.[Date Rcvd]) AS [LastOfDate Rcvd]
FROM tblReview
GROUP BY tblReview.[Pln#]
HAVING (((Max(tblReview.CND))>”0”))
ORDER BY Max(tblReview.CND), Last(tblReview.[Date Rcvd]) DESC,

Below is actual result of QUERY
PlanLog
Plan # LastOfPN MaxOfCND LastOfDate Rcvd
04-0126 Gotoh 001 7/21/2004
03-0081 KFC/Long John Silvers 001 7/14/2003
02-0030 Kaufman Tire, Inc 001 3/4/2002
04-0049 First Baptist Mission 002 4/14/2004
03-0043 Stratford@Battlecreek 002 4/14/2003
02-0098 Waterworks Car Wash 002 5/21/2002
04-0129 Schencker, Inc 003 7/23/2004
02-0155 TS Office & W'house'A' 003 8/9/2002
02-0156 TS Office & W.house'B' 003 8/9/2002
02-0157 TS Office & W'house'C' 003 8/9/2002
03-0128 Sri-Siva Temple 004 11/3/2003
02-0151 Hindu Temple Add #1 004 8/7/2002
04-0130 RCS@SouthPark-Bldg U 005 7/26/2004
02-0128 FOFMBaptist Church 005 7/1/2002
02-0178 S'woods Dist Bldg 800 006 9/12/2002

What I am trying to get out of this query is the CND field as MAX and the Date Rcvd field as the LAST input for CND Example

PlanLog
Plan # LastOfPN MaxOfCND LastOfDate Rcvd
04-0126 Gotoh 001 7/21/2004
04-0049 First Baptist Mission 002 4/14/2004
04-0129 Schencker, Inc 003 7/23/2004
03-0128 Sri-Siva Temple 004 11/3/2003
04-0130 RCS@SouthPark-Bldg U 005 7/26/2004
02-0178 S'woods Dist Bldg 800 006 9/12/2002

I’ve moved, switched & replaced the MAX & LAST statements with no results, if you know an easy way to do this, please help. Thanks in advance.

 

[dhookom]

Last is generally not a good aggregate to use in a query. If I understand correctly, you can use something like:
[tt]SELECT A.*
FROM tblReview A
WHERE A.CND = (SELECT Max(CND) FROM tblReview B WHERE A.[Pln#] = B.[Pln#])[/tt]


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[alphadude]

Hi Duane,
Thanks alot for the help. I tried it and it's query is much
the same as mine. I made a couple of changes to see if I
could get it to pull out consecutive numbers for plan rack,
it no do. It did however, query less numbers (don't understand that).
I am ready to shoot my screen. If you can think of anymore tricks,
i'd sure like to see em. Again, thanks for helping.
Stan

 

[dhookom]

How about providing some actual sample records and then how you want to select/determine the resulting records?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[alphadude]

My lord that was fast, I'm not prepared. I'll go get a piece of the table and be right back. Thanks, Stan

 

[alphadude]

Duane,
The table consist of 81 fields, I couldn't cut it down as to see what's there. Anyway, the 4 fields in first message are all i'm pulling out. If you would like, I can zip the whole MDB and send you a copy so you could kinda get an idea what it is.
In short, it keeps a complete database of commercial plans(architect) that I review for Clayton County. They are tooo cheap to get a program, and this is the only way I can keep up with what I do.
Would it help if I give you a list of all fields, or a form, report, anything? Let me know and i'll try.
Thanks again, Stan

 

[dhookom]

I am only interested in the significant fields. If you have only four fields that relate to this question then provide those four fields and how they are used, some sample records, and how you want them to query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

try this, it may do what you are looking for:

SELECT tblReview.[Pln#], tblReview.PN, tblReview.CND, Max(tblReview.[Date Rcvd])
FROM tblReview
INNER JOIN (SELECT Distinct CND FROM tblReview) A ON tblReview.CND = tblReview.CND
GROUP BY tblReview.[Pln#], tblReview.PN, tblReview.CND

Leslie

 

[lespaul]

my bad, typed the join incorrectly, try this:

SELECT tblReview.[Pln#], tblReview.PN, tblReview.CND, Max(tblReview.[Date Rcvd])
FROM tblReview
INNER JOIN (SELECT Distinct CND FROM tblReview) A ON tblReview.CND = A.CND
GROUP BY tblReview.[Pln#], tblReview.PN, tblReview.CND

Leslie

 

[alphadude]

Hi Les, Thanks for the help. I run your query and it does
the same thing. (All CND's come up).

I am gonna put more info so you
can get an idea what I use it for.

Duane, I take a set of drawings from an architect and log them in. The Plan Number is Primary Key. There are 11 fields which get entered at that time. When I review them for building code compliance, I enter the Other 60 fields. After they are reviewed, I clamp them and assign a CND
Number (Clamp Number). Each week I run a ‘clamp report’. It tells the Inspectors where the drawings are if they need to look at them. Before I did this, I had to keep up with them manually. Below are the first log in Fields,


Field Names Represent

Pln#...................Plan Number
Date RCVD..........Date Received
PN.....................Project Name
PA.....................Project Address
PC.....................Project City
PZ.....................Project Zipcode
Type Const.........Type Construction
LL.....................Land Lot
Dist...................District
Zoning.................Land Zoned As
SqFt...................Square Feet Of Project
CND....................Clamp Number Drawer

After I review them, I fill out a ‘form’ which has all the fields listed in tblReview It is a form with 3 subforms. Then I run a report which prints out the cost of Permit and required data to meet building code.

As of now, I run the plan log report and delete all ‘old plans’. The CND numbers run from 0 to 60. I do a query which would print out every plan that has had a CND assigned. I start at 1 and delete the ‘original ‘ CND, of course since I pull them from my tblReview, each time I run query, I start over again.

Any help would be greatly appreciated.

 

[alphadude]

Was just thinking, I'm not great with these args. The
'CND' is a data and not number, would this effect Max?
Wonderin'
Stan

 

[lespaul]

maybe this?

SELECT tblReview.[Pln#], tblReview.PN, tblReview.CND, A.MaxDate FROM tblReview
INNER JOIN (SELECT CND, Max([Date Rcvd]) As MaxDate FROM tblReview GROUP BY CND) A ON tblReview.CND = A.CND AND tblReview.[Date Rcvd] = A.MaxDate

Leslie

 

[alphadude]

Les, I like the way the inner join works. The last one works but doesn't grab all the 60 CND's??

 

[lespaul]

what do you get if you just run:

SELECT CND, Max([Date Rcvd]) As MaxDate FROM tblReview GROUP BY CND

do you get all 60?



Leslie

 

[alphadude]

Les, I get 'em. It only shows 'CND' & Date, but I get 'em.
Thanks a million Les. gonna work with it and see if I can
attache the rest of data with it. Will let you know. Thanks
again!! Stan

 

[lespaul]

glad to be of some assistance! Just post back if you have other issues!

les

 

[alphadude]

Les, Got it!!! I grouped PN, PA & Pln# as first, works like a charm. Thanks a million. Gonna send a pinkie your way. Stan