Relationships

[emma313823]

Hi All,

I'm super new to Access, so I hope someone can offer some guidance on how to approach this. I had two historical Excel files which had a great deal of duplicate entry and wanted to use Access to eliminate that duplicate entry and parse out the worksheet info into different tables. I've created what I believe are my unique/static tables. I have 3 tables for data entry - one is I guess my primary table - when a check is paid I would log the basic info from a check into the tblIncomingCommissions table.

As the check moves through processing - files are prepped and logged into the Processing Log table

Finally, when the check is completed, the check value is disbursed across 5 territories. To be clear the must be 5 lines associated to every check - 1 for each territory and associated value. This is logged into the Disbursal table.

The dilemma I have is:

1. I've created the relationships you see in the screenshot, but not sure if I am supposed to enforce ref integrity...I tried and it tells me I am unable to do so.

2. In the 3 tables I provided screenshots on the commonality is the check number. It is unique to any payment made; however I can't do a separate table because new check numbers are added almost daily. I did have CheckNumber in my Incoming Commission table as primary key but learned that this was not something I should do. Once I changed that and used an auto-ID...I lost my connection between the three tables.

3. I'm stuck on finding a simplistic way to understand when to use ref integrity, cascade update related fields and cascade delete related records. When I had the check number as the primary key in the IncomingCommission table these were ok to check and had no prompts on issues, once I removed check number as primary key any connection now prompts for an issue.

Does anyone have suggestions on what I can do to connect these tables? Ultimately, I want a form that will include the primary table of Incoming Commissions with sub-tables/forms for the Disbursal table and the Processing Log table. This did work when I had the check number as the primary key, however once I removed the primary key I lost all connections on my original form.

I've also attached an excel file which has a snapshot of the tables in design view and datasheet views, as well as the access db file.

 

[CarlettoFed]

There is no attachment

 

[plog]

I think we need some more context. Can you provide 2 short paragraphs:

1. Overview of what your organization does. No database jargon allowed, pretend its career day at an elementary school and explain to us what it is you do.

2. Overview of what this database will help you accomplish. A little database jargon allowed. Tell us about the real world process this database will assist you with.

My guess is that tblIncomingCommissions.IncComm_ID should be the foreign key into tblProcessingLog and tblDisbursal and CheckNo should only be in tblIncommingCommissions and not the others. Why wouldn't that work?

 

[Pat Hartman]

1. Referential integrity is always necessary
2. IncComm_ID is the FK in the Processing Log and the Disbursal, NOT the CheckNo So remove CheckNo and substitute IncComm_ID
3. RI is always necessary. Cascade update is never used when your PK is an autonumber. Cascade Update only ever cascades changes to the PK of one table to the FKs of related tables. So if your PK is name "Pat Hartman" (bad choice of course but this is an example) then the FKs would = "Pat Hartman". If I got married again and changed my name to Pat Jones, then when you change the PK in the parent table, ALL the related FKs in all related tables would be changed to "Pat Jones". NO data ever cascades. Only PK to FK. You can't use CheckNo as the PK because the checks are coming from different sources and I presume it is possible to get a check #2344 from different companies.

Once you use the correct fields to connect the tables, the relationships will work correctly.

Also, doesn't the Sale come before the payment? Wouldn't the sale be the parent table. Then you get a payment and then you disburse the payment. So the schema would be Sale --> Payment --> Disbursal rather than Payment --> Sale --> Disbursal?

 

[emma313823]

Hi All,

I'm super new to Access, so I hope someone can offer some guidance on how to approach this. I had two historical Excel files which had a great deal of duplicate entry and wanted to use Access to eliminate that duplicate entry and parse out the worksheet info into different tables. I've created what I believe are my unique/static tables. I have 3 tables for data entry - one is I guess my primary table - when a check is paid I would log the basic info from a check into the tblIncomingCommissions table.

View attachment 115184

As the check moves through processing - files are prepped and logged into the Processing Log table

View attachment 115185

Finally, when the check is completed, the check value is disbursed across 5 territories. To be clear the must be 5 lines associated to every check - 1 for each territory and associated value. This is logged into the Disbursal table.

View attachment 115186

The dilemma I have is:

1. I've created the relationships you see in the screenshot, but not sure if I am supposed to enforce ref integrity...I tried and it tells me I am unable to do so.

2. In the 3 tables I provided screenshots on the commonality is the check number. It is unique to any payment made; however I can't do a separate table because new check numbers are added almost daily. I did have CheckNumber in my Incoming Commission table as primary key but learned that this was not something I should do. Once I changed that and used an auto-ID...I lost my connection between the three tables.

3. I'm stuck on finding a simplistic way to understand when to use ref integrity, cascade update related fields and cascade delete related records. When I had the check number as the primary key in the IncomingCommission table these were ok to check and had no prompts on issues, once I removed check number as primary key any connection now prompts for an issue.

Does anyone have suggestions on what I can do to connect these tables? Ultimately, I want a form that will include the primary table of Incoming Commissions with sub-tables/forms for the Disbursal table and the Processing Log table. This did work when I had the check number as the primary key, however once I removed the primary key I lost all connections on my original form.

I've also attached an excel file which has a snapshot of the tables in design view and datasheet views, as well as the access db file.

Sorry I thought I attached the access file. This may make it easier to understand.

 

[mike60smart]

Sorry I thought I attached the access file. This may make it easier to understand.

Hi Emma
No file attached?

 

[emma313823]

I think we need some more context. Can you provide 2 short paragraphs:

1. Overview of what your organization does. No database jargon allowed, pretend its career day at an elementary school and explain to us what it is you do.

2. Overview of what this database will help you accomplish. A little database jargon allowed. Tell us about the real world process this database will assist you with.

My guess is that tblIncomingCommissions.IncComm_ID should be the foreign key into tblProcessingLog and tblDisbursal and CheckNo should only be in tblIncommingCommissions and not the others. Why wouldn't that work?

We are a sales/commissions-based company. We represent companies called 'principals' and sell their product. Each month they pay us a commisison payment by live check or ach transfer/direct deposit. I track all incoming payments - those fields are included in the tblIncomingCommissions table. I then move to review the supporting data against the check and prepare file(s) to process into our system. Those files are tracked and logged into the tblProcessigLog for sales and commissions against the check number for that principal. The final step is to disburse the commission payment across five territories. Each check must have one row for each territory, and this is logged against tblDisbursal table where I have the check number territory and the commission value disbursed to that territory. A check can go all to one territory, a couple or all territories. The owners of the company want to see a principal check and a breakout across all five territories.

What I want the database to accomplish is a more streamline process and reduction in data entry. Currently I use up to 3 Excel files with a great deal of duplicate entry and wasted time. When I began to look at the worksheets and how I could break them out, I felt that a primary table with just the basic check details (tblIncomingCommissions), a processing log which contained the individually prepped files with sales and commissions (tblProcessingLog) and a disbursal table for territory breakout (tblDisbursal) was the best approach. I created unique tables for Principal, Territory and Commission Type. In my world, I want to have one form which includes the basic check detail, a sub-table to show the individually prepped file records associated the check number and a sub-table to show how that payment is disbursed across territories. Having the flow of a check in one view reduces my duplicate entry and keeps everything I need to see in one view as I process a payment through our system.

Below is an screenshot of a quick form I created.

I did have some feedback from another forum and tweaked my table names, removed characters etc to help with a cleaner structure.

In the screenshot I have successfully created the relationships you see, however when I try to select Enforce Referential Integrity, I get this:

This occurs on any of the relationships shown when I try to Enforce Ref Integ.

I need the three primary tables to have an established relationship and can't seem to get there. In all three CheckNo is the common and unique item, as every check number is unique, but everyone says I can't use this as a primary key, so if that is the case, I can't get a relationship from tblIncomingCommissions to tblProcessingLog and tblDisbursal.

Emma

 

[Gasman]

Use surrogate keys and be done with it.
You can make a unique index on your cheque number.

 

[mike60smart]

We are a sales/commissions-based company. We represent companies called 'principals' and sell their product. Each month they pay us a commisison payment by live check or ach transfer/direct deposit. I track all incoming payments - those fields are included in the tblIncomingCommissions table. I then move to review the supporting data against the check and prepare file(s) to process into our system. Those files are tracked and logged into the tblProcessigLog for sales and commissions against the check number for that principal. The final step is to disburse the commission payment across five territories. Each check must have one row for each territory, and this is logged against tblDisbursal table where I have the check number territory and the commission value disbursed to that territory. A check can go all to one territory, a couple or all territories. The owners of the company want to see a principal check and a breakout across all five territories.

What I want the database to accomplish is a more streamline process and reduction in data entry. Currently I use up to 3 Excel files with a great deal of duplicate entry and wasted time. When I began to look at the worksheets and how I could break them out, I felt that a primary table with just the basic check details (tblIncomingCommissions), a processing log which contained the individually prepped files with sales and commissions (tblProcessingLog) and a disbursal table for territory breakout (tblDisbursal) was the best approach. I created unique tables for Principal, Territory and Commission Type. In my world, I want to have one form which includes the basic check detail, a sub-table to show the individually prepped file records associated the check number and a sub-table to show how that payment is disbursed across territories. Having the flow of a check in one view reduces my duplicate entry and keeps everything I need to see in one view as I process a payment through our system.

Below is an screenshot of a quick form I created.

View attachment 115210

I did have some feedback from another forum and tweaked my table names, removed characters etc to help with a cleaner structure.

View attachment 115211

In the screenshot I have successfully created the relationships you see, however when I try to select Enforce Referential Integrity, I get this:

View attachment 115212


This occurs on any of the relationships shown when I try to Enforce Ref Integ.

I need the three primary tables to have an established relationship and can't seem to get there. In all three CheckNo is the common and unique item, as every check number is unique, but everyone says I can't use this as a primary key, so if that is the case, I can't get a relationship from tblIncomingCommissions to tblProcessingLog and tblDisbursal.

Emma

We are a sales/commissions-based company. We represent companies called 'principals' and sell their product. Each month they pay us a commisison payment by live check or ach transfer/direct deposit. I track all incoming payments - those fields are included in the tblIncomingCommissions table. I then move to review the supporting data against the check and prepare file(s) to process into our system. Those files are tracked and logged into the tblProcessigLog for sales and commissions against the check number for that principal. The final step is to disburse the commission payment across five territories. Each check must have one row for each territory, and this is logged against tblDisbursal table where I have the check number territory and the commission value disbursed to that territory. A check can go all to one territory, a couple or all territories. The owners of the company want to see a principal check and a breakout across all five territories.

What I want the database to accomplish is a more streamline process and reduction in data entry. Currently I use up to 3 Excel files with a great deal of duplicate entry and wasted time. When I began to look at the worksheets and how I could break them out, I felt that a primary table with just the basic check details (tblIncomingCommissions), a processing log which contained the individually prepped files with sales and commissions (tblProcessingLog) and a disbursal table for territory breakout (tblDisbursal) was the best approach. I created unique tables for Principal, Territory and Commission Type. In my world, I want to have one form which includes the basic check detail, a sub-table to show the individually prepped file records associated the check number and a sub-table to show how that payment is disbursed across territories. Having the flow of a check in one view reduces my duplicate entry and keeps everything I need to see in one view as I process a payment through our system.

Below is an screenshot of a quick form I created.

View attachment 115210

I did have some feedback from another forum and tweaked my table names, removed characters etc to help with a cleaner structure.

View attachment 115211

In the screenshot I have successfully created the relationships you see, however when I try to select Enforce Referential Integrity, I get this:

View attachment 115212


This occurs on any of the relationships shown when I try to Enforce Ref Integ.

I need the three primary tables to have an established relationship and can't seem to get there. In all three CheckNo is the common and unique item, as every check number is unique, but everyone says I can't use this as a primary key, so if that is the case, I can't get a relationship from tblIncomingCommissions to tblProcessingLog and tblDisbursal.

Emma

Hi Emma
It would be quicker to resolve this if you could upload a copy of the database with non confidential data.

 

[plog]

That error message tells you what you need to know:

Edit the data so that records in the primary table exist for all related records

You've got tblProcessingLog.Commission_Type values not in tblCommType. This query will tell you what those values are:

SELECT tblProcessingLog.Commission_Type
FROM tblProcessingLog
LEFT JOIN tblCommType ON tblCommType.CommType_ID =  tblProcessingLog.Commission_Type
WHERE CommType_ID IS NULL
GROUP BY tblProcessingLog.Commission_Type

Also, your field names are gonna screw you up. You've got 2 Commission_Type fields (and Territory and Principal) in your datbase--one is text (tblCommType) and one is numeric (tblProcessingLog). I like to name foreign keys fields (e.g. tblProcessingLog.Commission_Type) by prefixing them with 'ID_' and the table they go to (e.g. 'tblProcessingLog.ID_CommType').

Lastly, I think I nailed your relationships in my first post. CheckNo should exist only in tblIncomingCommissions. tblProcessingLog.CheckNo and tblDisbursal.CheckNo should be replaced with a foriegn key to tblIncomingCommissions (ID_IncComm). You've got an autonumber primary key in tblIncomingCommissions, now use it.

 

[Pat Hartman]

need the three primary tables to have an established relationship and can't seem to get there. In all three CheckNo is the common and unique item, as every check number is unique, but everyone says I can't use this as a primary key, so if that is the case, I can't get a relationship from tblIncomingCommissions to tblProcessingLog and tblDisbursal.

Please review my previous post. I told you exactly how to make the relationships and so did plog. We aren't going to change our minds no matter how many times you ask. Relationships are PK to FK. They are not data field to FK which is what happens when you try to use CheckNo and in this case since CheckNo cannot even be unique, you will not end up with an updateable query.

Also, in the latest version of the schema you are confusing yourself and us by using the wrong names for your FKs. Use the PK name NOT the name of the text field. So in the log table use CommType_ID as the name of the PK because THAT is the value that is stored as the FK and if you need the text name, you get it by joining to the CommType table.

 

[The_Doc_Man]

The error you ran into says one of several things: First, that for a parent/child relationship (or independent/dependent relationships if you prefer that name), every child MUST have a parent. No orphans allowed for 1:many relationships. Second, for one/one relationships (which aren't recommended anyway), you can't have a sibling relationship between two tables if one of the supposed siblings is unmatched. Third, for definition/translation/lookup relationships, you can't have an undefined/untranslated item. (This is many:1, the opposite of the first case.) And access doesn't directly do many:many so that can't come up either.

The message you got says that you had two tables but when you tried to assert a relationship between those two tables, one of the reasons I gave above was not true. Relationships are formed by matching a field in one record with a field in another record (usually in another table), typically using the JOIN ...ON syntax. Something was unmatched on the "one" side of the proposed relationship.

 

[emma313823]

Well newbie means everything takes longer to find and discover. I took all night and part of this morning and figured out what I was doing wrong. I had to figure out through trial and error how to do a mismatch query and then was able to make relationships and establish ref integ etc.

 

[emma313823]

I'm not sure how to close this thread as completed, so if anyone can help tell me how to do so, I'd appreciate it.

 

[LarryE]

Here is a suggestion for a design:

I attach the file for you to use or study or both

 

[Pat Hartman]

Relationships are formed by matching a field in one record with a field in another record

it is not "a field" to "a field". It is specifically from the PK of one table to the field we will call the FK in the other table. If the PK (or a field with a unique index) is not involved, the relationship is indeterminate because Access cannot determine the cardinality.

Joins are not relationships. You can join any two tables/queries on any set of fields that match on data type. LastName - StreetAddress, FirstName - State, StartDate - EndDate, SalesTax - PurchPrice. Doesn't have to make sense. The database engine will do its best to match on these fields and return results. These types of crazy joins generally occur when you are working with a set of bad data. Maybe someone created an excel workbook and were sloppy about which columns they used and sometimes put city in the state field and state in the city field.

 

[The_Doc_Man]

I'm not sure how to close this thread as completed, so if anyone can help tell me how to do so, I'd appreciate it.

Normally, the opener of a thread has an option to mark that thread as [Solved]. However, our site owner, Jon, upgraded to a new version of the Xenforo software that drives the site, and now he has to manually, one feature at a time, restore the optional features that he had used in the previous version. He might not have gotten around to that option yet.

Having said that, your statement that the thread can be marked as "solved" is adequate. Or you can just post an entry that says "This thread is solved." Note that we normally DON'T close threads that have remained civil and potentially informative, because it is commonplace for folks to come back and ask questions or make suggestions - like LarryE did a few minutes ago.

Finally, the way you open a new thread determines whether it CAN be marked as "Solved" - which is what I think you would want to do. But you must not have checked the box that allows for a solution to be marked. It's OK, though. What counts is that you got an answer you can use.

 

[The_Doc_Man]

it is not "a field" to "a field". It is specifically from the PK of one table to the field we will call the FK in the other table. If the PK (or a field with a unique index) is not involved, the relationship is indeterminate because Access cannot determine the cardinality.

Joins are not relationships. You can join any two tables/queries on any set of fields that match on data type. LastName - StreetAddress, FirstName - State, StartDate - EndDate, SalesTax - PurchPrice. Doesn't have to make sense. The database engine will do its best to match on these fields and return results. These types of crazy joins generally occur when you are working with a set of bad data. Maybe someone created an excel workbook and were sloppy about which columns they used and sometimes put city in the state field and state in the city field.

I was speaking generically to a new user, Pat. My wife thinks I'm pedantic enough to drive a drill sergeant batty, but I'll have to introduce her to you so she can see the difference.

Of course, you are correct - but sometimes just a leetle bit stiff about some things. Sheesh.

 

[Pat Hartman]

I know but this particular user had already made the mistake of trying to use "a field". The thread started because he couldn't get OrderNo to work as a FK. Otherwise, I would have let it pass