RE: Help retrieving last letter sent 1

[allyne]

Hi Everyone,

It's been a long time since I've used Access. I think this may be ease but just can't seem to get it. I'm using Access 2000.

I have a table with the following fields.

RecNbr PrimaryKey,
LastName Text,
FirstName text,
FirstLetterSentDate datetime,
SecondLetterSentDate datetime,
ThirdLetterSentDate datetime,
FourthLetterSentDate datetime

I need to create a query that shows the last letter sent for each person. So I need something that looks like this.

RecNbr,LastName, FirstName,
LastLetterSent=If FourthLetterSentDate is not null then FourthLetterSentDate
If FourthLetterSentDate is null then ThirdLetterSentDate
If FourthLetterSentDate is null and ThirdLetterSentDate is null then SecondLetterSentDate
If FourthLetterSentDate is null and ThirdLetterSentDate is null and SecondLetterSentDate is null then FirstLetterSentDate
End

Is there away to do this in the query design view? If so how?

Thanks for your help!

 

[lespaul]

you'll need to switch to SQL view and create a normalizing query:

typed not tested (the null syntax is probably incorrect)

SELECT RecNbr, LastName, FirstName, FirstLetterSentDate As LetterSentDate, "First" As WhichLetter From TableName WHERE FirstLetterSentDate is not null
UNION
SELECT RecNbr, LastName, FirstName, SecondLetterSentDate, "Second" From TableName WHERE SecondLetterSentDate is not null
UNION
SELECT RecNbr, LastName, FirstName, ThirdLetterSentDate, "Third" From TableName WHERE ThirdLetterSentDate is not null
UNION
SELECT RecNbr, LastName, FirstName, FourthLetterSentDate, "Fourth" From TableName WHERE FourthLetterSentDate is not null

save that as qryNormal

and then create a second query:

SELECT RecNbr, LastName, FirstName, WhichLetter, Max(LetterSentDate) FROM qryNormal
GROUP BY RecNbr, LastName, FirstName, WhichLetter

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[fneily]

Yeah, your table is not normalized. A better table would be
RecNbr FisrtName LastName DateSent Attempt#

Notice in your table, you'll have alot of null cells. Not acceptable in relational database theory. Also, what will happen when you want to try a fifth attempt? Add another column? Wrong. This goes against the first normal form that you can't have variable length records.

If you correct your table, then your request is easy to answer. Just check the max attempt number of each customer.

Maybe this will help:
Fundamentals of Relational Database Design

http://r937.com/relational.html

 

[allyne]

Hi Leslie,

Thanks for much for your help! Works Perfect!

 

[lespaul]

thanks for the star!

Fneily is right though (I just didn't explain it this time)...the issue stems from the table structure. If the table was set up the way the normalizing query returns the data, it would be a correct table structure. Then the query is fairly easy.

Leslie