Returning multiple records Isn't what I need

[frobro390]

Hi everyone,

I am very new to Access and could certainly use a class on some of the more complex stuff, like SQL... I've got a database that I am using to track clients who come and go from the facility. I've got two tables, a Client_Info table and a Client_Log table. Client info keeps track of all current residents, the log table is where all the time entries are kept.

I want to run a query that will allow me to find out everyone who is currently logged out, and where they are logged out to. The Client_Info table is as follows:

Client_ID | Prefix | First_Name | Middle_Initial | Last_Initial | On_Campus | Current_Resident

Client_Log:

Log_ID | Client_ID | Destination | Purpose | Event_Time | Event_Date

I put a query together that checks the On_Campus field, and Current_Resident fields and combines data from both tables, and I am almost getting the results I need. Unfortunately I am getting each and every event for each person If they are currently off campus. I only want the most current event in the client log.

Does anyone have any possible solutions?

Thank you in advance, and please forgive my lack of etiquette, this is my first post.



Anthony

[weekend00]

you described you situation very clearly.

you need a sub query to find out the most current event for each person, e.g. select client_id, max(datetime) from client_log group by client_id.

then join to client_info to get other info.

[frobro390]

Thank you Weekend00, so I think I understand what you are saying. I unfortunately don't understand much SQL, but obviously if this is something I want to pursue I should start to study up. I'll post my results.

[frobro390]

Talk about feeling stupid... So here is the query as is, where would I apply said sub-query, and... how?


SELECT Client_Information.Client_ID, Client_Information.First_Name, Client_Information.Last_Name, Client_Log.Destination, Client_Log.Purpose, Client_Log.Event_Time, Client_Log.Event_Date
FROM Client_Information INNER JOIN Client_Log ON Client_Information.Client_ID = Client_Log.Client_ID
WHERE (((Client_Information.On_Campus)=False) AND ((Client_Information.Current_Resident)=True));

[NTC]

Have done plenty like this but some info in your post I find confusing. Fundamentally the info is found in the log table. (in most designs) One makes the query on the log table. But because the human doesn't know who is client ID 323 (I made that up)...you then join to the client table to provide the human name. Traditionally the client table serves no other role than to provide the human name to the record set that the log table query made.

But in your case you say you are making a query on fields in the client table - and so that is confusing.

[weekend00]

create a query named "lastestEvent":

SELECT client_log.client_id, Max([event_date]+[event_time]) AS eventDT
FROM client_log
GROUP BY client_log.client_id;

create second query to get result:

SELECT Client_infomation.*, client_log.*
FROM (latestEvent INNER JOIN client_log ON (format(latestEvent.eventDT,"hhmmss")=format(clien t_log.event_time,"hhmmss")) AND (latestEvent.client_id=client_log.client_id) AND (int(latestEvent.eventDT)=client_log.event_date)) INNER JOIN Client_infomation ON client_log.client_id=Client_infomation.client_id
WHERE (((Client_infomation.on_campus)=False) AND ((Client_infomation.current_resident)=True));

It looks something complicated. can may it easier by adding more sub query, but since it works, it's fine.

[frobro390]

Worked like a charm, thank you very much for your time and effort. Certainly saved my bacon