Has it been done?

[Sam]

Oh well, nobody responded to my earlier posts... but I'm allowed to try
again right? Hopefully this time I got some help. Any hint/clue is
appreciated.

I have a problem with creating a form from tables involving a junction
table. What I learned from this group is: Create a form based on one
one-table and a subform based one many-table, and have the combobox in
the subform be based on query on the-other one-table.

My situation:

tblCustomer one-many tblSales
tblSales one-many tblReceipt_Allocation
tblReceipt one-many tblReceipt_Allocation

One receipt from a customer can be used to pay more than one invoice.
And one invoice can be paid by more than one payment/receipt.

I have created a form frmReceipt (recordsource: tblReceipt) and a
subform fsubReceipt (recordsource: tblReceipt_Allocation).

In the subform, a combobox is used to pick out invoices that will be
paid. Then the user will enter the payment amount for each invoice,
manually splitting the payment amount (tblReceipt doesn't have a field
for total receipt amount).

Currently this combobox is not filtered by customer. Instead, it will
list all open/not fully paid sales. So, a payment can pay for sales of
more than one customer, which isn't right.

What I would like the user be able to do when receiving a payment is:
Open a form, pick a customer, enter payment info (date etc.), pick from
a list of open invoices, allocate the payment to one or more invoices.

What do you think? Is it even possible...? Thank you!

-Sam

 

[Jeff Boyce]

Sam

To paraphrase...

Open a form.

Select a customer.

Note payment information.

Get that customer's open invoices.

Allocate payment across open invoices.

What seems to me to be missing is the one-to-many relationship between a
payment and the one/more invoices it gets applied to.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sam]

Jeff,

Thank you for replying!

Mm... The invoices can also be paid in installment... i.e. a payment is
not enough to pay an invoice in full. That's why (at least in my mind)
I have another table called tblReceipt_Allocation.

CUSTOMER
Cust_ID*
Cust_Name
....other info
Cust_Init_Balance

SALES
Sales_ID*
Sales_Cust_ID
Sales_Num
Sales_Date

SALES_ITEM
SI_Sales_ID*
SI_Item_ID*
SI_Qty
SI_Price

RECEIPT
Recv_ID*
Recv_Num
Recv_Date

RECEIPT_ALLOCATION
RA_Recv_ID*
RA_Sales_ID*
RA_Amount

[tblReceipt] doesn't store the total amount of payment made. They are
distributed among [tblReceipt_Allocation] of that particular
[tbReceipt].

So the relationship is [tblReceipt] one-to-many [tblReceipt_Allocation]
many-to-one [tblSales]

Thanks again!

-Sam

 

[Jeff Boyce]

Sam

Consider including TotalReceiptAmt in the RECEIPT table. If, in the course
of allocating, you "miss" something, your approach doesn't appear to have
any record of how much was actually received.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thank you for replying!

Mm... The invoices can also be paid in installment... i.e. a payment is
not enough to pay an invoice in full. That's why (at least in my mind)
I have another table called tblReceipt_Allocation.

CUSTOMER
Cust_ID*
Cust_Name
...other info
Cust_Init_Balance

SALES
Sales_ID*
Sales_Cust_ID
Sales_Num
Sales_Date

SALES_ITEM
SI_Sales_ID*
SI_Item_ID*
SI_Qty
SI_Price

RECEIPT
Recv_ID*
Recv_Num
Recv_Date

RECEIPT_ALLOCATION
RA_Recv_ID*
RA_Sales_ID*
RA_Amount

[tblReceipt] doesn't store the total amount of payment made. They are
distributed among [tblReceipt_Allocation] of that particular
[tbReceipt].

So the relationship is [tblReceipt] one-to-many [tblReceipt_Allocation]
many-to-one [tblSales]

Thanks again!

-Sam

Sam

To paraphrase...

Open a form.

Select a customer.

Note payment information.

Get that customer's open invoices.

Allocate payment across open invoices.

What seems to me to be missing is the one-to-many relationship between a
payment and the one/more invoices it gets applied to.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sam]

Jeff,

Yes, I am considering that option. Actually I have a TotalReceiptAmt
field in RECEIPT table, but I haven't used it in my forms. Thought I'd
follow the doctrine of normalization here and calculate the
TotalReceiptAmt using query. The total received could be typed in an
unbound field on the form as an aid to the operator. Which approach is
better?

Anyway, I'm trying to tackle one challenge at a time... About this
form, based on the underlying table structure do you think it is
possible do the following?

1. Open a form.

2. Select a customer.

3. Note payment information.

4. Pick from a list of open invoices of that customer.

5. Allocate payment for that invoice.

6. Repeat 4, 5 if necessary.

The problem lies in that Customer is not directly related to Receipt. I
tried creating a form - subform that include Customer in the
recordsource of the mainform, subformReceipt_Allocation would not show
any record or add any new record. It just doesn't work, and logically I
kinda doubt it would.

Thanks for still helping! Really appreciate it.

-Sam

Sam

Consider including TotalReceiptAmt in the RECEIPT table. If, in the course
of allocating, you "miss" something, your approach doesn't appear to have
any record of how much was actually received.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thank you for replying!

Mm... The invoices can also be paid in installment... i.e. a payment is
not enough to pay an invoice in full. That's why (at least in my mind)
I have another table called tblReceipt_Allocation.

CUSTOMER
Cust_ID*
Cust_Name
...other info
Cust_Init_Balance

SALES
Sales_ID*
Sales_Cust_ID
Sales_Num
Sales_Date

SALES_ITEM
SI_Sales_ID*
SI_Item_ID*
SI_Qty
SI_Price

RECEIPT
Recv_ID*
Recv_Num
Recv_Date

RECEIPT_ALLOCATION
RA_Recv_ID*
RA_Sales_ID*
RA_Amount

[tblReceipt] doesn't store the total amount of payment made. They are
distributed among [tblReceipt_Allocation] of that particular
[tbReceipt].

So the relationship is [tblReceipt] one-to-many [tblReceipt_Allocation]
many-to-one [tblSales]

Thanks again!

-Sam

Sam

To paraphrase...

Open a form.

Select a customer.

Note payment information.

Get that customer's open invoices.

Allocate payment across open invoices.

What seems to me to be missing is the one-to-many relationship between a
payment and the one/more invoices it gets applied to.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Oh well, nobody responded to my earlier posts... but I'm allowed to try
again right? Hopefully this time I got some help. Any hint/clue is
appreciated.

I have a problem with creating a form from tables involving a junction
table. What I learned from this group is: Create a form based on one
one-table and a subform based one many-table, and have the combobox in
the subform be based on query on the-other one-table.

My situation:

tblCustomer one-many tblSales
tblSales one-many tblReceipt_Allocation
tblReceipt one-many tblReceipt_Allocation

One receipt from a customer can be used to pay more than one invoice.
And one invoice can be paid by more than one payment/receipt.

I have created a form frmReceipt (recordsource: tblReceipt) and a
subform fsubReceipt (recordsource: tblReceipt_Allocation).

In the subform, a combobox is used to pick out invoices that will be
paid. Then the user will enter the payment amount for each invoice,
manually splitting the payment amount (tblReceipt doesn't have a field
for total receipt amount).

Currently this combobox is not filtered by customer. Instead, it will
list all open/not fully paid sales. So, a payment can pay for sales of
more than one customer, which isn't right.

What I would like the user be able to do when receiving a payment is:
Open a form, pick a customer, enter payment info (date etc.), pick from
a list of open invoices, allocate the payment to one or more invoices.

What do you think? Is it even possible...? Thank you!

-Sam