Results 1 to 6 of 6
  1. #1
    V Brown is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    3

    uniques values help

    I am new to creating queries and do not know how to build this expression.

    I have a tables that lists each transaction in accounts payable and another for each check. How do I get only the total amount of each check and not all the entries for all the transactions?


    The problem is that many invoices hit multiple g/l accounts and each breakdown creates a new transaction. I cannot sort only by check number because I also need to know all the invoices that are unpaid.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    i have a form, with 2 text boxes: txtDateStart, txtDateEnd
    i enter date range, and the query uses it to pull the data

    select * from table where [TransDate] between forms!myForm!txtStartDate and forms!myForm!txtEndDate

    you may need 2 more queries, the above is the base query,
    Q2, pull InvoiceID that are unpaid, (set query property to UNIQUE VALUES = TRUE to remove dupes)
    Q3, to get the details of Q2.

  3. #3
    V Brown is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    3
    I am not sure I follow but I will try to see if I can make it work. Thank you for the advice.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    We really need to see your table design(s) to respond with more focus.
    Ease of use and query possibilities are quite related to normalized design.
    Good luck.

  5. #5
    V Brown is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    3
    I am pulling tables from an unsupported Oracle database. I may be missing needed tables or links but I believe I have all that is necessary to run the query. There used to be a way to limit your results to unique values for certain columns but I cannot find a way now to do that. I am not choosing to display the fields that create the multiple entries and I have set the query properties to unique values. I could sort by check number but that would exclude those entries that have not yet been paid. I suppose I could create two queries instead.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Is this a one-time exercise?
    You are accessing the oracle via ODBC?

    I may be missing needed tables or links but I believe I have all that is necessary to run the query.
    There is nothing we can advise here without more detail. Do you have a design document/relationship diagram for the oracle database?
    Typically you would identify the output you need from your data; then identify where said data exists (table/field); and mock up your query to ensure you have the necessary input data.

    You need to determine what fields make the data you need unique. eg CustomerID + OrderID or whatever.

    With CheckNumber you would seem to also need who (CustomerAccount), when (CheckDate) and HowMuch (Amount).

    You may be dealing with multiple concepts.
    Orders ---someone buys something(s) for some Amount
    Payments-- time related payment amount to pay off a debt (OrderAmount)

    Good luck

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 10-30-2016, 05:50 AM
  2. Replies: 19
    Last Post: 09-09-2014, 01:36 AM
  3. Replies: 4
    Last Post: 07-10-2014, 02:24 PM
  4. Removing duplicates & printing uniques
    By sp3cialed in forum Access
    Replies: 1
    Last Post: 08-06-2011, 12:31 AM
  5. Replies: 1
    Last Post: 03-27-2010, 06:13 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums