UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Import Excel Data To Access Table - Retain Primary Keys, Access 2016    
 
   
chrisk1979
post Aug 8 2019, 09:27 AM
Post#1



Posts: 1
Joined: 8-August 19



I am working on an inventory database in Access. I have a build of materials list in Excel. The part numbers have prefixes, bases and suffixes. Some of the part numbers are on the build of materials multiple times. Some of the part numbers change occasionally (suffix only; prefix and base will always stay the same). The changes happen often enough (not only part suffix changes, but parts being added, deleted or moved around) that I want to automate the import process of the parts on this build of materials on a daily basis to make sure I keep up with all changes automatically.

In Access, I am going to be keeping track of stock levels, invoicing, receiving new quantities, etc. - all kinds of activity which requires these parts to have a static primary key because I need to retain history and accurate stock levels of each part.

How can I import this data from Excel into Access on a regular basis and have Access:
1) Append any new parts (add to the end)
2) Ignore any existing parts that haven't changed at all
3) Replace or update part number (suffix) on those that have changed suffixes and retain its primary key, and therefore all its history, etc.

AND I also only want every part on the build of materials in my Access table only ONCE regardless of how many times it appears on the build of materials Excel file - I don't want more than one instance of a part number with a different primary key for each.

Please let me know if anyone can help or if any more clarification is needed.

Thanks!
Go to the top of the page
 
tina t
post Aug 8 2019, 02:24 PM
Post#2



Posts: 6,074
Joined: 11-November 10
From: SoCal, USA


QUOTE
The part numbers have prefixes, bases and suffixes.
...
How can I import this data from Excel into Access on a regular basis and have Access:
1) Append any new parts (add to the end)
2) Ignore any existing parts that haven't changed at all
3) Replace or update part number (suffix) on those that have changed suffixes and retain its primary key, and therefore all its history, etc.

to figure out how to do it in Access, you need to start with how you do it manually. for instance:

1) if you define a new part as one where the combination of prefix/base/suffix is unique, then you can write a query against the Excel data to pull only those unique combinations that don't already exist in the Access table. if you define a new part some other way, what is that way?

2) you can query against the Excel data to exclude any prefix/base/suffix combination that already exists in the Access table.

3) if my number one above is correct, then how do you determine that a part has a changed suffix, and is not in fact a new part? again, it depends on how you define a new part.

if you're not sure how to write the queries for 1) and 2), try using the query wizard - hopefully it still has an option for a "not-in" query, as in this value in table A (a linked Excel/text file) is not found in table B (the Access table).

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th September 2019 - 11:11 AM