Partially duplicate record on the click of a button.

[bubai]

I have a Payment_CNF table for CNF (freight forwarders) payments. In the attached DB's Cons table I have field for FK_CNF id and CNF bills. In the Payment_CNF table payments are made for each consignment's CNF bill amounts which occurs in the Cons table. That's why I have created an FK_ConsID field (which is the PK in Cons table) in the Payment_CNF table to identify for what consignment is the payment for.
The problem is, most of the times CNF bills are determined after the delivery is completed. So, often times payments are made in advance. ie before the CNF bills are recorded in the Cons table. So, I have to keep blank (Null) in the FK_ConsID field in the Payment_CNF table when recording the advance payments. Later when I get the bill, I have to assign FK_ConsID to those payment records with Nulls.
But, there is still another catch. The payment amount usually does not equate the bill amounts. ie. suppose I pay the CNF in advance of delivery of $12 and for 3 consignments my bill is total (5+5+5)= $15. So, I need to assign $10 to 2 consignment and $2 to one consignment (partially paid). Later the remaining $3 will be paid. I can't figure out a systematic way to handle this.


A probable solution I could think of is, to add a button to the form and when the blank(Null) FK_ConsID field would be filled up and Paid_Dt, and Amount fields would be changed with appropriate values, OnClick event of the button would update the CurrentRecord of the form as well as create a duplicate record to the underlying table with exceptions to the changed field. ie. for the possible 3 changed fields- ID will be DMax(...., ....) + 1, Paid_Dt will be OldValue, and Amount will be (OldValue - New value) and other 3 fields FK_CNFID, FK_ConsID and Payment Method will be as input is done.
However, I can not find out the right code for this.
Dlookup Prob1.zip

[moke123]

Hi Bubai,

It appears you haven't made some of the changes that were suggested in your prior thread https://www.accessforums.net/showthr...t=82113&page=2

take a look at this data model for invoices and payments perhaps it will give you some direction
http://www.databaseanswers.org/data_...ents/index.htm

[bubai]

Hi Bubai,

It appears you haven't made some of the changes that were suggested in your prior thread https://www.accessforums.net/showthr...t=82113&page=2

Hi moke123

It is just a part of the full database. I have made all the changes accordingly as suggested in the previous post. To change in two places is a cumbersome. That's why kept it as original. Just added one more table and form - Payment_CNF and Pay_CNF to the existing DB.

[moke123]

To be honest I cant really tell what your business is or what your work flow is.
I think you have major normalization errors in your tables.

For instance I dont know what bank value is compared to actual value. Do they even belong in the cons table or should they be in a table of their own?
what are bonds? do they belong in cons or their own table?
What does docArrived signify?
Why big and small quantities?

I cant even tell which field represents payments.

Tables should be broken down into their smallest common denominator.

If you look at the data model I linked to you will see that there is an orders table.
That orders table is related to the orderitems table and the invoice table.
the invoice table is related to the orderitems table and the financialtranscations table which is related to the transactiontypes and payments table.
you'll see that each table holds just a small part of the total data.
tables should be thin (hold just the least amount of related data) and tall(many records) They are tied together with the primary\foreign keys.

Without normalized tables you will find it harder and harder to get the information you want.

[bubai]

To be honest I cant really tell what your business is or what your work flow is.
I think you have major normalization errors in your tables.

For instance I dont know what bank value is compared to actual value. Do they even belong in the cons table or should they be in a table of their own?
what are bonds? do they belong in cons or their own table?
What does docArrived signify?
Why big and small quantities?

I cant even tell which field represents payments.

Tables should be broken down into their smallest common denominator.

If you look at the data model I linked to you will see that there is an orders table.
That orders table is related to the orderitems table and the invoice table.
the invoice table is related to the orderitems table and the financialtranscations table which is related to the transactiontypes and payments table.
you'll see that each table holds just a small part of the total data.
tables should be thin (hold just the least amount of related data) and tall(many records) They are tied together with the primary\foreign keys.

Without normalized tables you will find it harder and harder to get the information you want.

Dear moke123,
I do have related tables. eg in the Cons table I have kept as much FKs as possible. The other fields I have kept here does not have a chance of one-to-many relationship. ie they don't occur twice in this or any other tables. They belong to each occurrence of the Cons_ID PK field. The data model you referred is better suited for a sales database. But mine is import related and just focused only on the part of the import process I am responsible to look after. eg I don't need to keep track of what orders are being placed, invoice line items or financial transactions etc.
However, can you help me with the code?

[CarlettoFed]

• What does BankVakue and ActuValue mean?
  
• The Cons table should at least have the date of the delivery document.
  

Then I think you made a big mess, at least from what you see on the database.

For example, let's look at the case of payments related to delivery 2020-21 AKH Liq 4:

• in the Cons table where do you see the actual amount you should pay and then compare with those you have in the Payment_CNF table?

• why did you relate it to Payment_ID 4 and 5?

I apologize for my english but it is the one translated by google.

[bubai]

• What does BankVakue and ActuValue mean?
• The Cons table should at least have the date of the delivery document.

Then I think you made a big mess, at least from what you see on the database.

For example, let's look at the case of payments related to delivery 2020-21 AKH Liq 4:

• in the Cons table where do you see the actual amount you should pay and then compare with those you have in the Payment_CNF table?

• why did you relate it to Payment_ID 4 and 5?

Hi CarlettoFed,
Yes, payment for 2020-21 AKH Liq 4 was made in 2 installments. That's why Payment_CNF table have no 4 & 5 for this consignment. As I described in my post- payment does not necessarily equate with the bill. If you check it in the Cons table, you will find no CNF_Bill amount is assigned to this consignment. Because goods are not yet delivered. But I may need to pay him in advance. If you check the tab control on the Pay_CNF form you will find the sum of how much paid against this consignment (2020-21 AKH Liq 4) ie 1,81,000.00. Also you will find that payment no 1 has no value in the FK_ConsID field. That means money is paid as lump sum, but not yet decided for which consignment it will be assigned. You will also find this in the tab control of the form, under the tab of Unassigned CNF Payments.
For the Cons table, delivery date is not significant, but In-Bond date is, which I have put in the table.

[moke123]

The data model you referred is better suited for a sales database.

There are many others at that site. Many of the concepts are the same.

Heres the full list http://www.databaseanswers.org/data_models/index.htm

heres one on payments http://www.databaseanswers.org/data_...ents/index.htm

[CarlettoFed]

To see if I understand correctly, do you make payments with which you will have to cover the costs of the deliveries?
For the Cons table which of the two fields BankVakue and ActuValue is to be taken into consideration in order to then see the difference between the totals of Payments and totals of Deliveries?

[bubai]

For the Cons table which of the two fields BankVakue and ActuValue is to be taken into consideration in order to then see the difference between the totals of Payments and totals of Deliveries?

Neither of them. It's the CNF Bill column that has to be taken into consideration.

[CarlettoFed]

Why do you assign 2020-21 AKH Liq 4 in the Payement_CNF table what has not yet been delivered, as the amount is not in the CNF_Bill field ?



Perchè assegni 2020-21 AKH Liq 4 nella tabella Payement_CNF a Interflag qundo nella tabella Cons risulta essere fatta da Tonmoy Enterprise ?

[bubai]

There are many others at that site. Many of the concepts are the same.

Heres the full list http://www.databaseanswers.org/data_models/index.htm

heres one on payments http://www.databaseanswers.org/data_...ents/index.htm

Thanks moke123,
That is a wealth of information and it will go a long way. But for the second link, does it match the nature of my payment?
Still can you help me with the code?

[bubai]

Why do you assign 2020-21 AKH Liq 4 in the Payement_CNF table what has not yet been delivered, as the amount is not in the CNF_Bill field ?

I just told you in the previous reply. Advance payment.

[CarlettoFed]

So when you make the Payment you already have the value in the Import_ID field in the Cons table.
If this is the case, however, you cannot assign the payment of 2020-21 AKH Liq 4, which is headed by Tonmoy Enterprise, also to Interflag.

[bubai]

If this is the case, however, you cannot assign the payment of 2020-21 AKH Liq 4, which is headed by Tonmoy Enterprise, also to Interflag.

Good observation.
Actually the data entry to the tables were done on test basis alongside the designing. Later on the anomalies have been resolved, but data remained the same. The payment should belong to Tonmoy Enterprise only as recorded in the Cons table.