Grouping Query details problem

[Dan2kx]

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

SELECT tblReagent.RCat2, tblReagent.RDesc, Sum(tblBatch.BQuantity) AS BQuan, tblSiteRD.RULimit, IIf([RULIMIT]-[BQuantity]<0,0,[RULIMIT]-[BQuantity]) AS Diff
FROM (tblReagent LEFT JOIN tblBatch ON tblReagent.RLID = tblBatch.BRID) RIGHT JOIN tblSiteRD ON tblReagent.RLID = tblSiteRD.RID
GROUP BY tblReagent.RCat2, tblReagent.RDesc, tblSiteRD.RULimit, IIf([RULIMIT]-[BQuantity]<0,0,[RULIMIT]-[BQuantity]), tblSiteRD.RDept
HAVING (((tblSiteRD.RDept)=1));

Thanks guys

Dan

[jimatqsi]

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

GROUP BY tblReagent.RCat2

and in test 2

GROUP BY tblReagent.RDesc

and in test 3

GROUP BY tblSiteRD.RULimit

and in test 4

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

[nico5038]

I concur with Jim, as far as I see you need to use in the select a Sum() for the Diff variable like:

 SELECT ... , SUM(IIf([RULIMIT]-[BQuantity]<0,0,[RULIMIT]-[BQuantity]))  AS Diff  ...

and remove the IIF from the grouping.

Nic;o)

[Dan2kx]

You are right, it is the calculation...

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

Cheers

[Dan2kx]

I concur with Jim, as far as I see you need to use in the select a Sum() for the Diff variable like:

 SELECT ... , SUM(IIf([RULIMIT]-[BQuantity]<0,0,[RULIMIT]-[BQuantity]))  AS Diff  ...

and remove the IIF from the grouping.

Nic;o)

Doh, always the simple stuff huh,

Thank you both you have saved me hours of misspent frustration...

[nico5038]

Glad we could help, these "simple" problems often take the most frustration and time :-)

Nic;o)

[jimatqsi]

Glad to help. I've spent my fair share of that kind of time and frustration too.

Jim