Gift History- from 2 Tables; creating 1 record per Customer#

[Guest]

I have 2 tables- 1 that contains a customer # and name/address info; another
that contains the customer# and gift history (amount and date). I need to
create 1 table that contains all info from table 1 as well as info from table
2. How do I populate the gift history horizontally so I only have 1 record
for each customer#?

I can create a table that contains duplicate customer #'s with gift history-
but I need for only 1 record per customer #. There are a possibility of up
to 15 gifts.

Cust#, Name, Address, Gift1, Date1, Gift2, Date2, Gift3, Date3, etc.

Please advise.

Thanks!

 

[Guest]

You do NOT want to create another table. Instead you want to create a query
that displays the records. This can be done close to what you want with a
crosstab query.

First create a query that links the two tables. Add any criteria to filter
out records here. The query would look something like:

SELECT Customers.[Cust#], Customers.Name, Customers.Address, Gifts.GiftDate,
Gifts.GiftAmount
FROM Customers INNER JOIN Gifts ON Customers.[Cust#] = Gifts.[Cust#];

Next while in Queries on the database window, click on new and select the
Crosstab Query Wizard. Chose the name of the query that you created above.

For Row Heading chose Cust#, Name, and Address fields.
For the Column Headings chose the date field.
For Grouping you want Date or Date/Time. If it's possible that they have
more than one gift in a day, go with Date/Time.
For the Calculation I suggest Sum. You may or may not want to uncheck Row
Sums.

Run the crosstab and see if you like the results.