queries - look at last record only

[Guest]

I have a database for a fundraising organisation. They want to know who they
can go to so if someone gave a donation in the last six months then they
don't want to go to him so i did a query for that but then if the guy gave 2
donations and his first one was given more than six months ago he still comes
up. So i have a tick box for most recent donation and each time a donation is
given they untick the last one and tick the new one as most recent and from
there i can make my query - but is there any more professional and simple way
of telling dbase look at the last record in teh donations subform?

 

[Nikos Yannacopoulos]

No tick boxes required, remove the field altogether from your table. All
you need is a query on the table which will pick up the most recent
record for each person in it. To do that, start making a new query, add
the table, drag the person and date fields to the grid, then go View >
Totals; notice the new line headed Total that appears in the grid,
defaulting to Group By for both fields; change this setting to Max under
the date field, and the job is done.

HTH,
Nikos

 

[John Spencer (MVP)]

If I understand you correctly, you need to identify those people who have NOT
donated in the last six months. You don't need to store the information in the
tables at all. You can use queries to get the information when needed.

First, identify those that HAVE given in the last six months.

SELECT DonorID
FROM DonationsTable
WHERE DonationDate > DateAdd("m",-6,Date())

Save that as queryA and use it in a frustrated outer join query as below

SELECT DonorID, DonorName
FROM DonorsTable as D LEFT JOIN QueryA as A
ON D.DonorID = A.DonorID
WHERE A.DonorID is Null

You can also do this

SELECT DonorID, DonorName
FROM DonorsTable as D
WHERE D.DonorID NOT IN
(SELECT DonorID
FROM DonationsTable
WHERE DonationDate > DateAdd("m",-6,Date()))

Plus some other variations.

 

[Guest]

Think i'm on right track but i don't understant. I make a new field in query
totals; and then where do i put group by and max under didn't get that part?
Thanks for your help!

 

[Nikos Yannacopoulos]

I make a new field in query totals;

No clue what you mean... make a new field where? Why?

and then where do i put group by and max under

You don't "put" them, you go View > Totals in the meny, and the Totals
line appears in the grid; then you leave it as is under the person
field, and change it to Max from the drop-down under the date field.

 

[Guest]

Thanks so much that did it!

No clue what you mean... make a new field where? Why?

You don't "put" them, you go View > Totals in the meny, and the Totals
line appears in the grid; then you leave it as is under the person
field, and change it to Max from the drop-down under the date field.

 

[Nikos Yannacopoulos]

Good!

Thanks so much that did it!

: