[Help] Import & Validating Excel Spreadsheet (1 Viewer)

[cnstarz]

Say I have a spreadsheet the following spreadsheet...

[Color]   [Shape]   [Field3]   [Field4]
Red       Circle    Data       MoreData
Blue      Square    Data       MoreData
Yellow    Circle    Data       MoreData

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

[Color]
Red
Blue
Yellow
Orange
Purple
Pink
Black

Shapes

[Color]   [Shape]   [Field3]   [Field4]
Red       Circle    Data       MoreData
Green     Square    Data       MoreData
Orange    Triangle  Data       MoreData

How would do I get Access to read each Excel row and:

• 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.

I hope this makes sense!

 

[Trevor G]

Why not consider running a duplicate query so you can identify the duplicates and deal with them in access.

 

[jdraw]

You could make a unique composite index of color and shape, that would give a message if you attempted to add a duplicate.