list of last updated records

K

Kris

Hi,
I have two tables (contacts) and (actions) relation one to many.
I need to get a query where a selection is made of all last entered actions
records except when last (actions) field (ACTIONSTATUS) is "END"

And There must be a simple way (I thoudgt this was a main reason o try this
in Acees instead of Excel.

Can nyone help?
 
T

Tom Wickerath

Hi Kris,

There sure is an easy way to get the results you desire. Help us out a
little bit by providing the following information:

1.) The names of the fields that you want displayed in the query's recordset.

2.) The primary and foreign key field names (ie. the fields used to relate
the two tables).

3.) Some sample data would be helpful too, with an indication of what result
you expected based on the sample data provided.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
J

John W. Vinson

Hi,
I have two tables (contacts) and (actions) relation one to many.
I need to get a query where a selection is made of all last entered actions
records except when last (actions) field (ACTIONSTATUS) is "END"

And There must be a simple way (I thoudgt this was a main reason o try this
in Acees instead of Excel.

Can nyone help?
Click to expand...

Just be aware that unless you record the date/time or a sequential number when
the record was created or updated, you simply CANNOT do this. Access doesn't
keep track of "the last updated record" for you.

John W. Vinson [MVP]
 
K

Kris

I have two tables
table contacts with contact ID number and name adress phone data fields
table contacts with event ID number and specific fields about actions taken
of which one is next action date field

They are connected one to many by contact ID number

Of course I just want a query grouped per company what the next action date
is (excluding all contacts that have "end" in the status field)


Kris


I try to make a query with last event ID and grouped by company but this
doesn not work all the time.
 
J

John W. Vinson

I have two tables
table contacts with contact ID number and name adress phone data fields
table contacts with event ID number and specific fields about actions taken
of which one is next action date field

They are connected one to many by contact ID number

Of course I just want a query grouped per company what the next action date
is (excluding all contacts that have "end" in the status field)
Click to expand...

Use a Subquery: on the Criteria line under the next action date field put

=(SELECT Max([Next Action Date]) FROM actionstable AS X WHERE X.ContactID =
contacts.ContactID)

This will filter the actions to only the most recent action for that contact.

Just guessing at your fieldnames of course... adjust as needed.

John W. Vinson [MVP]
 
Top