Append Queries - Seek Old Invoices

  • Thread starter RobertPlant via AccessMonster.com
  • Start date
R

RobertPlant via AccessMonster.com

Hi Experts/Enthusiasts,

Could you please help me on this problem regarding INSERT query, we have had
two tables
Say for example, Payment Table and AccountsReceivable Table both they have PK.
Now, if payment is made is Payment Table, then it mus be insert into
AccountsReceivable Table thus creating a scenario like this:

Let say, FIRST PAYMENT
CilentCode: XXX09-12
PaymentRefNo: 1122
PaidDate: 12-Apr-2009
PaidAmount: 10,000

CilentCode InvNo InvDate PayRef PaidDate Debit
Credit Balance
XXX09-12 009-001 01-Mar-2009 10,000
0.00 10,000
XXX09-12 1122 12-Apr-2009
10,000 0.00
XXX09-12 009-002 05-Mar-2009 15,000
0.00 15,000
XXX09-12 009-003 07-Mar-2009 10,000
0.00 25,000

The mystery is, our access always insert the new record in last row of the
recordsets,
I just want to insert payment details in AccountsReceivable subform, BUT IT
MUST LOOK FIRST THE INVOICES WHICH IS NOT PAID, like the example above. Is
there any chance we can do the same as well?
If SECOND PAYMENT IS MADE, THEN IT WILL LOOK LIKE THESE:

Then SECOND PAYMENT:
CilentCode: XXX09-12
PaymentRefNo: 1133
PaidDate: 15-Apr-2009
PaidAmount: 15,000

CilentCode InvNo InvDate PayRef PaidDate Debit
Credit Balance
XXX09-12 009-001 01-Mar-2009 10,000
0.00 10,000
XXX09-12 1122 12-Apr-2009
10,000 0.00
XXX09-12 009-002 05-Mar-2009 15,000
0.00 15,000
XXX09-12 1133 15-Apr-2009
15,000 0.00
XXX09-12 009-003 07-Mar-2009 10,000
0.00 10,000

I need your expertise regarding the above samples, please help me experts..
paging all the MVPS / Enthusiasts!
 
K

KARL DEWEY

You need to set a one-to-many relationship between the tables. I assume
CilentCode is the common field and therefore must be the primary key in the
'one' side of the relationship. CilentCode would be the foreign key in the
'many' side. When establishing the relationship select Referential
Integerity and Cascade Update.

Use a form/subform for Client/Invoice with Master/Child links on CilentCode.

When you add a new record in the subform for payment the CilentCode is
automatically added to the table.
 
R

RobertPlant via AccessMonster.com

Hi Karl,
Thanks to your reply! There are a lot of Customers to be entered in it, Okay,
let us forget about the primary key (PK) on each table, this table is
AccountsReceivable, so not only payment details will be entered on
AccountsReceivable table, Invoices will be entered also so they will create
Accounts Receivable to each Customer/Client respectively, what I want to do
is to Insert all these transactions in accounts receivable table. That's why,
every time a payment been made, the program must locate the old invoices and
insert the payment in it, until the invoice will become zero, hope you see
the scenario Mr. Karl. Thanking you, Robert Plant.


KARL said:
You need to set a one-to-many relationship between the tables. I assume
CilentCode is the common field and therefore must be the primary key in the
'one' side of the relationship. CilentCode would be the foreign key in the
'many' side. When establishing the relationship select Referential
Integerity and Cascade Update.

Use a form/subform for Client/Invoice with Master/Child links on CilentCode.

When you add a new record in the subform for payment the CilentCode is
automatically added to the table.
Hi Experts/Enthusiasts,
Click to expand...
[quoted text clipped - 49 lines]
I need your expertise regarding the above samples, please help me experts..
paging all the MVPS / Enthusiasts!
Click to expand...
Click to expand...
 
Top