Access Query Double Counting

SDowse

Board Regular
Joined
Sep 23, 2009
Messages
120
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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
Hi,

Please see below the SQL query I have:
SQL:
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.
 
Upvote 0
Try this set up (change table names to your own)
VBA Code:
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
 
Upvote 0
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):

SQL:
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
If you don't group or count anything, how many Account_Info records do you have for that account number?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top