Results 1 to 5 of 5
  1. #1
    AccessNovice16 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19

    Duplicating results from my query

    I should start by saying that I am brand new to Microsoft Access so if my post is lacking critical details, please let me know. I'm trying to create a query from two joined tables, with the following info:



    Table 1: Purchase and sales journal entries only (vendor, GL account #, invoice #, amount, description 1, and description 2)

    Table 2: Purchase and sale journal entries plus all non-vendor specific general ledger journal entries (Vendor, GL account #, reference #, amount, and some other info)

    I created a relationship between the files using "invoice #" and "reference #" since they are the same. Also, since Table 1 is a subset of Table 2, I used "Include all records from" Table 2 and "only those records from" Table 1 "where the joined fields are equal" when double-clicking on the relationship line. This seems to work okay except that when I try to pull in "description 2", which is the ultimate goal, I end up with duplicated records for each "description 2" that is unique for that invoice #. In other words, since an invoice might contain allocations to many GL accounts, there are multiple values for "description 2" in Table 1 for the same invoice #, I'm getting a record for each "description 2" even though the amounts are not right and the accounts don't match.

    I'm happy to post the SQL language if that is more helpful. As I've said, I'm a complete novice so let me know if this requires more detail. Thanks.

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So in Table1, you will have more then 1 record that matches the record in Table 2 (invoice# to reference#)? Maybe give a sample of data in both tables and what the data is showing now and what you want it to look like when you run it.

  3. #3
    AccessNovice16 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19
    Okay, thank you. Sorry, I had to type these out since I can't upload files based on my company's IT restrictions.

    Here is Table 1:

    Account Vendor Description Period Amount Reference#
    6550 CO1 Company 1 201704 $200 CREDIT201703
    6550 CO1 Company 1 201704 $100 CREDIT201703
    4565 CO2 Company 2 201704 $350 CREDIT201703
    4565 CO2 Company 2 201704 $450 CREDIT201703
    5000 N/A Journal Entry 201704 $50 Accrue expenses
    6000 N/A Journal Entry 201704 $25 Accrue expenses


    Here is Table 2 (the first four transactions are the same but with different fields):

    Account Vendor Invoice# Period Amount Description 1 Description 2
    6550 Credit Card Company CREDIT201703 201704 $200 Holiday party Party Rentals - Tables & Chairs
    6550 Credit Card Company CREDIT201703 201704 $100 Holiday party Party Rentals - Tent
    4565 Credit Card Company CREDIT201703 201704 $350 Lunch with consultants
    4565 Credit Card Company CREDIT201703 201704 $450 Meeting with bankers


    So I am trying to create a query to combine the files to pull "Description 1" and "Description 2" based on Reference#/Invoice #. These descriptions are the only thing missing from Table 1. I'm doing this just for account 6550 (first two lines) in order to keep the trouble-shooting simple. When I do this, I'm getting something that looks like the following:


    Account Vendor Description Period Amount Reference# Description 1 Description 2
    6550 CO1 Company 1 201704 $200 CREDIT201703 Holiday party Party Rentals - Tables & Chairs
    6550 CO1 Company 1 201704 $100 CREDIT201703 Holiday party Party Rentals - Tent
    6550 CO1 Company 1 201704 $200 CREDIT201703 Lunch with consultants
    6550 CO1 Company 1 201704 $100 CREDIT201703 Meeting with bankers

    The problem is that despite the fact that the descriptions for "Lunch with consultants" and "Meeting with bankers" are not for account 6550 (they are for account 4565), they are still being pulled into the file because the descriptions are different and the invoice number, which is what I'm joining on, is the same. In addition, the amounts erroneously being pulled in are for the two transactions that are booked to 6550 and not for their original amounts.

    I hope this makes sense. Thanks for your help.

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Looks like all the data from Table 1 is in Table 2 already? If that is the case why do you need to get anything from table 1?

  5. #5
    AccessNovice16 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19
    I actually need "Description 1" and "Description 2" from Table 2 to marry up with the info in Table 1 because Table 1 has journal entries (i.e. a complete set of data) and the Table 2 has a subset of that data (i.e. no journal entries.

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

Similar Threads

  1. Query is duplicating values
    By epardo87 in forum Access
    Replies: 3
    Last Post: 01-26-2017, 06:21 PM
  2. Replies: 4
    Last Post: 07-24-2015, 07:03 AM
  3. Replies: 5
    Last Post: 01-11-2013, 03:54 AM
  4. Replies: 6
    Last Post: 05-14-2012, 07:24 AM
  5. Duplicating data in query
    By thart21 in forum Queries
    Replies: 3
    Last Post: 04-07-2011, 10:50 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