New user - separate a table into two tables

[Henry_Reimer]

Hi, I have a table that should be two tables. The existing table has an entry for every donation that is made to our nonprofit. What I need is one table with the names of the donors, and a second table with the donations (one-to-many.) What is the simplest way to go about deconstructing my table? Thanks!
Henry

[TheShabz]

First let's get a list of all the fields in your table. Then lists of which fields you want in your two separate tables.

[Henry_Reimer]

Sure, fields in the first table (I imagine) would include autonumber, first name, last name, street address, city, state, zip. That is, one record per donor.
Fields in the second table would include date, amount of donation, earmark, type of donation, memo. That is, one record per donation.

[TheShabz]

What do you currently currently have in your table?

[Henry_Reimer]

I have about 1500 entries (with all of the fields I mentioned) -- one entry for each donation. That is, until now, we have been adding the entire name/address for each donor every time a person makes a donation.

[Mclaren]

I am sure TheShabz meant.....what field do you have in the existing table ? not how many records are in the current table.

[goodguy]

Do not forget the foreign key in the second table. (I see you have omitted it in your tentative list!)

[goodguy]

Make your desired two tables (and create a relationship between them even before importing the data. Import the data to Donors table first by selecting the required columns only. And then to the Donations table in the same way. You will have to work around the autonumber primary key in Donors table during the import.

[TheShabz]

Before you do anything, back up your database.

Assuming all the info was typed correctly, you can run a make-table query that selects only the fields you want in your Donors table.
If you do a GROUP BY on all of them (right clicking in the field grid on the bottom of the designer and selecting Totals), assuming there were no typos, you'll have one record for each Donor in your new table. Now you add a autonumber field to that table for DonorID.
Now add a DonorID field (number) to the first table (which is now your Donations table).
Assuming no two donors have the same name, you can join off the name and update that new DonorID field with the DonorID field from your Donors table.
Once the DonorID is populated and you verified its accuracy, delete all the fields from your Donations table, except for DonorID, that is in your newly created Donors table.
Now go into the relationships and create a relationship from Donor to Donations on DonorID and enforce referential integrity.
You should be done.

[goodguy]

Very nicely explained. Thumbs up!

[Henry_Reimer]

Thanks very much, I now have a cleaned up donors table.

What I did (keystrokes): create/ query design/select my overlarge table from the show table box/close show table box/select all fields and drag them to a cell in the "field" row (down below)/click the Totals button on the Design tab (on the ribbon) which put a "group by" tag on all the fields (except one multitvalue field)/ I saved the query (crtl S) and hit Run. It didn't work -- some kind of error message.
However, I wanted the query to select unique values for the "first name" + "last name" combo, and I wanted it to select the latest value of all the other fields (that is, I wanted the latest address, latest email, etc.) so I left the "first name" and "last name" on "group by" status and changed all the rest of the field to "last" (click the "group by" cell and click the drop down menu button.) This time when I clicked Run it worked. So I went back to Design view and clicked the Make Table button on the ribbon (Design tab).
I made another donor table for "organizational donors" in the same way.
I put an autonumber primary key into both of my new donor tables.
I put two "number" columns into my overlarge table (one for the donorID, one for the organizational donorID).

In Access Help: "create a make-table query", "show distinct records by specifying which duplicate appears in your results", and "create or remove a primary key"

That's as far as I've gotten. Unfortunately, I don't know how to build the relationship between them. How will Access know how to assign the right donorID number to each donation?

By the way, good guy, I read your post and played around a bit and realized that it is much easier to move columns to a new database than I had thought (just copy and paste!) So thanks for that.

[TheShabz]

As you dont have a key to join on at the moment, you have to run an update query in your blank DonorID field in the Donations table joining on first name and last name. This is where you hope you dont have 2 donors with the same name. once you have it joined you can run an Update query to update the DonorID with what is in the newly created Donors table.

[Henry_Reimer]

Your suggestion is to populate the Donations DonorID field by getting Access to put a specific number (let's say 10) into the DonorID column every time a specific name (let's say John Smith) comes up.
Then -- the second update -- change all 10s into the number that is associated with John Smith in the other (Donors) table.
True?

Could you give me some detail on how to do these two things? I cannot figure out the criteria (or "update to") one would use in the update queries.
(I don't have two donors with the same name, by the way.)

[TheShabz]

When you add the Autonumber field to the Donors table, each donor will have their own unique ID. Now you create a Number field (note, NOT autonumber) in the Donations table and leave it blank. You then run an Update query, to match the names and update the DonorID field in the Donations table with the corresponding DonorID in the Donors table. Your query will look something like:
UPDATE Donations
SET DonorID = Donors.DonorID
WHERE Donors.FName = Donations.FName
AND Donors.LName = Donations.LName

once this is done, you will have the IDs of each Donor in your donations table. Once you've verified that they are correct, you can deleted all the fields from Donations that exist in Donors with the exception of DonorID, which will become your Foreign Key to Donors.

[Henry_Reimer]

I don't know where to put code of that kind. In the "create query" window you can only insert criteria on a field-by-field basis.