Database Schema help

[Jason G]

I'm building my third DB in Access and although i understand the basic
priciples am very new to designing their layouts.

I have a spreadsheet whis has alot of manual intervention being run on it
and basically it needs to be turned into a DB. I have fields in my
spreadsheet such as:

Agent
DataDate
Site
CustName
CoName
Address1
Address2
Address3
Postcode
VIN
RegNo
RegDate
WorkDue
Outcome

Now my understanding of DB's is that several of these fields should be
stripped out into seperate tables with look up fields in the main table
pointing to these seperate fields.

I understand how these seperate tables work and interact on the premise that
new records would be created in a form and all corresponding tables populated
accordingly, this functinality i need as the outcome field will be populated
by the agents (and will prompt other fields to be populated dependant upon
which outcome is selected), but my problem is, I'm building this databse with
a view that eack week a excel dataset will be generated holding around 2000
records and will need to be imported in the DB.

If i normalise the DB and split out fields into seperate tables, how do i
import data into it each week that holds data accross most of these seperate
tables?

thanks

Jason

 

[Jeff Boyce]

Jason

A common approach to migrating 'flat' Excel data into relational tables is
to first link to (or import) the Excel data as "raw".

Then create "normalizing" queries that distribute the raw data appropriately
among the tables. You can run these queries individually, or bundle them
together using code or a macro.

Good luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jeff Boyce]

.... by the way, avoid using "lookup fields" in your Access tables.

Access tables store data, Access forms display it -- use the forms and
comboboxes to do 'lookup'.

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.