Say I have a spreadsheet the following spreadsheet...
In Access, I have the following tables for Colors and a table for Shapes. Shapes can be assigned a color via a combo-box on a form.
Colors
Shapes
How would do I get Access to read each Excel row and:
I hope this makes sense!
Code:
[Color] [Shape] [Field3] [Field4]
Red Circle Data MoreData
Blue Square Data MoreData
Yellow Circle Data MoreData
Colors
Code:
[Color]
Red
Blue
Yellow
Orange
Purple
Pink
Black
Code:
[Color] [Shape] [Field3] [Field4]
Red Circle Data MoreData
Green Square Data MoreData
Orange Triangle Data MoreData
- See if the Color already exists in the "Colors" table:
- If No, create a new record in the "Colors" table for that color and create a new record in the "Shapes" table for that shape.
- If Yes, see if the Shape already exists in the "Shapes" table and is already assigned the same color:
- If No, create a new record in the "Shapes" table for that shape and assign that color in the [Color] field.
- If Yes, prompt user that duplicate already exists and that record will be skipped.