referential integrity

[John T Ingato]

I recieve a spreadsheet of active and open stores from a major chain every
quarter which obviously list all attributes of each store in a separate
record. I also receive a sales report from the chain every week listing
inventory for each store and each of our products.

I have created and updated two tables to hold each of these flat files and
when I created them I left referential integrity off.

when I try to establish referential integrity, it will not allow me to
because there are new stores that just opened on the sales report, but I
have not received the updated store list yet; I only receive an updated list
once each quarter.

I would like to add all the stores that are not present in the stores table
manually and establish referential integrity. Each week when I receive a new
sales report in Excel, I import the data into the SalesTable using Import
Table.

If I do set the integrity, the next time I import a sales report I assume it
is going to fail because there is not a matching store number in the stores
table, true?

Should I leave referential integrity off? or are there other way around
this? Ultimately, I would like to import the report and if there is a new
store, add its store number to the store Table and maybe somehow flag it as
a record with incomlete info. yes? / No?

 

[Marshall Barton]

I recieve a spreadsheet of active and open stores from a major chain every
quarter which obviously list all attributes of each store in a separate
record. I also receive a sales report from the chain every week listing
inventory for each store and each of our products.

I have created and updated two tables to hold each of these flat files and
when I created them I left referential integrity off.

when I try to establish referential integrity, it will not allow me to
because there are new stores that just opened on the sales report, but I
have not received the updated store list yet; I only receive an updated list
once each quarter.

I would like to add all the stores that are not present in the stores table
manually and establish referential integrity. Each week when I receive a new
sales report in Excel, I import the data into the SalesTable using Import
Table.

If I do set the integrity, the next time I import a sales report I assume it
is going to fail because there is not a matching store number in the stores
table, true?

Should I leave referential integrity off? or are there other way around
this? Ultimately, I would like to import the report and if there is a new
store, add its store number to the store Table and maybe somehow flag it as
a record with incomlete info. yes? / No?

If you can link to the sales spreadsheet, then you could run
an append query before you try to add the stores that are
not already in the stores table.

INSERT INTO stores (storeID, storename)
SELECT DISTINCT storeidcol, "Unknown"
FROM saleslinktable LEFT JOIN stores
ON storeidcol = storeID
WHERE storeID Is Null

 

[John T Ingato]

So in other words you are saying, create an append query to bring the new
data sheet into the table instead of manually importing each time. And save
the excel file in the same directory each time and name it, let say,
I"mport.xls", and link the query to that sheet. Each time resaving the new
excel sheet over top the old "Import.xls" Then I can run the query.

Won't that still create a referential integrity error when it tries to
import a "sales" record that has a storeID that is NOT already present in
the StoreList table?

 

[Smartin]

John, Marsh's query is updating the stores table only. It grabs unknown
stores from the sales data and inserts the store id into the stores
table. After doing this you should be able to import the sales data with
RI in force because all the stores will exist.

HTH

So in other words you are saying, create an append query to bring the new
data sheet into the table instead of manually importing each time. And save
the excel file in the same directory each time and name it, let say,
I"mport.xls", and link the query to that sheet. Each time resaving the new
excel sheet over top the old "Import.xls" Then I can run the query.

Won't that still create a referential integrity error when it tries to
import a "sales" record that has a storeID that is NOT already present in
the StoreList table?

 

[John T Ingato]

I see. Thank you. I will try that. That is a great method.. so importing
the new table will be two steps: Append the store list from the report,
adding new stores, then importing the sales data. I assume I should be able
to run that as one operation through VBA or a macro.

Thank you both. I am still learning Access from Excel.. Boy, it's a
different animal.

John, Marsh's query is updating the stores table only. It grabs unknown
stores from the sales data and inserts the store id into the stores table.
After doing this you should be able to import the sales data with RI in
force because all the stores will exist.

HTH