Flatten Data

[vgillis]

Hi All.
I have a table with giving history.
Fields I need are
1. Gift Date
2. Gift Amount
3. Gift Designation

A person can have 1 gift or multiple gifts.

A vendor I am working with needs this data flattened

The table would look like this:
Gift1, GiftDate1, GiftDesg1, Gift2, GiftDate2, GiftDate2 ect..up to 10 gifts

Does anyone know how to do this?
Help would be so appreciated.
Veronica

 

[JHB]

How do you table (structure) with the giving history looks like?

 

[Cronk]

I would use two recordsets, one of all the donors and the other based on donations by each donor.

Loop through the donors recordset and within the loop have a second loop, looping through the donations for that donor and concatenating the fields to generate a string which would then be written to the output table.

You might be better taking this up in the Modules forum.

 

[MSAccessRookie]

Hi All.
I have a table with giving history.
Fields I need are
1. Gift Date
2. Gift Amount
3. Gift Designation

A person can have 1 gift or multiple gifts.

A vendor I am working with needs this data flattened

The table would look like this:
Gift1, GiftDate1, GiftDesg1, Gift2, GiftDate2, GiftDate2 ect..up to 10 gifts

Does anyone know how to do this?
Help would be so appreciated.
Veronica

The structure that you have appears to be proper, and flattening the data would go against the standards and practices of Relational Databases. Besides, maintaining the Database records once ten gifts have been given becomes cumbersome to say the least.

Does the vendor really need to have a flatter Database, or would a Report or an Excel Spreadsheet that was Flat be closer to what they need?

A Report that shows the most recent ten Gifts for each Donor could be created, and that way the Database would be able to retain a history of gifts beyond the most recent ten.

-- Rookie

NOTE:

I notice that Cronk has provided a VBA Approach that is similar to the Report that I was describing that would be a good approach for creating Excel Spreadsheets.