Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    tracking payments (2000-SR1)

    I have an orders table (

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: tracking payments (2000-SR1)

    I would use separate tables:

    1) A table tblOrders to store the general info about the order, with primary key OrderID. This table would contain OrderDate, CustomerID and other info relevant to the order as a whole.
    2) A table tblOrderDetails to store info about the items for an order; the primary key consists of the combination of OrderID and ItemID. This table would also contain ItemQty and the fee - copied from tblItems when a record is created. This is because you may give a customer a special price, or the price in tblItems might change over time; you don't want such price changes to affect existing orders (I presume).
    3) A table tblPayments, with primary key PaymentID (autonumber), containing OrderID, PaymentDate, PaymentAmount, PaymentMethod and other info relevant to the payment.

    You can use Totals queries to tally the total amount and total payment per OrderID.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tracking payments (2000-SR1)

    Thanks, Hans--I appreciate your help. (Did I ever mention that I am an amateur? Surprise, surprise! I'mdoing this stuff to try to help out a non-profit organization. So I do really appreciate all of your help.)

  4. #4
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tracking payments (2000-SR1)

    Question on the relationships:
    !tblOrders!OrderID should be joined to !tblOrderDetails!OrderID ?
    Which side is the one and which is the many?
    (When I tested this, data showed up in tblOrderDetails, but nothing was stored in tblOrders)

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: tracking payments (2000-SR1)

    tblOrderDetails.orderID is the many, as there can be many items in the one order.

    Can you assume that each payment will be for just one order, and will always pay it off? My experience is that this does not always happen, so I put in a PaymentOrder table between Payments and Orders so there is a many to many relationship between payments and orders. This allows one payment to be applied to more than order, and several payments to be applied to the same order.
    Regards
    John



  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: tracking payments (2000-SR1)

    John has a valid point: I assumed that a payment will always be for a single order. This leads to the relationships depicted below. The next post will depict the situation John describes.
    Attached Images Attached Images

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: tracking payments (2000-SR1)

    If a payment can be for several orders, you have a many-to-many relationship. You need an intermediate table for this, similar to the OrderDetails table. This one will relate orders and payments. See the relationship diagram below.

    See <post#=364,203>post 364,203</post#> for a sample database showing how to implement a many-to-many relationship using forms and subforms.
    Attached Images Attached Images

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: tracking payments (2000-SR1)

    I always put the customerID in the payments table as well.

    When you receive a payment, you know who it is from, before you decide what invoices to apply it to. In fact you use the customerID in the payment to choose the relevant invoices. (In the the example below, the customers are called communities.)
    Attached Images Attached Images
    Regards
    John



  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: tracking payments (2000-SR1)

    Do you allow payments from customer A for an order by customer B? If so, you should indeed include the CustomerID field in the payments table.

    (As you can probably tell, financial databases are not my area <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

  10. #10
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tracking payments (2000-SR1)

    Fortunately, I can't envision one paying for another, or that we care who writes the check--just apply that payment to the write order.
    I am working on the forms and have a "frmOrdersMain" that is linked to the CustomerTable with a lookup sort of combo box to find the right customer's record to enter his order. Then, I have a sub-form "frmOrders" based on the tblOrders. Inside of that sub-form is a (sub)sub-form "frmOrderDetails".
    The frmOrders has a text box for the autonumber field for the OrderID field in tblOrders. This fills in on both of the sub-forms as soon as I type in a CustomerID number (or any other keystroke in that text box. However, when I run that as the sub-form, with the lookup combo box, it pulls up the right Customer's records, but doesn't fill in the Autonumber OrderID (unless I do something in that text box that it then sees and inserts the OrderID in its text box. (Is that at all clear?) How do I get the autonumber to happen as soon as the right record is selected (and to fill in on both the frmOrders and frmOrderDetails sub-forms?

    Thanks again for all the help!

    Warren

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: tracking payments (2000-SR1)

    That is normal behavior. When the user selects a customer, there is no new order yet, and that's as it should be: the user might change his/her mind, or discover that he/she selected the wrong customer. The moment the user enters something in the orders subform, a new AutoNumber will be generated, and it will be filled in in both the subform and in the sub-subform.

  12. #12
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tracking payments (2000-SR1)

    But, since I am not entering anything in that sub-form "frmOrders" (the date is set by default), the first entry that I make is actually in the sub-sub-form "frmOrderDetails", making the entries in that form doesn't seem to generate the autonumber. Is there a way to make that happen inside the sub-sub-form or some other way?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: tracking payments (2000-SR1)

    You could put a command button on the main form that generates a new record in the orders subform. Air code:

    Private Sub cmdNewOrder_Click()
    If Not Me.frmOrders.Form.NewRecord Then
    Me.frmOrders.SetFocus
    RunCommand acCmdRecordsGoToNew
    Me.frmOrders!OrderDate = Date
    End If
    End Sub

    This should create a new AutoNumber.

  14. #14
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tracking payments (2000-SR1)

    Well...when I try to run the code for the command button, I get Run-time error '2046'
    The command or action 'RecordsGoToNew' isn't available now.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: tracking payments (2000-SR1)

    Hmm... the If statement should have taken care of that.

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •