Merging Excel file into multiple tables? (1 Viewer)

[Dr. Juris]

I'm in the process of creating a database to track campaign contributions, and I'm kind of stuck.

I've created tables for the citizens, the candidates, and the contributions to the candidates. Now I'm trying to populate them with an Excel spreadsheet. The problem is I have no clue on how to split the Excel file so the appropriate parts go into each table.

The spreadsheet contains:
Name, address, etc. - This needs to go into the Citizens table
Names of candidates individuals contributed to - This needs to go into the candidates table.
Dollar amounts and dates of contributions - This needs to go into the contributions table.

Each citizen may have made multiple contributions to multiple candidates.

The easy way would be for everything to be in one table, but that would be a bad database design, right? Here's a shortened version of how my tables are designed:

Citizens: Name, contact info, etc. of citizens
Candidates: Name, party affiliation, etc. of candidates
Contributions: Candidate (fk is pk of Candidate table), Citizen (fk is pk of citizen table), contribution date, and contribution amount.

I appreciate the assistance, as I'm getting really frustrated.

J.C.

 

[tanski811]

Merging Excel

In the interest of saving time you should "import" your excel file to Access and then "append" the appropriate fields, in a query, to each of your tables you have predefined in Access.

You will still need to identify your primary key you want to use to link your tables together.

 

[Dr. Juris]

Thanks for the suggestion. I thought that might be the way to go, but I got stuck trying to figure out how to do it. I haven't been able to find any documentation on how to match the names up properly. For example, I tried just importing all the contact info into the citizen database so they'd get a primary key, and then appending that primary key to the original records, but I couldn't make them sync; the primary keys just got appended to the end of the database, not matched to any records.

 

[tanski811]

Matching Fields

In the query design for "Append Table" you do not have to match the names to each other. You are able to select which column to append the information. I will send you an example.

You can use an ID number as the primary key or you can use the person's name instead.

 

[tanski811]

sorry it took so long

my pics were to big. After you append the information you can use and "update query" to attach the appropriate primary key instead of appending the primary key.

 

[Dr. Juris]

Thanks, I'll try this and see if I can make it work!