retrieving latest data.

hi. i need to retrieve e latest data from a table.. but am nt sure how to do it...
here's how it goes...(by e way i'm using ms access) i have a table with 10 schools.. in every school, there's 8 stalls . and in every stall, i have 1 current owner n a few previous owners.

k.. n how do i retrieve e current owners of every stall when i click on e school? tat means when i click e school, each stall will only show e current or latest owner to date. thanks...

Is all this information in one table or multiple tables? What is you general DB Structure

S-

it's all in one table... i dun get what u mean by general db structure.. or mayb i should put my screen shot up? by e way i'm using ms access

You should be able to create a basic select statement that should bring back the information that you need. Just include the school id in the Where part of the statment

"SELECT * from SCHOOLTABLE WHere SCHOOLTABLE.SCHOOLID = 'SCHOOL1';"

S-

Your screen shot would help me give you a more accurate statement

erm... wad i mean was to return table with e latest owner of all the stalls of tat particular shcool.. tat means should have stall1 - latest owner, stall 2 latest owner... and soo on.. sorry

Better include your screen shot

s-

erm.. think i can't include it.. cos i dun have a server or account to store my pics.. anyway.... i shall post it this way..

GIVEN TABLE:

<u>Name</u>|| <u> stallnumber</u> ||<u>datejoined</u> || school

abc || stall 1 || 23/06/1984 || nss

cde || stall 1 || 23/06/1999 || nss

xyz || stall 2 || 23/09/1999 || nss

nick || stall 2 || 23/06/2003 || nss



but this is what i wana retrieve from this table.

<u>Name</u> &nbsp<u> stallnumber</u> &nbsp<u>datejoined</u>&nbsp school

cde &nbsp stall 1 &nbsp 23/06/1999 &nbsp nss

nick &nbsp stall 2 &nbsp 23/06/2003 &nbsp nss




*note.. only e latest owner with reference from e datejoined from the same stall is retrieved.

Have fun with this

If you need a copy of the DB I created this is let me know

SELECT [Shawnleong].[Name], [Shawnleong].[StallNumber], [Shawnleong].[DateJoined], [Shawnleong].[School]
FROM
Shawnleong
INNER JOIN
[SELECT Shawnleong.StallNumber, Shawnleong.School, Max(Shawnleong.DateJoined) AS DateJoined1
FROM Shawnleong
GROUP BY Shawnleong.StallNumber, Shawnleong.School
HAVING (((Shawnleong.School)='nss'))]. AS PLAY_GROUP
ON ([PLAY_GROUP].[School]=[Shawnleong].[School]) AND ([PLAY_GROUP].[DateJoined1]=[Shawnleong].[DateJoined]) AND ([PLAY_GROUP].[StallNumber]=[Shawnleong].[StallNumber]);

S-

hmm... kinda blur down here,,,... or mayb i'll send u my table? i'm nt really sure down her.e.

Here is a database set up with a table as you stated
__________________________________________

Name|| stallnumber ||datejoined || school

abc || stall 1 || 23/06/1984 || nss

cde || stall 1 || 23/06/1999 || nss

xyz || stall 2 || 23/09/1999 || nss

nick || stall 2 || 23/06/2003 || nss

___________________________________________

The SQL Statement works, it is saved in query1 (you need to use SQL view)

Basically itis a two part query in one statement; you have a query that is grouped to give you the max Joindate of each school/stall combination. Then you join this query back to the orginal table to get the latest name of the person in charge of each stall. All you need to do is change the part the references which school.

S-

sorry but wad's play_group?

Play-Group is an Alias table (Some think of it as a virtual table). I name the nested select statement Play_group so it can be reference in other parts of the Statement(noticed the "AS PLAY_GROUP" at the end of the nested select statement).

S-

erm.. i need to add some more headers... how do i do so using sql? n how do i retrieve e header n line them up... as in...

name value1
stallnumber value2
datejoined value3
school value4