Break Integrity each week for an update taking time

[Vaslo]

All,

I'm fairly new to Access as a long time Excel power user. It was time to move a very data intensive process to Access to do the initial work before putting into Excel for use by sales associates.

I learned all about referential integrity and built a simple data base that links about 8 or 9 tables to the simplest of weekly pulls to get the access needed each week. It was a great learning process but when I went to update in week 2, nothing would happen and it's because I have ref integrity turned on. New customers, new week numbers, etc will flow in every week. It is a huge pain to break this integrity each week, only to set it again.

What should I do? Just keep it broken? Here's a perfectly good example that I think makes referential integrity somewhat overkill. I have 360 days in a fiscal year, that I can assign to fiscal months, quarters, and years through a table. I know what my entire year looks like in this table, and I'd rather just load this table yearly (or frankly one time for the next 10 years!. However, on days 1-359, I'll have days that have no data in pull to match up because those days haven't occurred...yet are in my lookup table! Its much more work for me to have to update a lookup table each time a new day flows into my data pull as I pull it weekly. Is it really the end of the world to have a table setup in advance with the days ready to go as needed? Does this make me a horrible database citizen??

Thanks.

 

[spikepl]

Provide one specific example with specific real data - not a story by itself but data, together with a screenshot of your relations windows with all tables expanded fully, so all fields are visible.

Disabling "referential integrity" each weeks suggests that your usage of it is rather unconventional.

 

[Vaslo]

I attached a copy of my relationship. I think I can be clear about this without loading specific data but I'm sure you will let me know.

"Weekly Data" is the data I pull each week from our Oracle Database.
The only "Date" data in that table is a record called Fiscal week. That is joined to a table called "Date Table". From that one Fiscal Week pull I can immediately tell what month, what month number, what quarter, etc. it falls into.

So what I am saying is this: I can put a date table into my database and never touch it again. I know by fiscal week what month, quarter, etc each fiscal week will fall into for the next 10 years. I want to drop that table in and forget about it.

Remember though that weekly data will populate with a new week each week. It will have the fields you see: Sales, Gross Profit, etc. It obviously won't have this data for 5 years from now or even one week for now. Because my Date Table has 10 years worth of combinations but my Weekly data only has up through the current week, I'll break integrity.

Is that clearer?

 

[stopher]

I know what my entire year looks like in this table, and I'd rather just load this table yearly (or frankly one time for the next 10 years!. However, on days 1-359, I'll have days that have no data in pull to match up because those days haven't occurred...yet are in my lookup table!

Why is this a problem?

 

[Vaslo]

Ok, I attached data to illustrate.

For referential integrity. If I have a few years of Dates in my "Date Table" but in my "Weekly Table" I only have about 8 weeks of data. I KNOW that I'll need the rest of the year, so I'd rather just load the rest of the "Date Table" data to avoid this step every week. I've got a bunch of dates that aren't matching in both tables, so I've violated referential integrity and Access won't let me load the new data. So very simply:

I want to load predetermined data. But one table will be updated every week from an outside system. The date table will be in the database and NEVER change. The weekly data will update every week with the new week. If date tables "Fiscal Weeks" don't match up with the same weeks in Weekly Data, I have to remove my referential integrity, add the new week and turn it back on.

I am asking, quite simply, is it ok to violate integrity to save myself trouble? This is just one table that has this issue.

As I write this and think more about it - I mean, I will have a similar issue, though one that IS a concern - where a new customer may pop up. In that case I DO want to be warned of that so I can update my tables. Just trying to find a best practice here.

 

[stopher]

Why don't you just enter your Date Table dates until the end of time and be done. You don't need to break the ref.int. to do this as this table is the primary table not the foreign key.

 

[Vaslo]

Oh wow I just tried to update the table in your suggestion and it didn't give me an error. So I should update all the other tables BEFORE I update my weekly data because the weekly data is all the foreign keys?

 

[stopher]

Exactly. Consider a simple database comprising of a Customer table and an Order table. You can't possibly create an order in the order table without a customer record in the Customer table. But you can perfectly reasonably create customer records that do not yet have orders.

What referential integrity does is ensures that the order record can't exist without the customer.

 

[Vaslo]

So essentially I've been looking at this stupidly. I thought both tables had to match exactly to keep integrity. That isn't the case after all. Thank you.

 

[Vaslo]

I take back my excitement. It was loading because I had the wrong name. When I try to overwrite the table it says "Can't overwrite table or query 'Date_Table." Why would I get this error now-I thought this wouldn't be affected by referential integrity?

 

[stopher]

I take back my excitement. It was loading because I had the wrong name. When I try to overwrite the table it says "Can't overwrite table or query 'Date_Table." Why would I get this error now-I thought this wouldn't be affected by referential integrity?

How are you updating your table? You should just append new records. You can't overwrite it because there will be records linked to the weekly data. It would be like trying to remove (and overwrite) the customer table in my previous example. The orders need to have the customer table and customers intact.

So just append new records. This sort of job is easy to do in Excel then copy paste to your table.

 

[Vaslo]

Ok so I think I've been thinking about this all wrong. Because of the dates this is one I can easily append. But for my "Weekly Data" - How do I append only the new records very simply? Each week I will have 300-1000 new transactions and they won't necessarily be in order in a way I can just "cut and paste" them over. is there a way to just update new or unique records only?

Many thanks, almost there I think. Just trying to make this as easy as possible. I want to be able to update very quickly (within an hour) so I can generate a number of reports from my data. In fact, I'd love to get a daily sales report out of this sucker which would me daily updates and these have to be fast.

Basically I am asking what is a fast way to update my records every week, or everyday. Quickly overwriting them would have been the most intuitive way, but that obviously won't work with the relationship.

 

[stopher]

For your weekly data you need to import the data and/or write an update query depending on where the data is coming from.

You will need a way to ensure you have only unique records. If your table has a unique key (which it seems to have) then when you append, you will only be able to append unique records. Access will discard the rest and notify you which is what you want.

hth
Chris

 

[Vaslo]

Ok let me try this - what if I have a combo unique key?

 

[The_Doc_Man]

The way I would do this is to first import your data into a temporary table with a known structure. This table has NO repeat NO relationships to any tables in your database.

Then, perhaps guided by a macro, perhaps by VBA code (your preference applies here), run queries to add to your independent tables (parent tables) to do something like "INSERT INTO table XYZ (FLDA, FLDB, FLDC) SELECT DISTINCT TMPFLDA, TMPFLDB, TMPFLDC FROM TEMPORARYTABLE WHERE TMPFLDA NOT IN (SELECT FLDA FROM XYZ) ;"

Once you have the updates done to all parent tables, you can start importing from that TEMP table to the child tables. But here, let's say that XYZ has an autonumber PK that we will prosaically call PKXYZ. (Hey, I'm shooting from the hip here...). So when you did the inserts of the new parent records they got new prime key values for the new records. And because of the SELECT DISTINCT, you only inserted one parent record. And because of the "IN" clause, you didn't insert anything new.

Now you can go back and import fields from the raw import (temporary) table. If it is true that you have only incremental data (i.e. no data in the temporary table that was imported in the previous cycle) then you can go back to update that temporary table with the PK values matching all of your parent tables (including the ones that existed before and so weren't added new in this cycle.) Then go back and import everything to your child tables with all the (now foreign) keys filled in.

Then discard the temporary table.

This approach has worked for me many times. It is an example of the "divide and conquer" approach. Attack the table imports - as not monolithic but rather is individual pieces that, when taken together, represent the whole data set. Work from the top down, i.e. grandparent tables, then parent tables, then child tables, etc. If you try to do it all at once, your problem will cause you to lose sleep, hair, and patience. If you do it piecemeal and then automate THAT process, you'll have to debug it a bit, but once you get it right it will be a single click of a button or macro and you will be done.

 

[Vaslo]

Thanks Doc but this way is way too complicated - I'm sure it works, but to do this daily would be challenging.

I can't believe that I am one of the few human beings that gets a new table every week with a weeks worth of new data, and that there isn't some way in Access to just quickly drop it in. In my last role there was an access data base where we looked at weekly payables, and the guys just had us download a file and the import it into access. It worked really well - I don't see why it's not working for me, except that I am enforcing referential integrity.

 

[Vaslo]

I think I found a workaround by having two tables that is a sort of like your suggestion Doc (without the SQL)- one that isn't linked anywhere but has the data uploaded to it each week and the then the one directly linked to my analysis. I run a delete query to clear out the analysis to no records, and then an append to just dump the new records in. Not as ideal as just quickly overwriting with new records but I am constrained to the system I guess.

Starting a new thread on this because I think I am losing the spirit of what I originally asked and it's probably getting confusing. Thanks for your help all.