How to query this?

[Song Su]

I want a uniq MajorCode with only latest AcadYear with MajorDescription.

Thanks.

AcadYear MajorCode MajorDescription
20082009 000300 Undecided
20092010 000300 Undecided
20072008 012080 Medical Assistant
20082009 012080 Medical Assistant
20092010 012080 Medical Assistant
20002001 020100 Architectural Computer Aided Design/Drafting
20012002 020100 Architectural Computer Aided Design/Drafting
20032004 020100 Architectural Computer-Aided Design- AutoCAD
20042005 020100 Architectural Computer-Aided Design- AutoCAD

 

[MGFoster]

I want a uniq MajorCode with only latest AcadYear with MajorDescription.

Thanks.

AcadYear MajorCode MajorDescription
20082009 000300 Undecided
20092010 000300 Undecided
20072008 012080 Medical Assistant
20082009 012080 Medical Assistant
20092010 012080 Medical Assistant
20002001 020100 Architectural Computer Aided Design/Drafting
20012002 020100 Architectural Computer Aided Design/Drafting
20032004 020100 Architectural Computer-Aided Design- AutoCAD
20042005 020100 Architectural Computer-Aided Design- AutoCAD

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could probably do something like this:

SELECT MAX(AcadYear) As LatestYear, MajorCode, MajorDescription
FROM table_name
GROUP BY MajorCode, MajorDescription

If MajorDescription is a Memo data type only the first 255 characters
will be sorted/grouped by the GROUP BY clause.

Or this (avoids the MajorDescription GROUP BY problem):

SELECT AcadYear, MajorCode, MajorDescription
FROM table_name As T1
WHERE AcadYear = (SELECT MAX(AcadYear) FROM table_name
WHERE MajorCode=T1.MajorCode
AND StudentID = T1.StudentID)

You really need a Student ID (or some other unique ID) for the 2nd
query; otherwise the results may be muddled.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBScb7xoechKqOuFEgEQIOqgCg0Kw5uKaTe5KlnQgWzO3avn+12n4An2Cj
Q5De8HZCZTE8UnqqTGjvj2E2
=sApN
-----END PGP SIGNATURE-----

 

[Song Su]

Thanks for the solution. The table does not have student ID.
MajorDescription is a text field. It might change as academic year change,
like the last 2 records (drafting changed to autocad). I do not want any
obsolete major. For MajorCode, example, I only want 020100 for the latest
AcadYear (20042005). Any solution? Thanks

 

[raskew via AccessMonster.com]

Hi -

Slight modification of the previous post seems to work. Just change the
table name as appropriate:

SELECT
AcadYear
, MajorCode
, MajorDescription
FROM
tblTest4 As T1
WHERE
AcadYear = (
SELECT
MAX(AcadYear)
FROM
tblTest4
WHERE
MajorCode=T1.MajorCode)

HTH - Bob

Thanks for the solution. The table does not have student ID.
MajorDescription is a text field. It might change as academic year change,
like the last 2 records (drafting changed to autocad). I do not want any
obsolete major. For MajorCode, example, I only want 020100 for the latest
AcadYear (20042005). Any solution? Thanks
[quoted text clipped - 33 lines]

You really need a Student ID (or some other unique ID) for the 2nd
query; otherwise the results may be muddled.

 

[John Spencer]

The following will work as long as Major Description stays constant for
each Major Code
SELECT Max(AcadYear) as TheYear
, MajorCode
, MajorDescription
FROM SomeTable
GROUP BY MajorCode, MajorDescription

You can build that in the design view by
-- Add your table
-- Add the three fields
-- SELECT View: Totals from the menu
-- Change GROUP BY under AcadYear to Max
-- Run the query
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I want a uniq MajorCode with only latest AcadYear with MajorDescription.

Thanks.

AcadYear MajorCode MajorDescription
20082009 000300 Undecided
20092010 000300 Undecided
20072008 012080 Medical Assistant
20082009 012080 Medical Assistant
20092010 012080 Medical Assistant
20002001 020100 Architectural Computer Aided
Design/Drafting
20012002 020100 Architectural Computer Aided Design/Drafting
20032004 020100 Architectural Computer-Aided Design- AutoCAD
20042005 020100 Architectural Computer-Aided Design- AutoCAD

 

[raskew via AccessMonster.com]

The following will work as long as Major Description stays constant for
each Major Code

It didn't. See original post.

Bob

 

[John Spencer]

Yes, I noted that after I posted. However, some other valid solutions
were posted, so I did not correct my post.

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