Entry of Large Data Sets into multiple tables

[bcouzens]

This is my first attempt at making a relational database to fit my needs, so sorry if this is a basic question. I am attempting to make a direct marketing database with multiple tables that will accommodate the import of large data sets (csv.)

I have developed the table structures, made various queries and forms, and have modified tables with various action queries. My problem is that I don't understand how to import a large csv data set to more than one relational table in the database (Ie. splitting the csv into multiple tables that are relationally joined.)

As an example, I have a table for "Agencies" that contains all agency contact information, I have a second table for "Demographics" that charts the demographic data for each agency. I also have a "Contacts" table for charting any contact that I may have with the agency.

The problem is that the external csv data set (import data) may contain both the "Agency" data and "Demographic" data in the same table. I need to import it to the separate tables in a way that keeps the data integrity.

I thought that using a query joining the tables would work with an append function, but it seems that append only works for one table at a time.



Any help or suggestions would be appreciated.

[pbaldy]

It would help to see an example of the raw data and how it needs to end up. I'd expect multiple append queries, each pulling the key field plus the fields appropriate to its table.

[bcouzens]

Here is one version of a field from an incoming csv
Usfa-census-national2 ID Fire Dept Name HQ Addr1 HQ Addr2 HQ City HQ State HQ Zip Mail Addr1 Mail Addr2 Mail PO Box Mail City Mail State Mail Zip HQ Phone HQ Fax County Dept Type Organization Type Website Number Of Stations Active Firefighters - Career Active Firefighters - Volunteer Active Firefighters - Paid per Call Non-Firefighting - Civilian Non-Firefighting - Volunteer Primary Agency for EM? 1 #9 Area Volunteer Fire Department Route 1 Box 63 FD
Eufaux OK 74432 Route 1 Box 63 FD

Eufaux OK 74432 (918) 452-3763
MCINTOSH Volunteer

1 0 15 0 0 2


Here is the field of the final format for the "Agency" table
Agencies AgencyID First Last Title Agency Address_1 Address_2 City State_Prov ZipCode Country Phone_Wk Extension Fax Phone_Mobile EMAIL Website Memo 9498 John Doe Chief Some Fire Dept. 123 Pine
Franktown California 12345 United States 5551234567


jdoe@somefire.gov


Here is the Format for the "Contact" table (this would be entered when a direct mail piece was sent)
Contact ID AgencyID MktMatID ContactDate ContactEmployee ContactNotes 8 Some Fire Dept. First Postcard printed from local printer 5/27/2011
Test Data
Here is the general structure of the agency type table.
AgencyType AgencyID AgencyType
Fire Department
Basically the premiss goes like this. I get various data sets (hospitals, law enforcement, fire depts. etc.) I add them to a database. I sort them at various times and send out direct mail pieces for our disaster training program. I track who was sent what direct mail so that I don't send the same agency the same mail over and over again.

The data base would also track cost and conversions. The key is that all of the data sets are different. I have figured out how to solve that problem, but I don't understand how to propagate multiple tables with one data set (ie. Agency contact information in one table and agency type in another table.)

[bcouzens]

Sorry about that, I guess the tables in the preview didn't post properly.

[bcouzens]

Imaging this scenario. My incoming data consists a csv file consisting of contact information for people, and the cars they drive by make and model (all contained in one file.)

I wish to import that data into my DB, but my DB is structured as two tables, one with contact information, and one with car information. These tables are joined.

So my problem is not how do I merge information, but rather how do I split information from one csv file into my joined tables?

[NTC]

while the theory is the same; the implementation will differ if your import & split is a one time thing you are going to do in order to get the database up & running - - or - - a recurring requirement.

Import into a temp table. You must have a unique (non repeating) value in each record to enable you to cross reference. If this exists in an appropriate form then simply Append appropriate columns to table1. Then Append appropriate columns to table 2 and be sure both include that column with the unique cross reference values.

Sometimes you have an ok cross reference, but not ideal - - like a name. where a future duplicate might occur - - so you would need to create a better key, such as an autonumber in table 1 - - and then include that into a field in table2; in this scenario that works if it is a 1 time set up effort; but if you are going to import recurring then you are going to need to take some care in being sure you manage this issue correctly.

Hope it helps.

[bcouzens]

Okay, you have almost given me the silver bullet I need. I fully understand what you are getting at but I am unclear of one primary issue. I have only figured out how to use an append query to change one table, I have no idea how to change two tables. It seems like this should be easy but I am definitely missing something.

Here is the process. I convert my csv into a temporary table appended with all the correct field names. I create a query then I click on append. The pop up box asks what table I want to append. It doesn't allow me to chose more than one table, so if I knew how to append more than one table I think I would be well on my way.

FYI: Thanks for the help.

[nicknameoscar]

The way I read NTC's post it said to append one table at a time. Maybe I misread though.

[bcouzens]

I figured out a few ways to do that already, but since this is likely to be a common occurrence, I was hoping for an easier way to get the task accomplished rather than beating the data in with a crowbar.

Perhaps it can't be done, but it seems that I'm not likely to be the only one trying to do this sort of thing.