Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Has it been done?

Discussion in 'Information Technology' started by Sam, Jul 28, 2006.

Tags:
  1. Sam

    Sam
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    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" <sampahkertas@yahoo.com> wrote in message
    news:1151515650.742989.201520@x69g2000cwx.googlegroups.com...
    > 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
    >
     
  4. Sam

    Sam
    Expand Collapse
    Guest

    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 wrote:
    > 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" <sampahkertas@yahoo.com> wrote in message
    > news:1151515650.742989.201520@x69g2000cwx.googlegroups.com...
    > > 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
    > >
     
  5. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    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


    "Sam" <sampahkertas@yahoo.com> wrote in message
    news:1151518810.112485.40880@d56g2000cwd.googlegroups.com...
    > 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 wrote:
    >> 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" <sampahkertas@yahoo.com> wrote in message
    >> news:1151515650.742989.201520@x69g2000cwx.googlegroups.com...
    >> > 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
    >> >

    >
     
  6. Sam

    Sam
    Expand Collapse
    Guest

    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


    Jeff Boyce wrote:
    > 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
    >
    >
    > "Sam" <sampahkertas@yahoo.com> wrote in message
    > news:1151518810.112485.40880@d56g2000cwd.googlegroups.com...
    > > 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 wrote:
    > >> 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" <sampahkertas@yahoo.com> wrote in message
    > >> news:1151515650.742989.201520@x69g2000cwx.googlegroups.com...
    > >> > 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
    > >> >

    > >
     

Share This Page