split imported tables and maintain links

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I have 4 tables in Excel that each describe different aspects of survey
events. Each of these tables are all related by the same three fields: Group,
Date, and Platform. The combination of these 3 fields defines a unique survey
event.

I would like to import the tables to Access (2003), and split off a new
table (called tblSurvey) containing the 3 shared fields. This would then
become the top-level table which I could use to define one-to-many
relationships to the remaining data of the original 4 tables.

The problem I have is how to maintain the links from the 4 tables upon
import. If I just needed to split one table, I could just manually do so
using the Table Analyzer, but I'm not sure how to approach this scenario
since 4 different tables need to be linked to specific combinations of the
same three data fields.

Currently, I just simply want to accomplish the import and play around with
capabilities, but eventually I hope to automate the import of future data
through VBA or macros.

Any suggestions would be greatly appreciated! - Allan
 
Create tables to receive the Excel data being imported and do your
TransferSpreadsheet into these tables. There are several advantages to this
approach. First, you have better control over data typing and formatting.
It also saves memory and processing time. Your fields will be of the defined
size. For text fields, the default is 50 characters. Usually more than you
need and sometimes not enough. You can also locate these tables in the back
end where they belong. To do the import, you first delete all the data in the
existing tables, then do the TransferSpreadsheet.
Now that you have your tables imported, you can write code or create append
or update queries as needed to get the data into your permenant tables. That
way, you don't lose your relationships or your links.
 
I'm not sure how to accomplish this with append/update queries... It seems to
me that Append queries are used to add new records or fields to a table, but
I'm not sure how to use append queries to split multiple tables that share
the same fields and maintain the links....

Could you/someone provide explicit details on how to accomplish this, or
perhaps provide suggestions on references that describe this sort of
procedure? My reference resources only seem to deal with beginning from
scratch - which seems like it would be immensely easier, but I have a few
years of historical data I need to incorporate.

Also, just in case I'm not describing this very well, below is an example of
the original Excel tables, followed by an example of the Access product I am
trying to get to.

Thanks for very much for your insights! - Allan


Example of Original Excel Table Layout
*****************************************

Table1
Group Date Platform Event Time Add'l fields...
AAA 1/1/05 A 01 08:00
AAA 1/1/05 A 03 09:00
AAA 1/1/05 A 03 09:30
ABC 1/1/05 B 01 07:00

Table2
Group Date Platform Image Add'l Fields...
AAA 1/1/05 A 1234
AAA 1/1/05 A 1111
ABC 1/1/05 B 0001

Table3
Group Date Platform Observation# Add'l Fields...
AAA 1/1/05 A 1
AAA 1/1/05 A 2
ABC 1/1/05 B 1
ABC 1/1/05 B 2
ABC 1/1/05 B 3



Respective Example of the Access product I would like to get to in order to
link tables and eliminate redundancy
*******************************************************************************************************************

tblSurvey
SurveyID Group Date Platform
1 AAA 1/1/05 A
2 ABC 1/1/05 B


* Each table below linked to the above tblSurvey by One-to-Many
Relationship via the SurveyID field

tblTable1
SurveyID Event Time Add'l fields...
(linked) 1 01 08:00
(linked) 1 03 09:00
(linked) 1 03 09:30
(linked) 2 01 07:00

tblTable2
SurveyID Image Add'l Fields...
(linked) 1 1234
(linked) 1 1111
(linked) 2 0001

tblTable3
SurveyID Observation# Add'l Fields...
(linked) 1 1
(linked) 1 2
(linked) 2 1
(linked) 2 2
(linked) 2 3

Thanks for any suggestions - Allan
 
Append queries do add new records to a table. That, if I understand
correctly is what you are trying to do.
This can be done in two steps. First, you could create a query that would
use Excel Table 1 to add records to tblSurvey. This would give you the
fields you need to link the other tables. Then your other Append queries
would need to use the Excel table and tblSurvey to create records. Let's
take Excel Table 1 as an example. you would need to join Excel table 1 and
tblSurvey on Group, Date, and Platform. From the fields in those two tables,
you can create records in tblTable1. You would need to select the fields for
tblTable1 from the fields in tblSurvey and Excel Table 1.
 
Thanks for continuing to work with me on this. The piece I'm still dull on is
how to collapse the mutliple fields from any one of the excel tables into
tblSurvey in such a way as only one record is created per unique combination
of Group/Date/Platform fields. In other words, how can I accomplish the step
that the Table Analyzer does without using the Table Analyzer? This is for 2
reasons: 1) I want to automate the step in the future, and 2) Table Analyzer
apparently uses Lookup fields/wizard to accomplish the link - but I've read
elsewhere in this bulletin that it's better practice to use realtionships
rather than lookup fields in db design.

Once I get around how to concatenate duplicated field value (combinations),
I think I got the rest figured out. Any further suggestions on how to
manually do what I could do through Table Analyzer? (Or if there is no more
elegant way, how to call on Table Analyzer through vbcode/macro to make the
breaks where I need them.)

Thanks again for all your input.

Allan
 
Back
Top