Results 1 to 2 of 2

Query returns combinations of values from two tables when I want one result per value.

  1. #1
    Puzzled is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    1

    Query returns combinations of values from two tables when I want one result per value.

    I have tables in Access linked to Excel where I enter my data. One of my tables is a list of payees and invoices. One table (my downloaded bank statement) is a list of payees and payments.
    I want to see a result which shows a list of payees (grouped by payee) each followed by a list of invoices and amounts and a list of payments and amounts so I can see who owes me money.

    When I try this Access returns all combinations of invoice amounts and payment amounts which is very unhelpful.

    Can any body advise if there is an easy way round this please.

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,307
    You need to Join (link ) the tables in the query view on the payees from each table.
    This may not be straight forward to do in one query, depending on your data structure, as you will have payees with invoices, without payments, so you will need to perform an left join to show all payees with invoices and any potential payments.

    If you get stuck, I would post up some sample data, what you have in each table , and what you expect to see as a result.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Replies: 2
    Last Post: 03-15-2018, 02:36 PM
  2. Replies: 7
    Last Post: 04-27-2017, 11:00 AM
  3. Replies: 3
    Last Post: 10-30-2016, 05:50 AM
  4. Replies: 9
    Last Post: 05-08-2013, 02:37 PM
  5. Aggregate Query Returns No Values
    By Xiaoding in forum Queries
    Replies: 6
    Last Post: 03-29-2010, 02:01 PM

Posting Permissions

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