Hi,

I have an Access table to track the textbooks used in courses, the table is
designed to keep track of all the textbooks ever used for a course.

Table structure:

item_number : access auto-number
course_code: automatically fills in when you submit the textbook
enter_date: auto date field in access
enter_name: the persons name making the entry
textbook_entry: textbook info

I can easily create a report that pulls up all the info, what I want to do
is only display the newest entry for each of the 144 courses, so in theory,
the report should only have 144 records, not the full 400+ records that are
in the table.

I can't figure out the sql needed.

I am using an ASP page and Access 2000 db.

Any help would be greatly appreciated.

Thanks!

Mitch

from the head I think you want something resembling:

SELECT TOP 144 *
FROM Textbooks
ORDER BY enter_date DESC

What this does is it selects the 144 latest entries, but this query does not care if it returns 2 textbooks that are the same, so if you have one or more books that are not updated for a year or more and another has monthly updates then you might lose the one that isn't updated often and you also get double results for the frequently updated textbook.

So there's nothing we can do but get hairy with SQL and use a sub-select...

SELECT TOP 144 *
FROM Textbooks AS Textbooks1
WHERE NOT EXISTS (SELECT * FROM Textbooks WHERE enter_name NOT LIKE Textbooks1.enter_name AND item_number > Textbooks1.item_number)
ORDER BY enter_date DESC

or something like that, atleast.

with regards,

Dennis van Galen
DMXzone Manager

Extension, Tutorial and FAQ's Manager

Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5

oh duh, while reading my first statement it came to me...

SELECT DISTINCT *
FROM Textbooks
ORDER BY enter_date DESC

with regards,

Dennis van Galen
DMXzone Manager

Extension, Tutorial and FAQ's Manager

Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5

Hi djvgalen,

The first idea return only 1 record.

The second idea does not work, I don't think you can do a DISTINCT with a *


Thanks!

Mitch

the problem is probably that distinct will not work with memo fields, but no worry, try this

SELECT DISTINCT item_number, course_code, enter_date, enter_name
FROM textbooks
ORDER BY enter_date DESC

that should work fine

with regards,

Dennis van Galen
DMXzone Manager

Extension, Tutorial and FAQ's Manager

Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5

OK, here's an update.

This seems to work, except I cannot add the field textbook_entry to the RS.

When I do, it comes up with an Aggregate function error saying that the field textbook_entry is not part of it.


SELECT course_code, MAX(enter_date) AS enterdate
FROM textbook_history
GROUP BY course_code
ORDER BY course_code ASC

Any help would be greatly appreciated.

Thanks,

Mitch

djvgalen,

I tried you latest selection, but it returns ALL of the records and not the 144 records it should.

I'm sure we will get this. <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Thanks!

Mitch

haha, i think i got you know. let's stick with the course_code

let's distinct select them

SELECT DISTINCT course_code
FROM textbook_history

that should return 144 results, right ?

ok, now add order by enter_date descending and it should do what we're trying to do.

SELECT DISTINCT course_code
FROM textbook_history
ORDER BY enter_date DESC

with regards,

Dennis van Galen
DMXzone Manager

Extension, Tutorial and FAQ's Manager

Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5

Edited by - djvgalen on 14 Jan 2003 21:45:34

Sorry,

When I try it I get this error:

ORDER BY clause (enter_date) conflicts with DISTINCT


This seems to work best...ALMOST:


This seems to work, except I cannot add the field textbook_entry to the RS.

When I do, it comes up with an Aggregate function error saying that the field textbook_entry is not part of it.


SELECT course_code, MAX(enter_date) AS enterdate
FROM textbook_history
GROUP BY course_code
ORDER BY course_code ASC

i think i just remembered why in my faq about distinct i recommended group by instead of order by, can you try this and let me know what it does ?

SELECT DISTINCT course_code
FROM textbook_history
GROUP BY enter_date DESC

the problem with your textbook_entry field is that it is probably a memo field containing entire word documents, am i right ?
you cannot aggregate those, you cannot order by those either, so if the above does not work then it's back to something like:

SELECT TOP 144 *
FROM Textbook_history AS Textbook_history1
WHERE course_code = (SELECT * FROM Textbook_history WHERE course_code NOT LIKE Textbook_history1.course_code)
ORDER BY enter_date DESC

i am not sure if the brackets ( and ) are needed, if it errors then try removing them, i still use access 97 more than 2000.
But if this does work then you have the textbook_entry as well. I'll assume you only store short descriptions there and not 100s of pages...

btw, you can leave the top 144 out of the subselect query, there are only 144 courses, right ?

EDIT: better where clause, try it now.

Edited by - djvgalen on 14 Jan 2003 22:18:28

you can also try something along the lines of:

SELECT TOP 144 *
FROM textbook_history
WHERE (((textbook_history.item_number) Not Like "DISTINCT")
ORDER BY textbook_history.enter_date DESC

and this certainly works, because i just tried this on a memo field table and ordered on date, but there has to be some unique value to work with or it will fail.

what does the MAX() function do anyway ? <img src=../images/dmxzone/forum/icon_smile_blush.gif border=0 align=middle>

Dennis

Hi djvgalen,

Regretfully none of those samples worked, they either came up with an error, or showed all of the records.

What did work was using a Nested Repeat Region

www.dmxzone.com/ShowDetail.asp?NewsId=394

Thanks for all of the help, perhaps I can help you one day.

Mitch

PS: MAX

MAX
A SQL keyword. MAX is one of the set functions. It is used to get the value in a Column which is greater than or equal to all other values.

When in doubt...reboot!

Hi Mitch,

I'm glad you got it to work, but frankly, if you cannot use distinct with order by date and with memo fields then what use is MS-Access ?

I'm going to search alittle and see how others achieve something like this in MS-Access, also because I wanted to write a tutorial about showing highest score of each training session with the learningsite command and for that you simply NEED distinct order by date.

I will let you know when i figure this out, because you are now wasting CPU cycles on your webserver because you use two queries and one query filters the other, which is costly in CPU terms.

with regards,

Dennis van Galen
DMXzone Manager

Extension, Tutorial and FAQ's Manager

Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5

djvgalen,

Yeah it's weird.

It's a shame that I could get it to almost work with:

SELECT course_code, MAX(enter_date) AS enterdate
FROM textbook_history
GROUP BY course_code
ORDER BY course_code ASC

But then when I add "textbook_entry" to the select statement it gives the aggregate error, I guess.

Like you said, it's just a simple MEMO field with no more that 50 - 100 words.

It'll be interesting if you find some additional info.

Thanks!

Mitch

PS: this report will not be used a lot, so hopefull not to many CPU cycles are wasted. <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Mitch,

Here's what I found out so far:

DISTINCT cannot order by a un-selected field, so in order to get order by datefield to work it HAS to be in the select statement, this is true in Access 97, 2000 and SQL Server 2000 and maybe others too, but i only tried those 3. This is because the sql engine cannot determine what unselected date is related to the selected fields, so it fails.

With memo fields you can do very little manipulations since they usually turn out to be unique in their own way because their values are unique and different from eachother.

This query:

SELECT course_code, MAX(enter_date) AS enterdate
FROM textbook_history
GROUP BY course_code
ORDER BY course_code ASC

You wrote:
"MAX
A SQL keyword. MAX is one of the set functions. It is used to get the value in a Column which is greater than or equal to all other values."

according to this article it returns 1 record, the highest date only:
msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ma-mz_3h6g.asp

Your query, as stated above, errors complaining that enter_date is not in the GROUP BY clause. However, if i remove the group by, order by and course_code from select then it returns 1 record, the highest date in the table, just like the MSDN article claims, so this isn't the way to go, unless each update took place on the same date, then you can use MAX() and probably in this fashion:

SELECT course_code, textbook_entry
FROM textbook_history
WHERE (enter_date =
(SELECT MAX(enter_date)
FROM Textbook_history))
ORDER BY course_code

This works, BUT only if all modification dates are identical, but you said using MAX comes closest, if you do this then you can select the textbook_entry memo field because the second query is not counted in the select, so the cannot use aggregate error is gone now.

Phew, does that make sense ?
You know, i followed a basic Access course, and 3 SQL Server courses (XML, Administering and Programming) but nobody ever mentioned once that DISTINCT does not work with unselected columns and/or memo fields.

Let me know what this does in your table, if it returns 144 records that are unique courses then please DO let me know how you update your textbooks.

with regards,

Dennis van Galen
DMXzone Manager

Extension, Tutorial and FAQ's Manager

Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5

Hi djvgalen,

Sorry, but the latest attemp only returned one record. Ah!

I'll see if I can tweak your code and get it to work.

Thanks for the continuing help!

Mitch

djvgalen,

I think the problem lies in this part of the code:

(SELECT MAX(enter_date) FROM Textbook_history))

We are telling it to only select the record that has the MAX date, which would be the most recent.

Therefore, the

ORDER BY course_code

has nothing to ORDER except for one record.

Thanks!

Mitch


When in doubt...reboot!

Mitch,

I'm all out of ideas, everything I tried fails or returns inproper recordsets, so I'm relaying the article that the MVP who told me about DISTINCT recommends:
www.mvps.org/access/queries/qry0020.htm
There's nothing new in it, except use join.

Actually, it is written by the MVP who tried to help me, Most Valuable Professionals ? This is exactly why I think M$ Certification is a waste of my money !

This just shows how limited M$ databases are ! And not just Access.
In progress you can just select unique id and be done with it, it's impossible in a single table access database, i guess.
If I do find a solution I will post it.

Sorry that I can't offer you a better solution at this time <img src=../images/dmxzone/forum/icon_smile_sad.gif border=0 align=middle>

Dennis

Dennis,

Thanks!

You've done more than the average person would have done.

I got an e-mail from a Macromedia person who was also trying to help, he asked for the database so he could see what was going on. I will update this post with the results.

Thanks!

Mitch

Mitch, did you solve this ?

SELECT *
FROM textbook_history
WHERE (enter_date =
(SELECT MAX(enter_date)
FROM textbook_history AS tb_h1
WHERE tb_h1.course_code = textbook_history.course_code))

that is what I used today in a coldfusion function to display the last articles updated by our employees. Mind you, i need to think of something to alert my users or make an auto-archive function. Works great, it's the same table that i use verity on to find stuff again. The sql syntax above comes straight out of sql and works with text / ntext which is sql for memo and it brings up the same error with distinct as the memo field in access when i export the table using Data Transformation.

Hope this works for you as well.

with regards,

Dennis van Galen
DMXzone Manager

Extension, Tutorial and FAQ's Manager

Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5