Splitting one table in to multiple tables- how to move the data afterwards (1 Viewer)

fuddy

New member
Joined
May 21, 2009
Messages
2
Hi all,

I was wondering if I could get your opinion/advice on this situation. Hopefully someone knows the answer to my question below

Database information:
Table 1 has 20 columns and about 10k rows of data. The database is expected to grow to 30-50k entries in the next couple years. This is currently the only table in the database.

How data is currently being transferred to the database:
We use a macro that scans a folder for excel files. If there's an excel file, it automatically appends it to 'table 1' found in the database.

Problem: (Reason for wanting to split the tables)
1) Listboxes that query through 10k rows of data each time
We currently have a form that contains 4 listboxes. The listboxes dynamically update depending on the selection of the other listboxes. (ie. if you select 'car' in listbox 1, listbox 2 would show the different models of cars) The listboxes show distinct column values from table 1- basically each listbox has to scan through the whole table (10k entries) to populate its values. 4 listboxes means it has to query through 40k entries. Users would select the listbox values then press a button. That button would generate a query based on their selection. The obvious problem here is scanning through 40k entries in the form is extremely inefficient and slow. When the database grows, MS Access will probably explode from trying to query through all that data. If I split up the 1 table in to multiple tables, I can write a seperate query that selects only the relavant columns which reduces the number of entries each listbox has to go through.

2) The data in table 1 will most likely have to be changed/updated
Splitting it up in to multiple tables will be so much easier to update the data..


Question:
1) If I split single table to multiple tables, how would I move the existing data from that 1 table to the multiple tables without manually typing every row in to the 6 new tables.

2) Anybody have a different suggestion to what I should do?


Thanks all,
 

Atomic Shrimp

Humanoid lifeform
Joined
Jun 16, 2000
Messages
1,954
Personally, I would not create more than two tables in this situation (and then only reluctantly) - one containing 'current' data and the other containing 'archived' data.

Generally speaking, multiple tables with the same structure, containing the same kind of data are a really bad thing to have - because of all the complications of getting your application to talk to the right one at the right time, and the possible necessity to add new tables indefinitely into the future.

50K records is not really all that many - I've had Access tables containing nearly two million and I'm sure some other folks here could say the same (or more)

I think the problem is the way you're generating the list boxes - why not create a table listing all the makes of car, then use that for the listbox (and use the same table to create a list of options when adding new records by hand)
If someone wants to add data about a brand new make of car to the big table, they have to define it in the list of makes first (or if importing from Excel, you could update the list with new values automatically, as you import).
 

fuddy

New member
Joined
May 21, 2009
Messages
2
Thanks for the response Mike!

I think you may have the impression that I wanted to replicate the 1st table (and all the columns) to a 2nd table. Just to clarify, the reason why we want to split up the tables is to create a relational database. In other words, we're actually splitting up the columns in that 1 table in to many tables. For example, the current columns listed in that 1 table contain: "Vendors, Employees, Region". I would seperate these 3 columns and anything related to these columns in to 3 seperate tables. The 3 tables would then link to a 'transactions' table which links everything else together. If I wrote a ' select *(all)' query based on the multiple divided tables, I should get what I currently have in my 1 consolidated table.

As for the listboxes, they are dynamically linked together. If i linked it directly to my tables, the other tables would not update accordingly. Taking my previous example:

Listbox1: Car, Trees, computers
Listbox2: (if I selected Car from listbox 1, the following would show): Engine type, model number, maximum speed
Listbox 2 still: (if I selected Trees from listbox 1): Tree type, size of tree
Listbox 2 still: (if I selected both Cars and computers from listbox 1)
Engine type(car), model number(car), maximum speed(car), RAM(computers), ROM(Computers), Model number (Computers).
Listbox 3: Shows an even more detailed selection based on the values selected in listbox 2.

Essentially listbox2 is linked to listbox 1 via the query relationship. I get the same result querying a query by using a 'select distinct' statement opposed to grouping only the unique 'cars' in to a seperate table. The only difference is linking it directly to the table would save me loads of processing power. (which I want to do) But I don't know how I could do this while still having the following listboxes link together (see example above) without using a query.

However, my main question is still the following:
I already have all the data, the problem is I have no clue how I could move this data to their respectful tables besides manually inputting each row using the access form function. Is it actually possible? Any suggestions?
 

Atomic Shrimp

Humanoid lifeform
Joined
Jun 16, 2000
Messages
1,954
Ah. I see - you've got the data in a great big wide input file and you want to split it out into tables according to it's type and meaning - It sounds like you're actually normalizing your database, which is a good thing.

I would:
-Create data structures for each of the different kinds of data
then
-Import fresh data into a temporary table that preserves all fields
-Run a set of queries to append parts of the data into their relevant places
-Run a final query to clear down the temporary table, ready for next import.

Does that sound any good? If so, which bit do you want to tackle first?
 

just.a.guy

Registered User
Joined
May 14, 2009
Messages
14
Mike's method uses less SQL, and is probably quicker.
Here is an approach that I would use.

First, I assume that you will replace the data
that you move out of the "big table" with
a "key" from the target table (a foreign key).

1. Create the new table.
2. create a new query (I only use the query builder to get
started, because I like to write SQL. :)
Edit the query in the SQL (view), this is how I get
to edit it. I am not really an ACCESS person as
a SQL programmer.
The query would look something like:

INSERT INTO <NEW_TABLE_NAME>
( column_1_name
, column_2_name
, ....
, column_n_name
)
select
, b.column_1_name
, b.column_2_name
,....
, b.column_n_name
FROM <BIG_TABLE_ NAME> AS b
;

You can add "where conditions" if necessary.

I assume the new table will have
a primary key (an autonumber) field.
For this example assume the primary
key field is named "ID".

3. Modify the "big table" to add columns to
store the key of the new table.
Lets assume you call this new column
in big table "new_key".

Do this with an update statement:

UPDATE <big_table_name> AS b
, <new_table_name> AS n
set b.new_key = n.ID
where b.column_1_name = n.column_1_name
and b.....
and b.column_n_name = n.column_n_name
;

Now that you have the proper keys in the "big table"
you can use the table design tool to delete the
columns that you moved to the new table.

You can repeat this process for each table.

This is one way of doing it, but it requires
some SQL coding.

If you find it helpful, use it. If not, then sorry
for waisting your time.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom