• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

query count problem

hey guys,

i've got a weird problem with a totals count query.

below is my QBE design view
picand here's the data table i get from this query
Count-Problem.xlsx

question: how come my count for Team Member W is sometimes 2 or 1 but the rest is all 1? account # is only in tblDataStorage and the primary key is the ID field. even after including the ID field in the query as a group by field, i can still get 1 row of ID but count of 2 for account number. if the count is really 2 for account number, then i should have 2 ID fields right? and 1 record can only have 1 account # (i've also taken the extra measure to verify this. so how come i'm still getting a count of 2? this is most perplexing!!! (i thought i was trained up well enough by EE already but seems like i'm still not good enough to solve this ha = (   )
0
developingprogrammer
Asked:
developingprogrammer
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
remove the group by (and count) function, to run the query to show the plain data.
you will see that you will have several rows for the same "group" of data, considering all fields that you are grouping by.

usually, with joining multiple tables, this is because one or more of the linked tables has indeed several records for the grouped ID ...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
though this article is not about ms access in particular, it may help understanding the issue:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
developingprogrammerAuthor Commented:
thanks angelIII

yup i did more testing and turned off the Totals and realised that the staff was entering the codes wrongly thats why for 1 ID field there were 2 codes e.g. LVA / PA so there were 2 ID rows with the same ID.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now