Automatic Split of Excel Table to two Access Tables that are linked...is it possible?

[tv69]

Hello,

I'm new to MS Access and new here. I'm working on a project using Excel, mainly because that is how the person has been running it so far. I believe MS Access will be a better solution and thus I'm here to learn more and try things out.

I will describe a simplified example for what I am trying to do. I have an Excel file that lists Car Models in Column A, Transmission Type in Column B, and Car Color in Column C. There is a lot of duplicated information and it would be ideal to use a relational database to eliminated duplication of data.

Car models in Column A may include VW Jetta, VW Golf, VW Passat, VW Toureg, Porsche Turbo, Porsche Carrera, Porsche Cayenne, Porsche Panamera, etc...

Transmission Type in Column B will include Automatic and Manual, for each of the models listed.

Car Colour in Column C will include Black, White, Blue, Silver, Gray, etc... for each of the Transmissions and models Listed.

So VW Jetta will be duplicated in Column A a total of 10 times with the example above (2 transmission choices and 5 color choices for each transmission), as will all of the models listed.

In Access I can create 3 tables for each of the columns in the Excel Spreadsheet, each table with its own primary key ID AutoNumber.

The examples of linking the data between tables that I have seen thus far are all manual. You include a similarly named foreign key in the related table and populate it with all the variations. But I already have my variations stored in the Excel Spreadsheet. Is there a way to achieve all the desired variations in the related access tables automatically by use of the Excel file or by some other manner. The example I have provided is a simplified version of what I am trying to do and manual linking at this point seems cumbersome, tedious, and error prone with thousands of records.

Please tell me there is a better solutions than manually creating all the links...or perhaps my limited MS Access knowledge is making me miss something. Either way, any help would be much appreciated.


Cheers,

TV

[June7]

You don't need to split this table to 3 tables. Numeric PK/FK not mandatory.

If you want lookup tables (a table of vehicles, a table of transmission types, a table of colors) to use as sources for combobox lists, that can be done through queries. Create 3 new tables with:

SELECT DISTINCT ColumnA INTO Vehicles FROM main;

SELECT DISTINCT ColumnB INTO Transmissions FROM main;

SELECT DISTINCT ColumnC INTO Colors FROM main;

Now if instead of saving the text descriptors in main you want a numeric PK/FK, that will require a little more work.

In each of the 3 new tables, create an Autonumber PK field.

Create 3 number type fields in main.

Run UPDATE queries to populate the PKs into main, like:

UPDATE main INNER JOIN Vehicles ON Vehicles.vehiclename=main.ColumnA SET main.VehicleFK = Vehicles.ID

When all is good, delete the text descriptor fields from main.

Now build query that joins all 4 tables so the related text descriptor info can be viewed in a single dataset which will look like the 1 table you started with.

Access Help has guidelines on using the query builder.

[tv69]

Hi June7

Thank you for the reply. I'm going to try to work out what you have suggested but since I am new to Access it will take me some time to figure it all out. I will try to report back once I have figured things out.

Thanks,

TV

[tv69]

In my example in post #1, the color choices given were identical for all car models. This oversimplified my situation as there will be color choices for one model vehicle that are not available for another. Is there a way to set up the color column to dynamically change based on the car model selected?

TV

[June7]

Add a field in color table that associates car models with colors.

Model	Color
1	blue
1	green
2	red
2	blue
3	green
3	red
4	silver

Then the color combobox RowSource can be dependent on the car model selected in another combobox. Review http://www.datapigtechnologies.com/f...combobox2.html

[tv69]

June7,

I should have been more specific and asked how to have a dynamic combo box selecting appropriate color for car model from the original data contained in the spreadsheet (no splitting of data into multiple tables). Can it be done that way?

TV

[June7]

Hmmm, never tried that. Maybe:

SELECT DISTINCT Color FROM main WHERE Model=[model combobox name];