Updating normalized database with raw data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I developed a database that I recieve a csv price and product update for
(about 14.000 records +-) every month. I can't seem to be able to figure out
how to update the new info into the new normalized database. How do you show
relationship to a manufacturer in your manufacturers table when you add new
items, when you have a manufacturer's id on the rest of the items in a table?
I can do this easily from a form, but I can't figure out how to do it with a
file.
 
If your incoming data has a manufacturer, can you first join the incoming
data to the manufacturer table on the manufacturer name, to pick up IDs?

Then you could use that query as the source of a second query that uses the
manufacturerID to help load your "main" table.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Jeff Boyce said:
If your incoming data has a manufacturer, can you first join the incoming
data to the manufacturer table on the manufacturer name, to pick up IDs?

Then you could use that query as the source of a second query that uses the
manufacturerID to help load your "main" table.
Click to expand...

Perfect!

The best solutions are always the simplest. Now I've got all the records
for my "main" table ready to append, and I get the dreaded "key violation"
error for all the records. I checked and rechecked the field properties, and
they are identical because they come from the same csv file which I import
using the same template in Access. The fields from other tables are id
numbers from manufacturer, vendor, etc. tables. Part numbers were the key, so
I change it to an auto number field, and got rid of the index, no duplicates.
Still won't work. Is there something obvious I'm missing? I combed through
the Access query group, and I think I've gone through their standard
checklist.
 
Are you saying you get the "key violation" error when you try to append to
the main table? If so, the first place I would look is at any/all indexes
on the main table, including the primary key.

And if any of the fields on your main table are "lookup" fields, read up on
the trials and tribulations of using lookup fields at mvps.org (NOTE:
lookup tables are fine, lookup fields cause considerable confusion and
issues).

Also, if you haven't recently used Tools | Database ... | Compact & Repair,
first make a backup copy and then run Compact & Repair.

Any of those work?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Back
Top