By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,747 Members | 1,482 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,747 IT Pros & Developers. It's quick & easy.

Grouping Query details problem

100+
P: 365
Probably a nooby question but i'm having trouble with a query...
i have a table that holds records of batches, this table can have multiple reagents (seperate table) and uses a foregin key for the link, pretty standard stuff.

what i want to do is sum the quantities from the batch table but the query returns results that arent grouped? for example multiple entries in the batch table for the same foreign key are not grouped/summed?

Any ideas?

here is the SQL
Expand|Select|Wrap|Line Numbers
  1. SELECT tblReagent.RCat2, tblReagent.RDesc, Sum(tblBatch.BQuantity) AS BQuan, tblSiteRD.RULimit, IIf([RULIMIT]-[BQuantity]<0,0,[RULIMIT]-[BQuantity]) AS Diff
  2. FROM (tblReagent LEFT JOIN tblBatch ON tblReagent.RLID = tblBatch.BRID) RIGHT JOIN tblSiteRD ON tblReagent.RLID = tblSiteRD.RID
  3. GROUP BY tblReagent.RCat2, tblReagent.RDesc, tblSiteRD.RULimit, IIf([RULIMIT]-[BQuantity]<0,0,[RULIMIT]-[BQuantity]), tblSiteRD.RDept
  4. HAVING (((tblSiteRD.RDept)=1));
  5.  
Thanks guys

Dan
Jul 12 '10 #1
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 1,240
What are the results showing you? I suspect the grouping is there but the data is not exactly as you expected it to be. If looking closely at results does not reveal the reason, I'd try grouping on one field at a time in successive tests and look at the results.

In other words, in test 1
Expand|Select|Wrap|Line Numbers
  1. GROUP BY tblReagent.RCat2
and in test 2
Expand|Select|Wrap|Line Numbers
  1. GROUP BY tblReagent.RDesc
and in test 3
Expand|Select|Wrap|Line Numbers
  1. GROUP BY tblSiteRD.RULimit
and in test 4
Expand|Select|Wrap|Line Numbers
  1. GROUP BY  IIf([RULIMIT]-[BQuantity]<0,0,[RULIMIT]-[BQuantity]
And of course there is no point in grouping by department if you are selecting only one department.

The problem is probably number 4 where you are calculating some value. It could be the result is different on every row.

Jim
Jul 12 '10 #2

nico5038
Expert 2.5K+
P: 3,072
I concur with Jim, as far as I see you need to use in the select a Sum() for the Diff variable like:
Expand|Select|Wrap|Line Numbers
  1.  SELECT ... , SUM(IIf([RULIMIT]-[BQuantity]<0,0,[RULIMIT]-[BQuantity]))  AS Diff  ...
  2.  
and remove the IIF from the grouping.

Nic;o)
Jul 12 '10 #3

100+
P: 365
You are right, it is the calculation...

any way around that? Other than creating a second query!?

Cheers
Jul 12 '10 #4

100+
P: 365
@nico5038
Doh, always the simple stuff huh,

Thank you both you have saved me hours of misspent frustration...
Jul 12 '10 #5

nico5038
Expert 2.5K+
P: 3,072
Glad we could help, these "simple" problems often take the most frustration and time :-)

Nic;o)
Jul 12 '10 #6

Expert 100+
P: 1,240
Glad to help. I've spent my fair share of that kind of time and frustration too.

Jim
Jul 12 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.