Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    150

    Unanswered: Putting boxes from 2 table in one form??

    Hi,

    How can i put Text boxes from more than one table on the same form?

    Would it be wiser to simply put everything on the same table?

    Thanks!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Just make a query and join the two tables. If you ned more help, I will need to know more about the table structures.

  3. #3
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Exclamation hi there

    Originally posted by jmrSudbury
    Just make a query and join the two tables. If you ned more help, I will need to know more about the table structures.

    Dont allways use join

    you can allso use

    SELECT Table1.Field1, Table2.Field1
    FROM Table1, Table2

    ok the result you get won't allways be as you think or want but its possible

  4. #4
    Join Date
    Nov 2003
    Posts
    150
    Thanks for the quick replies.

    I think it will be simpler to put my data into the same table. I realized it's not gonna be much more complicated. The reason i wanted to put the data into 2 tables is that i need to calulate a total from these fields and display it in the form.

    However, i'm realizing that inserting a function into a table is not quite the same as in Excel.

    The fields that i want to add are the following:

    Cash
    Visa
    Mastercard
    Paypal
    Total

    in which Total=sum of all the others

    I want to display all of these fields in the form associated with the table "Customer Transaction"

    Could you point me in the right direction? I know the calculations are very simple, but i just dont know where to enter it!!!

    Thanks!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  5. #5
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I am not sure what you are asking, but I usually put PayMethods into a different table and use a pull down list to select the pay method for the current payment.

  6. #6
    Join Date
    Nov 2003
    Posts
    150
    the problem with that way is that it doesnt support more than one payment method per transaction.

    It happens often that a customer wants to pay a part of the total on a credit card and the rest with cash. So i need the system to support more than one payment method per transaction.

    I think the best way is to have a seperate table with the amounts of each payment type entered, and the total. If the total is equal to the amount due, than the transaction is approved, otherwise, it's considered a deposit.

    The only thing i'm missing is a way to show the total as you enter the amounts. I thought it would be like adding a function to a column in Excel, but obviously, it's not.

    any ideas?

    thanks!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  7. #7
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I do exactly this. I have a separate payments table. I have a receipt table and a separate detail table, so the customer can pay off more than one item with more than one payment method. After updating the detail amount or the payment amount the total on the main receipt form is recalculated. Each payment gets it's payment method specified. The only exception is postdated cheques. They must stand alone, so I know what is being paid off by it. This is a flexible method but it is impossible to reverse engineer what cheque paid off what product when partial payments are used.

    To do the total calculation, I use dSums:

    Forms!crentry!Tendered = Nz(DSum("[Amount]", "CRPay", "[Method] = 'Cash'"), 0)
    Forms!crentry!Other = Nz(DSum("[Amount]", "CRPay", "[Method] <> 'Cash'"), 0)
    If Forms!crentry!Tendered = 0 Then
    Forms!crentry!ChangeDue = 0
    Else
    Forms!crentry!ChangeDue = Forms!crentry!Tendered + Forms!crentry!Other - Forms!crentry!Total
    End If

Posting Permissions

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