Trying to figure out the best way to group records

[mdnikki]

I have been given a data file from an outside source. I am bringing it into access to create some reports.
The database are donors for the past three years. The issue is instead of the donor appearing once with donations for 2015, 2016 and 2017, they are in separate rows.

I was thinking I could create a query with this table in there twice and do some kind of update and add fields to one of the tables but I just can't seem to get it to work.

Here is an example of the data. I am trying to figure out the best way or anyway I can use a query to get these folks to appear in one row with all 3 years if they have it on that row.

[Bulzie]

Show us a sample of what the data looks like in the table. Is it like: (first number is unique ID for each donar. If not that what is there unique ID?)

1, John Doe, 2015, $100
1, John Doe, 2016, $200
2, Mary Smith, 2016, $500
3, Jane Toms, 2016, $300
3, Jane Toms, 2017, $100

[June7]

Image doesn't appear to have loaded in your post.

Possible options:

1. CROSSTAB query

2. VBA, review: http://allenbrowne.com/func-concat.html

[mdnikki]

Sorry here is the sample data, not sure why it didn't come through the first time. The unique identifier for each person is the constituent id

Attachment 31721

[June7]

Error "Invalid Attachment". However, probably won't alter suggestion. Did you consider either?

[mdnikki]

Ok I give up on the attachment ...but thanks I think I can work with the crosstab. Have some tweaking to do with it but it should do the trick. Appreciate your help.

[ArviLaanemets]

The issue is instead of the donor appearing once with donations for 2015, 2016 and 2017, they are in separate rows.

Looks like a normalized database. It is exactly the best way to handle data in Access. In year 2018, you simply add a next rows into database, in year 2019 the same, and in year 3018 too, when your database isn't crapped at this time.

When you go with donor having all years as separate fields in single row, you'll have a joy redesigning your database (tables, forms and reports) every year so long your database lives, or you have to design all future years into your table now (how many of them? 5? 10? 20? ...). And you'll have a headache writing reports or calculating statistics with such structure.

[mdnikki]

Thanks for the reply the issue that having them in seperate rows creates is...I have to take this database and "bump" it with another database. Some donor information is store in this table and some of their information is stored in another. Coming from 2 different sources.
In this database the individuals are on different rows. In my other database they are on one row with their giving for each year in columns.
So I was trying to make one table look like the other so I could join them.

If that makes any sense?

Nicol

[June7]

One db is correct structure. The other is a mess.

Options are to fix the bad structure or to manipulate data as is.

A UNION query can rearrange the bad structure to normalized.

A CROSSTAB query can rearrange the good structure to non-normalized.

Whichever way you go, the two datasets can then be combined in a UNION.

But in no case should you mess up the good structure.