Access Query Double Counting

SDowse

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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
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

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

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,319
Office Version
  1. 365
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
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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.
 

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,319
Office Version
  1. 365
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

Board Regular
Joined
Sep 23, 2009
Messages
110

ADVERTISEMENT

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

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
If you don't group or count anything, how many Account_Info records do you have for that account number?
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,155
Messages
5,640,448
Members
417,143
Latest member
boukadidanizar

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
Top