Access Query Double Counting

[SDowse]

Hi,

I have a very simple database, so far anyway. 2 tables:
tbl_All_Accounts - this simply has a list of account numbers
tbl_Account_Info - this has account numbers, plus a 2nd column with order numbers

What I want to do is to display all the account numbers from tbl_All_Accounts with a count of order numbers, by account number, from tbl_Account_Info.

I created a query with account number from All_Accounts and order number from Account_Info. Grouped All_Accounts, Count Account_Info - but, where there are 2 orders against an account, in the query it shows 4, if there are 3 orders it shows 6 etc...

Any ideas why?

Note, the reason for using the 2 tables is some accounts in All_Accounts are not in the Account_Info table - so I want to show all Account numbers, and then count if there are any Order Numbers.

Many thanks in advance.

 

[xenou]

If you are counting account numbers you cannot include order number in the result. That would collapse into a count of orders. Probably you will need to share your query (put the query in SQL View and copy the SQL code). Maybe you will need to provide a few rows of sample data and the expected outcome. Offhand nothing in what you describe is explaining double counting.

 

[SDowse]

Hi,

Please see below the SQL query I have:

SELECT tbl_All_Accounts.ACCOUNT_NO, Count(tbl_Account_Info.Order_No) AS CountOfOrder_No
FROM tbl_All_Accounts INNER JOIN tbl_Account_Info ON tbl_All_Accounts.ACCOUNT_NO = tbl_Account_Info.ACCOUNT_NO
GROUP BY tbl_All_Accounts.ACCOUNT_NO;

So, essentially - all accounts should show from All_Accounts, and where that account has orders in Account_Info, I want to count the order numbers, to see how many orders an account number has placed. At the moment, if an account has ordered once, it's fine - the count returns a 1. If they have ordered more than once, it doubles the amount.

 

[jackd]

Try this set up (change table names to your own)

SELECT AllAccounts.AcctNo
, Count(tblAccountOrderINfo.OrderNo) AS CountOfOrderNo
FROM AllAccounts LEFT JOIN tblAccountOrderINfo
ON AllAccounts.id = tblAccountOrderINfo.acctid
GROUP BY AllAccounts.AcctNo;

Basically change your Inner Join to LEFT JOIN

 

[xenou]

Your query works fine for me you probably need to investigate if there are anomalies in your data. My test script (this is NOT MSAccess compatible sql though):

drop table if exists #Account, #AccountInfo
create table #Account (AccountNum int)
insert into #Account (AccountNum)
    values (1),(2),(3),(4)

create table #AccountInfo (AccountNum int, OrderNum int)
insert into #AccountInfo (AccountNum, OrderNum)
    values (1,1), (1,2), (1,3), (2,4), (2,5), (3,6)

select
    t1.AccountNum,
    count(t2.OrderNum) AS CountOfOrders
FROM
    #Account t1
    inner join #AccountInfo t2
    on t1.AccountNum = t2.AccountNum
group by
    t1.AccountNum;

Result is three orders for account #1, two for account #2, and 1 for account #3, as expected. Note that a left join would be needed as explained above if you wanted to get the "zero orders" case, but that doesn't explain double counting.

 

[jackd]

xenou,
I agree. I did a mockup and ran with the sql/query I posted and got counts including those Accounts with0 Orders.
I think the double counting is something with the OP's data or set up.

 

[SDowse]

Hi all - thanks for all the posts. It may well be something to do with my setup ?.

If I pick an account number that I know has 2 orders, but is showing as 4 - if I update my query criteria to just that account number, make the Account_Info "group by" it only shows the 2 orders. If I remove the criteria, make the Account_Info count again, it reverts back to 4.

I have updated to a LEFT join btw.

 

[JonXL]

If you don't group or count anything, how many Account_Info records do you have for that account number?

 

[xenou]

If I pick an account number that I know has 2 orders, but is showing as 4 - if I update my query criteria to just that account number, make the Account_Info "group by" it only shows the 2 orders. If I remove the criteria, make the Account_Info count again, it reverts back to 4.

I'm not one hundred percent sure what this difference is. Can you post these two queries?