Search code examples
group-byduplicatesms-access-2013ms-queryright-join

How do I prevent removing duplicate records from my Access query results?

The Data

I'm working in MS Access 2013. I have two tables ('Import' and 'Import-Pay'). I have a query that combines data from the two.

Import-Pay contains transaction data from a client, which includes the occasional duplicate transaction record (example: customer buys something +$50, customer returns -$50, customer changes mind and buys it again +$50). Its rare, but it happens. My issue is, when creating my clients billing report (the query), since the client is only recording date of sale in the transaction I end up with TWO +$50 records in the Import-Pay table.

The Query

I am querying the transaction data and marrying it with secondary table information on the customers via the query below:

SELECT DISTINCTROW Import.[ACCOUNT#] AS [ACCOUNT#], [Import-Pay].[Account Number], [Import-Pay].[Name], [Import-Pay].[P TRANS DT], [Import-Pay].[P Trans Amt], [Import-Pay].[Total Account Balance]
FROM Import RIGHT JOIN [Import-Pay] ON Import.[CD#] = [Import-Pay].[Account Number]
GROUP BY Import.[ACCOUNT#], [Import-Pay].[Account Number], [Import-Pay].[Name], [Import-Pay].[P TRANS DT], [Import-Pay].[P Trans Amt], [Import-Pay].[Total Account Balance];

My Issue

The tables are RIGHT joined, so ALL records from my 'Import-Pay' table should be displayed... but for some reason the duplicate records of Import-Pay are lost after the query runs giving me a different total [Trans Amt].

Troubleshooting

  1. I've double checked my table join to make sure that's not the issue.
  2. I've tried removing the 'Group By' statement
  3. I've removed the 'DISTINCTROW' function

I've messed with this for two days now and I'm out of ideas. A fresh set of eyes on the problem would be greatly appreciated!

Thanks!

Solution

  • You don't have any Aggregate functions, so get rid of the GROUP BY clause. Also remove DISTINCTROW.

    Your Import-Pay table should have a Primary Key. Include this column (if it is a composite key, all columns) in the SELECT list.

    If it doesn't have a Primary Key, create one (an AutoNumber column works fine).