If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Database Schema help
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 |
#2
|
|||
|
|||
Database Schema help
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. "Jason G" wrote in message ... 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 |
#3
|
|||
|
|||
Database Schema help
.... 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. "Jason G" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|