emma313823
New member
- Local time
- Today, 00:45
- Joined
- Jul 17, 2024
- Messages
- 10
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.
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.