Updating records joined to grouped queries

[Enlin]

I've searched the site, and not found anything that really helps. I have a partial solution - I can return the records I want, but that then causes other problems.

To give you a bit of background, I'm maintaining a database that isn't my design, but I will soon be replacing it, so I don't want to spend too much time working on the existing one.

I have two tables, one for Customers, and one for Letters.

The only really relevant information in the customer table is the reference. The letters table contains a letter type, letter date, and customer reference.

I have a query that displays all records from the customer table, where a corresponding record exists in the letters table that's over a certain number of days old, and matches a certain letter type. This is used as the data source for a form, for users to update records (a specified number of days after a letter is sent, the account is ready to be looked at again).

The SQL is something like this. There's a lot of completely unrelated data in the customer table, so I've simplified my actual query. This is the only part I'm interested in.

SELECT Customers.Reference, Letters.LetterType, Letters.LetterDate
FROM Customers LEFT JOIN Letters ON Customers.Reference = Letters.Reference
WHERE (((Letters.LetterType)="Letter1") AND ((Letters.LetterDate)<Date()-13));

The query works, but it's very possible to have more than one matching record in the letters table - the same letter can be sent multiple times. I want to be able to base my query on the most recent letter of a particular type.

Instead of joining the customer table to the letters table, I made a further query to join the accounts table to, that only showed the most recent record.

SELECT Letters.reference, Letters.LetterType, Max(Letters.LetterDate) AS MaxLetterDate
FROM Letters
GROUP BY Letters.reference, Letters.LetterType;

This gives me the most recent letter for each reference and type. If I call this query LettersGroup, then my original query would be:

SELECT Customers.Reference, LettersGroup.LetterType, LettersGroup.LetterDate
FROM Customers LEFT JOIN LettersGroup ON Customers.Reference = LettersGroup.Reference
WHERE (((LettersGroup.LetterType)="Letter1") AND ((LettersGroup.LetterDate)<Date()-13));

This now displays exactly the records I want. All customer records where the most recent letter of a particular type was sent over a certain number of days ago.

My only problem now, is because I'm joining to a query that is grouped to limit the records returned, the original query is no longer updatable. Is there a way around this, or another way of only displaying the most recent letter record? I've played with a few things, but I'm much more familiar with VBA, and only really just getting to grips with SQL, so it's not been very productive.

Maybe there's a completely different approach that I'm not aware of. Any help would be greatly appreciated.

thanks
Ann

 

[PHV]

Why an outer join ?
Anyway, have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Enlin]

An outer join for no other reason than that's how it was before. It is possible to have records in the customer table, where no records exist in the letters table, but these wouldn't be displayed anyway so it's not necessary. Again, I've changed very little since the database was handed over to me - I'm still trying to work through everything that it does. I'm sure there's a better way to do a lot of the things that it does now. I'm still learning what some of those are.

Thanks for the link. Looking at the information, a subquery could be updatable. I think I'll need to do a bit more research, and ask again if I come across any problems I really can't resolve. Very frustrating not to be able to work it out by myself! I'll post the solution when I find one.

Thanks for the point in the right direction,
Ann

 

[Enlin]

I'm getting slightly frustrated at not being able to do what I want, but I do feel like I've learned some things in in trying.

What I want to do, is display the most recent date for each reference and letter type. For exmaple a table like this:

Reference LetterType LetterDate
---------------------------------
123456 Letter1 10/01/2005
789123 Letter1 01/11/2004
123456 Letter1 01/01/2001
123456 Letter2 08/09/2002
789123 Letter1 12/05/2003

Would show:

Reference LetterType LetterDate
---------------------------------
123456 Letter1 10/01/2005
789123 Letter1 01/11/2004
123456 Letter2 08/09/2002

Something like:

SELECT  Letters.*
FROM Letters
WHERE LetterDate = 
(SELECT Max(LetterDate)
FROM Letters)

That would show the most recent date overall, and still be editable, but I'm not sure how I'd work with multiple fields if I use a subquery.

Any suggestions would be very gratefully accepted. The more I can do myself rather than just copying someone elses SQL the better. I'm trying to learn as much as I can.

thanks

 

[PHV]

Something like this ?
SELECT A.Reference, A.LetterType, A.LetterDate
FROM Letters As A INNER JOIN (
SELECT Reference, Max(LetterDate) As LastDate FROM Letters GROUP BY Reference
) As L ON A.Reference = L.Reference AND A.LetterDate = L.LastDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Enlin]

Thanks again. It looks like it's in the right direction, but I get an error on that (syntax error in from clause). I can't work out what it should be.

Access97 by the way if that makes a difference (should have mentioned that in my first post).

 

[lespaul]

yes, it makes a difference. In Access 97 you will need to create two queries:

Query1:

SELECT Reference, Max(LetterDate) As LastDate FROM Letters GROUP BY Reference

Query2:

SELECT A.Reference, A.LetterType, A.LetterDate
FROM Letters As A INNER JOIN Query1 As L ON A.Reference = L.Reference AND A.LetterDate = L.LastDate




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Enlin]

Thanks Leslie. That's exactly what I did in the first place. The join on the query using groups and aggregates make the query so it's not updatable. I understand why grouping records does this, but can't find a different way to do it.

It might be that I have to live with it until the database is replaced, which hopefully won't be too long.