Simple question driving me mad

[Kevin Smith]

Dear all I have 2 tables 'Deal Status' and 'Updates' the primary key
is codename. For each deal there are a number of updates linked by
codename.

I have this query for a report

SELECT [Deals Query].Code_Name, [Deals Query].[Start Date], [Deals
Query].Location1, [Deals Query].[Short Description], [Deals
Query].Sector1, [Deals Query].[Contact Name], [Deals Query].[Value
(Local Currency)], Updates.Update
FROM [Deals Query] LEFT JOIN Updates ON [Deals Query].Code_Name =
Updates.Code_Name;

What I want to do is only return the deal details with the latest
update, the query above returns one entry for each update.

Other entries in the updates table include an autonumber(i know thats
bad!) codename and a date of update, but this can be duplicated as
some updates are done more than once a day. i am sure this is simple
but I have a mental block :-D

Please feel free to reply to my mail address.

Many thanks for any help. Kevin

 

[Brian]

Dear all I have 2 tables 'Deal Status' and 'Updates' the primary key
is codename. For each deal there are a number of updates linked by
codename.

I have this query for a report

SELECT [Deals Query].Code_Name, [Deals Query].[Start Date], [Deals
Query].Location1, [Deals Query].[Short Description], [Deals
Query].Sector1, [Deals Query].[Contact Name], [Deals Query].[Value
(Local Currency)], Updates.Update
FROM [Deals Query] LEFT JOIN Updates ON [Deals Query].Code_Name =
Updates.Code_Name;

What I want to do is only return the deal details with the latest
update, the query above returns one entry for each update.

Other entries in the updates table include an autonumber(i know thats
bad!) codename and a date of update, but this can be duplicated as
some updates are done more than once a day. i am sure this is simple
but I have a mental block :-D

Please feel free to reply to my mail address.

Many thanks for any help. Kevin

Actually, it's not quite as simple as you might think. This needs a
correlated subquery, something like this (warning, untried!):

SELECT [Deals Query].Code_Name, [Deals Query].[Start Date], [Deals
Query].Location1, [Deals Query].[Short Description], [Deals
Query].Sector1, [Deals Query].[Contact Name], [Deals Query].[Value
(Local Currency)], Updates.Update
FROM [Deals Query] LEFT JOIN Updates ON [Deals Query].Code_Name =
Updates.Code_Name
WHERE (Updates.UpdateID IN (SELECT Max(U1.UpdatedID) FROM Updates U1
WHERE U1.Code_Name = [Deals Query].Code_Name))
OR Updates.UpdateID IS NULL;

Unfortunately, correlated subqueries like this tend to run slowly in Access.

Incidentally, just because some purists start foaming at the mouth when
autonumbers are mentioned, this doesn't mean that you have to agree with
them: study the arguments for and against, and make your own mind up!

 

[Guest]

I haven't tried this but it should work. Try adding an Order By clause
putting the Deals in desired order and Updates in descending order so the
most recent update comes to the top. Instead of a Select ..., replace it with
Select Top 1. Only the first record should show in the query.

 

[Kevin Smith]

Many thanks for all the responses, I found the answer in the end in creating
a sub query that did a GroupBy Max() function on the date of the update,
turns out it was a date/time stamp.

Although both of your responses look interesting and I am going to give them
a try just for fun.

Many thanks

Kevin