Searching for last gift...

[kpsony]

I work for a non-profit, and I'm trying to do a query on what everyones last gift amount given was... I cant seem to figure out a code. Is this even possible?

I am fairly new to access, but do know the basics.

Any help would be incredible.

Thanks,

Brian

 

[dhookom]

An answer would be much easier if you would take the time to provide some table and field names as well as about 5 sample records.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kpsony]

Name/address/gift1amount/gift1date/gift2amount/gift2date/
John/11th st/$100.00/06/26/04/$50.00/11/03/03
Sally/10th st/$25.00/04/03/04/ / /

soemthing along thsoe lines, not everyone has give the same amount of gifts...

 

[dhookom]

I wouldn't go any further with this table structure. Normalize the tables so that you don't have repeating groups of fields.

If you can't normalize your table structure then create a union query
SELECT [Name], Gift1Amount as Amount, 1 as GiftNum, Gift1Date as GiftDate
FROM tblNoName
UNION ALL
SELECT [Name], Gift2Amount, 2, Gift2Date
FROM tblNoName
WHERE Gift2Amount is not Null
UNION ALL
SELECT [Name], Gift3Amount, 3, Gift3Date
FROM tblNoName
WHERE Gift3Amount is not Null;

You can then create a query that finds the maximum GiftDate for a [Name] (not a good Name for a field). Use the totals query joined to the union query to find all the information about the most recent gift.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kpsony]

Here are the exact field names
First Name/Last Name/Primary Address1/City/State/Zip/Gift 1 Amount/Gift 1 Date/Gift 2 Amount/Gift 2 Date/ all the way up to 8.

I appreciate all the help dhookom, but I'm not that good. Another thing is, all of our gifts are not being recorded into our main table, so when I run a query, its not pulling up all the gifts, but when I go through the form and look up someones name, its showing all their gifts...

If anyone is in the LA area, I'd love to hire you to come in for a couple hours and show us what the heck is going on.

 

[dhookom]

How can you expect someone to explain how to pull up the most recent gift amount when "gifts are not being recorded into our main table".

Your table structure is very limiting. Access is a relational database, not a spreadsheet. Did you try create the union query that I suggested? You would need to add the fields beyond the 3rd set of columns.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kpsony]

Let me explain a little better.

We have a database that was donated to us, and have 3500+ records. When I go to the main table where all the info is saved, its not showing everyones gifts, and the peoples gifts it does show, it is not showing all of their gifts, but when I go to their record in the form, it has all their gifts listed.

I have no idea what a 'union query' is... that's why if anyone has time, I'd love to hire you to come check out our messed up database.

 

[lespaul]

Open a new query, switch to SQL design view, copy this query:

SELECT [First Name] As FirstName, [Last Name] As LastName, Primary Address1, City, State, Zip, [Gift 1 Amount] As GiftAmount, [Gift 1 Date] As GiftDate FROM tblName
UNION
SELECT [First Name], [Last Name], Primary Address1, City, State, Zip, [Gift 2 Amount], [Gift 2 Date] FROM tblName
UNION
SELECT [First Name], [Last Name], Primary Address1, City, State, Zip, [Gift 3 Amount], [Gift 3 Date] FROM tblName
UNION
SELECT [First Name], [Last Name], Primary Address1, City, State, Zip, [Gift 4 Amount], [Gift 4 Date] FROM tblName
UNION
SELECT [First Name], [Last Name], Primary Address1, City, State, Zip, [Gift 5 Amount], [Gift 5 Date] FROM tblName
UNION
SELECT [First Name], [Last Name], Primary Address1, City, State, Zip, [Gift 6 Amount], [Gift 6 Date] FROM tblName
UNION
SELECT [First Name], [Last Name], Primary Address1, City, State, Zip, [Gift 7 Amount], [Gift 7 Date] FROM tblName
UNION
SELECT [First Name], [Last Name], Primary Address1, City, State, Zip, [Gift 8 Amount], [Gift 8 Date] FROM tblName

Edit this query to have the correct tblName in the FROM clause.

Save this query as qryNormalizedGifts. Now your data is structured in a more normalized way, you can use a very simple query to get the information you need:

SELECT FirstName, LastName, Primary Address1, City, State, Zip, GiftAmount, Max(GiftDate) FROM qryNormalizedGifts Group By First Name, Last Name, Primary Address1, City, State, Zip, GiftAmount,

Leslie

 

[dhookom]

Leslie is correct on the SQL of the UNION query. The final query will not return only one record per person unless the GiftAmount is exactly the same for all gifts.

My earlier post suggests creating a query that returns the most recent giftdate and then combining this query with the union query to find the amount of the most recent gift.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lespaul]

I thought I was missing something, you're right, need to join into a query that has the latest date only in it.



Leslie

 

[kpsony]

I tried that formula, but it was pulling up everyones gift. I think its pointless if the Main Table and the form aren't communicating correclty, because how can it pull info from a table that doesnt have it?

 

[kpsony]

Actually, it worked fine, and error on my side

 

[kpsony]

But what were you saying about it not pulling up the most recent gift?

 

[dhookom]

The union query that Leslie and I have suggested will retrieve all gift records. You asked for just the most recent gift record.

Are you seeing only the most recent gift with the date and amount or are you seeing all gift records from an individual? When you state "it worked fine" does this mean you have your final solution?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kpsony]

It's pulling up multiple records for each person, and its pulling up their zip codes in one record, then on the next record, their state, in the same field.

Example:
FirstName/LastName/PRIMARY ADDRESS 1/Expr1003/PRIMARY STATE/PRIMARY ZIP/GiftAmount/GiftDate

John/####/92103/CA/92103/$500.00/12/1/2001
John/####/San Diego/CA/92103/$10,000.00/12/8/2002

Here the code im using:

SELECT [First Name] As FirstName, [Last Name] As LastName, [PRIMARY ADDRESS 1],  [PRIMARY ZIP],  [PRIMARY STATE],  [PRIMARY ZIP], [Gift 1 Amount] As GiftAmount, [Gift 1 Date] As GiftDate FROM [The Trevor Project Database]
UNION
SELECT [First Name], [Last Name], [PRIMARY ADDRESS 1], [PRIMARY CITY], [PRIMARY STATE], [PRIMARY ZIP], [Gift 2 Amount], [Gift 2 Date] FROM[The Trevor Project Database]
UNION
SELECT [First Name], [Last Name], [PRIMARY ADDRESS 1], [PRIMARY CITY], [PRIMARY STATE],  [PRIMARY ZIP], [Gift 3 Amount], [Gift 3 Date] FROM [The Trevor Project Database]
UNION
SELECT [First Name], [Last Name], [PRIMARY ADDRESS 1], [PRIMARY CITY], [PRIMARY STATE],  [PRIMARY ZIP], [Gift 4 Amount], [Gift 4 Date] FROM [The Trevor Project Database]
UNION
SELECT [First Name], [Last Name], [PRIMARY ADDRESS 1], [PRIMARY CITY], [PRIMARY STATE],  [PRIMARY ZIP], [Gift 5 Amount], [Gift 5 Date] FROM [The Trevor Project Database]
UNION
SELECT [First Name], [Last Name], [PRIMARY ADDRESS 1], [PRIMARY CITY], [PRIMARY STATE],  [PRIMARY ZIP], [Gift 6 Amount], [Gift 6 Date] FROM [The Trevor Project Database]
UNION
SELECT [First Name], [Last Name],[PRIMARY ADDRESS 1], [PRIMARY CITY], [PRIMARY STATE],  [PRIMARY ZIP], [Gift 7 Amount], [Gift 7 Date] FROM [The Trevor Project Database]
UNION SELECT [First Name], [Last Name], [PRIMARY ADDRESS 1], [PRIMARY CITY], [PRIMARY STATE],  [PRIMARY ZIP], [Gift 8 Amount], [Gift 8 Date] FROM [The Trevor Project Database];

 

[kpsony]

LOL, im an idiot, nevermind

 

[kpsony]

but it is still pulling out all their gifts, not just the most recent...

 

[dhookom]

Create another query based on the Union query:
SELECT [First Name], [Last Name], Max([GiftDate]) as MaxDate
FROM quniYourQuery
GROUP BY [First Name], [Last Name];
Then combine this query with the union query and join the two name fields and the date fields so that your final query only returns one record per first and last name.

BTW: this breaks if you have two people with the same first and last name or if you have two gifts from the same person on the same date.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kpsony]

How do I create a new query based on a union query?

And how do I combine?

 

[dhookom]

When you create a new query, you are provided the opportunity to select one or more tables or queries as the source for your new query. If you can't figure this out, provide us with the exact SQL of your union query as well as its name.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.