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,
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,