Frequently updated data means referential integrity causing issues

[Vaslo]

Hello,

I have created my first database with a table of "primary keys" linked to a bunch of other tables. I think this is referred to as 1NF?

While I was proud of my work of art and the ability to keep referential integrity, now that new data has come in it is difficult to upload the file.



To describe, the data base has transaction information by day for sales of chemicals, linked to tables with customer, product, date, company, etc. This needs to be updated every week, and without question new products, customers, etc will be going in/out of the data.

Should I enforce referential integrity? In general, how do you handle these issues in Microsoft Access? How do I know when something new has come in, because I have to update a table to, say, make sure the new product code has the right container type.

Thanks!

[June7]

There must be identifiers that are common to the source data and the destination db - CustomerID or AccountNum, ProductID or ProductNum, etc.

What format is the source data - Excel, CSV?

Set links to the source data.

Run UPDATE sql action to edit existing customer and product records.

Use Find Unmatched queries to determine if records (CustomerID, ProductID) in the source are not in the db, then APPEND the new ones.

APPEND sales transaction records.

[ssanfu]

Would need to see how the tables are related. Sounds like you have linked the tables PK to PK (a one-to-one relationship), instead of PK to FK (a one-to-many relationship).

Would you post a pic of your relationship window?

[Vaslo]

Here's a picture of the relationship map. Note that in the top corner there is a relationship that is a many to many because I want to be able to group a bunch of profit centers into groups, and some profit centers will end up in multiple groups.

Data source is a group of CSV files downloaded each week from an Oracle Database. Wish I knew how to just pull into Access from the Database but thats advanced for me. I have to download each report as a CSV and save for now.

[June7]

So Weekly Data has a composite key/index?

Don't see a PK for Profit Center Groups. Don't really understand the two Profit Center tables. Why not one table? Nor Customer Name and Customer Long. Again, why not one table?

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[Vaslo]

Weekly data is a composite key because each of those must be put together to create a unique record. For example, you can have the same profit center, company code, Fiscal week, customer, and product number, but you may ship to two separate locations. Without a compound key I can't distinguish the unique record.

Profit center groups is as follows, hope i can explain (and could be doing completely wrong). Lets say I have profit centers 1000, 2000, 3000, and 4000. All four report to a VP. 1000 and 2000 report to one director, 3000 and 4000 report to another. Each one has a manager. I tried to use the Database to create a way to separate the groups in reporting. As you can see, there is overlap. For example, 1000 could be part of a managers report, a directors report, or a VPs report. If I select the VP in the query, it ensures I pick up all four. Is there a better way to do?

Customer Name is the billing name. However, sometimes our sales guys look at the group. So for example: A customer called the Jones company may come up in our database as Jones LLC, Jones Chemical Company, and Jones Brother Chem. Sales guys want to just see Jones. I tried to create a lookup table to be able to group. I could add another column and tag each with its group, but aren't I supposed to have a separate table to avoid redundancy?

I will fix the table names per your suggestion to exclude spaces.

[June7]

All those Jones variations are the same company? Names make very poor unique identifiers. What if there is a Jones Company that has nothing to do with the other Jones versions?

Why would multiple names for a company be allowed? But if you must, consider:

tblCustomer
CustomerID (primary key)
CustomerGroupName

tblCustomerNames
CustomerID (foreign key)
CustomerName

[Vaslo]

Unfortunately I didn't setup the back end. This is the 3rd large corp I have been with and I've seen this structure when it comes to billing at the request of the customer. I'll see if they give them unique codes to your point, and I'll instead use those. Maybe the customer number is different depending on the billing customer.

What is your thought though on the Profit Center piece I described? Is there a better way in a relational database to handle overlapping groups like this? I suppose its a many to many that can be mapped? 1000 can go to either the VP, Director, or Manager depending on what is queried and conversely the VP gets more than just the 1000 code...what other way can this be done?

[June7]

Thankfully, I've never had to build an accounting db. I worked with QuickBooks and GreatPlains.

So profit categorization is dependent on group (1000, etc) and subgroup (VP, Director, Manager, etc) - or the reverse for group/subgroup?

I suppose options are to save both values into record or to have a master table of all possible group/subgroup combinations, and save the recordID of that table into the data record.

[Vaslo]

I think yes based on your explanation. Here's a better way to describe. Let's say I have 4 divisions - 1,2,3, and 4. All 4 divisions are up under the president. Sometimes the president wants to know how he is doing, so I would need to query on all four. I might call that record key "President". Division 1 and 2 roll up under the Director named John (who reports to the president). So sometimes I don't want "President" which is divisions 1,2,3, and 4. I want to query instead on "John" which only gives me 1 and 2. Further down the chain, John has two reports named Bill and Sue. Sometimes I only want to see Bill's piece of the business (division 1) so instead of querying on John or President, I query on "Bill".

So in short, Sometimes you want 1. Sometimes you want 1 and 2. Sometimes you want 1,2,3, and 4. You don't necessarily want ALL permutations but a lot of them! I thought creating a group was the way to go. Right now its a two column table. It would look like this:

1 President
1 John
1 Bill
2 President
2 John
2 Sue

I know, there is probably a better way...

[June7]

Seems appropriate to me. Decide whether to save the recordID or both values into data record. Then construct search parameters appropriately. You might find UNBOUND cascading comboboxes for entering search criteria useful.

[Vaslo]

Thanks June. Lots Lots Lots to learn but significant progress since last week.