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.