Join query in Access 2013

Currently have a single table with large amount of data in access, due to the size I couldn’t easily work with it in Excel any more.

I’m partially there on a query to pull data from this table.

• 7 Column table
• One column GL_GL_NUM contains a transaction number. ~ 75% of these numbers are pairs. I’m trying to pull the records (all columns information) for each unique transaction number in this column.

I have put together some code from googling that hypothetically should work but I think I’m missing something on the syntax or simply asking access to do what it cannot.

See below:

SELECT SOURCE_FUND, GLType, Contract, Status, Debit, Credit, GL_GL_NUM
FROM Suspense
JOIN (
SELECT TC_TXN_NUM TXN_NUM, COUNT(GL_GL_NUM) GL_NUM
FROM Suspense
GROUP BY TC_TXN_NUM HAVING COUNT(GL_GL_NUM) > 1 )  SUB ON GL_GL_NUM = GL_NUM

Hey Beth is this the suggested code? It says there is a syntax error in the FROM clause. Thanks.

SELECT * from SuspenseGL

JOIN (

SELECT TC_TXN_NUM, COUNT(GL_GL_NUM) GL_NUM

FROM Suspense

GROUP BY TC_TXN_NUM

HAVING COUNT(GL_GL_NUM) > 1

Answer

Do you want detailed results (all rows and columns) or aggregate results, with one row per tx number?

If you want an aggregate result, like the count of distinct transaction numbers, then you need to apply one or more aggregate functions to any other columns you include.

If you run

SELECT TC_TXN_NUM, COUNT(GL_GL_NUM) GL_NUM
FROM Suspense
GROUP BY TC_TXN_NUM 
HAVING COUNT(GL_GL_NUM) > 1 

you’ll get one row for each distinct txn, but if you then join those results back with your original table, you’ll have the same number of rows as if you didn’t join them with distinct txns at all.

Is there a column you don’t want included in your results? If not, then the only query you need to work with is

select * from suspense

Considering your column names, what you may want is:

SELECT SOURCE_FUND, GLType, Contract, Status, sum(Debit) as sum_debit, 
sum(Credit) as sum_credit, count(*) as txCount
    FROM Suspense
    group by
    SOURCE_FUND, GLType, Contract, Status

---

based on your comments, if you can’t work with aggregate results, you need to work with them all:

Select * from suspense

What’s not working? It doesn’t matter if 75% of the txns are duplicates, you need to send out every column in every row.

---

OK, let’s say

Select * from suspense

returns 8 rows, and

select GL_GL_NUM from suspense group by GL_GL_NUM

returns 5 rows, because 3 of them have duplicate GL_GL_NUMs and 2 of them don’t.

How many rows do you want in your result set? if you want less than 8 rows back, you need to perform some sort of aggregate function on each column you want returned.