Duplicate records from one table in a query

[Susan W]

I want to run a query that will give me the amounts from two accounts with the same document name. I have a table created for each account (tables are named "171100" & "173100"), and created an Inner join between the tables on the column "Document" to run a query, but I get duplicate entries on the tables' Amount Column. The query looks like this in Datasheet View:

DOCUMENT 171100.AMOUNT    173100.AMOUNT
7280         ($324.50)        $89.90
7280           $89.90         $89.90
7280          $500.00         $89.90
7280         ($324.50)       $500.00
7280           $89.90        $500.00
7280          $500.00        $500.00
7280         ($324.50)      ($324.50)
7280           $89.90       ($324.50)
7280          $500.00       ($324.50)

But the tables look like this:

DOCUMENT   AMOUNT
7280       $89.90      
7280      $500.00
7280     ($324.50)

In this case, the numbers are the same in both tables, but that's not always true for all of the docments listed in the tables. Here is the SQL view of my query:

SELECT DISTINCTROW 
       [171100].DOCUMENT
     , [171100].AMOUNT
     , [173100].AMOUNT
FROM   171100
       INNER JOIN
       173100
  ON  ([171100].DOCUMENT = [173100].DOCUMENT)
 AND  ([171100].DOCUMENT = [173100].DOCUMENT)

Can anyone help? I'm stuck!

[Rabbit]

That output is correct. If you look at the results, there are no duplicate rows. Each instance of the document in the one table will join with every instance of the document in the other table. You've defined no other criteria on which to match on. So those results are correct for what you have defined.

[Susan W]

Ultimately, what I want to do is to see all of the amounts in each table that are tied to the particular document, and to have that in one place. Should I be doing this diffently? Sounds like I probably should. What's the best way? Also, ultimately I will have about 30 tables from different accounts, if that matters.

[Rabbit]

I suspect you need only one, maybe two tables. I don't have any idea what the data is supposed to represent but it's extremely unlikely you're in a position that requires thirty tables. What you probably need is one table with an account field. Rather than thirty different account tables all holding the same data.

As for the query, I have nothing to suggest. From what you have defined, the results of the query is correct. You say that you want to see the amounts for each document number from each table. But if you have three records with the same document number in each table, then you're going to have nine records to show every combination of numbers from each table.

If you look at your table data, record 1 has the same document number as record 2 and record 3. When it tries to match the same data in the other table, record 1 matches to record 1, 2, and 3. Record 2 matches to record 1, 2, and 3. And record 3 matches to record 1, 2, and 3.And that's because you're matching only on document number. It would help to post the results you're after.

[NeoPa]

It seems like you've added the DISTINCTROW predicate to the SELECT clause in the vain hope of getting rid of some of what you term duplicates in your output. That is not going to work as there is none. You've also added the same WHERE specification in twice by the way. That won't cause any problems other than naff code mind you, and possibly some confusion on the part of anyone trying to decipher it.

Your fundamental problem is to define how you want your records from the two tables to match. At the moment you only specify that they match on [DOCUMENT]. As there are multiple records in each table for the same value though, each will be paired with each of those from the other table, to produce a mini-cartesian product, which is exactly what you're seeing.

As Rabbit says, your results are exactly what would be expected from the situation you describe. You need to consider what it is that causes only one record of each table to pair with only one of the other. When you know that you can include it it your code and get the results you're looking for.

[NeoPa]

I cross-posted with you guys, but I would echo what Rabbit says about the number of tables. Tables are not supposed to be created for different sets of data, but only for different designs of data. Keep it all in the one table and include an [Account] field.

[Susan W]

Ah hah! When you guys told me my query was doing what I had told it to, you knocked a wall out of my head! I have only been using Access for 4 months, & I think I get a bit "Query-Happy". After reading the responses, I realized that I needed to import all 30 of my spreadsheets (the 30 tables) into one table using the "append" option while importing, and then do a query one the one table to sort the documents to group them together, and then I used the "Totals" function to sum the amount. Worked like a charm! Thanks for your advice; you showed me that sometimes I need to think more about tables, rather than running straight to a query!

[NeoPa]

How pleasant to hear that. Not only is your problem solved, but it was solved by you after something we commented on triggered your new thought processes. the best of all possible outcomes from our point of view. Well done :-)