Append Query

[emma313823]

Hi All,



I have a new database created and have finished my table structure and relationships and think I have that locked.

I had a snag I hit where I have 3 tables where data entry is performed at different stages of my process. I have incoming commissions checks paid weekly. My process is this:

1. Check comes in and I enter preliminary basic detail on the check into my tblIncomingCommisions table.
2. As the supporting data is reviewed and prepped for processing, individual excel files are compiled to pump into our system and each has a sales and commission value related to a check number entered, so new data goes into my tblProcessingLog table - basically every file prepped creates a line tied back to a check number. I could have multiple files for any given check.
3. The final step when a check is completed is the commission value will get disbursed across 5 distinct territories, so these values get entered into my tblDisbursal table - so five lines (or territories) with a value tied back to a check number.

These tables need to have a relationship and the only common unique value is the check number.

I took all my check numbers and popped them into a table. I was able to establish relationships based on each item below enforcing ref integ and cascade boxes checked:

1. One to One from tblCheck_Number to tblIncomingCommissions table
2. One to Many from tblCheck_Number to tblProcessingLog table
3. One to Many from tblCheck_Number to tblDisbursal table

The hurdle I need to jump now is let's say I receive 10 new commission checks today. I would enter the preliminary check detail into my tblIncomingCommissions table.

I need to find a way through I think an append query to take the new check numbers from tblIncomingCommissions table and have them added to my tblCheck_Number table.

1. Is this possible?
2. Can someone guide me on how to do this?
3. If this is possible, what would the general concept be to ensure the new check numbers are always updating back to the tblCheck_Number table? Do I have to always run the append query or does it automatically run?

Appreciate the feedback and help.
Emma

[mike60smart]

Hi Emma
Your use of the Check number as the linking fields is the wrong route to take.

What you actually needs is a relationship diagram along the lines shown in the attached.

Also, the field Principle is not required in tblIncomingCommissions.
Also, ChequeNo should only be in tblIncomingCommissions.

[mike60smart]

Hi Emma
revised relationships as follows;

[emma313823]

Thanks for the info...could you tell me how I am to get the IncComm_ID into the tblDisbursal table? If I attempt to make a relationship as you have illustrated, I get an error.

to your other points:

Also, the field Principle is not required in tblIncomingCommissions.

are you saying this because the principal_ID would be in the table? If that is the case, then in my form for data entry/processing how would I get the principal name to be displayed instead of showing an ID#?

Also, ChequeNo should only be in tblIncomingCommissions.

Why? When I need to go to my form which would have the 3 data tables...I want to look up the check # and then close out my processing by entering data into the disbursal table and the processing log table, then on the primary Incoming Commission table change the status to completed. With no check number in the two tables, how am I to know I'm not entering data into the wrong record? I'm just trying to understand how the flow works and the why of making the changes in the way you are suggesting.

[mike60smart]

Hi Emma
You asked Q1 "how I am to get the IncComm_ID into the tblDisbursal table?"

The answer to this question is that you would run an Update Query to Update the IncComm_ID field where the "CheckNo" in tblDispersal
equals the "CheckNo" in "tblIncomingCommissions"

You asked Q2 "how would I get the principal name to be displayed instead of showing an ID#?"

The answer to this has already been explained to you in https://www.access-programmers.co.uk...-table.331735/.

You asked Q3 "Also, ChequeNo should only be in tblIncomingCommissions."

The answer to this is that "ChequeNo" is Unique in tblIncommingCommissions". There is no need to have this field in any related table.

Your related tables would have a PK IncCommID in "tblIncomingCommissions" and your Child FK IncCommID would establish the Link
Parent to Link Child Records.

Hope this helps?

[mike60smart]

Hi Emma

Here is your database updated.

Note the coloured Controls are the Linking Parent to Child Controls.

These would be hidden from the user.

[emma313823]

Hi,

So, I'm looking at the database your worked on and am comparing to mine to see what you did and what I need to do to try and understand.

One thing right off is when I look at tblIncomingCommissions I see that you have data populated in PrincipalID and StatusID columns. When I added these to my table, I show absolutely nothing despite creating the relationship and adding them in as a column.

I also removed the Status column and Principal column per your recommendation and that created a huge dilemma because hte PrincipalID and StatusID shows nothing also.

How did you get those ID numbers into the table?



Emma

[mike60smart]

Hi
to get the StatusID data first of all I created a a table named tblStatus.

StatusID	Status
1	Completed
2	In Progress
3	Issues
4	Prepped
5	Received
6	Requested Files
7	Sent to Interlynx

Add the StatusID to tblIncomingCommissions

Then the best method to add the StatusID to the table is to take the table data into Excel

Filter your text Status field for Completed. Insert 1 for all these records in StatusID
Filter your text Status field for Received. Insert 5 for all these records in StatusID

Next copy the StatusID data from Excel and Paste into your tblIncomingCommissions - StatusID

Similar process for Principal and PrincipalID

The process can also be achieved by using Update Queries.