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
6 1401
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
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)
You are right, it is the calculation...
any way around that? Other than creating a second query!?
Cheers
@nico5038
Doh, always the simple stuff huh,
Thank you both you have saved me hours of misspent frustration...
Glad we could help, these "simple" problems often take the most frustration and time :-)
Nic;o)
Glad to help. I've spent my fair share of that kind of time and frustration too.
Jim
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Joel Thornton |
last post by:
I'm having much difficulty figuring out how to write the following
query. Please help!
I have this table:
Event
EventId int Primary Key
PatientId int
SeverityLevel int
|
by: John |
last post by:
I have a query that returns data such as:
(First row is field name, following are records)
strClass, strLastName, lngMonth, lngGrade
Biology, Douglas, 1, 90
Biology, Douglas, 2, 80
Biology,...
|
by: George |
last post by:
I am trying to check whether a query string is being passed to my .aspx page, like so:
If Not Request.QueryString Is Nothing Then
If CInt(Request.QueryString.GetValues("values")(0)) <> 1 Then...
|
by: Frank |
last post by:
I have a table with the following structure:
main_category| category| sub_category| answer|date
Basically, the data will be along these lines:
Neuro | LOC | Status | answer1|date
Neuro |...
|
by: worldspy |
last post by:
Hi guys,
I don't know whether this is feasible or not. But here is my problem
I have a session variable which has database field names in a particular order and this variable is generated...
|
by: glamster7 |
last post by:
Ok folks its Friday & I'm feeling a bit thick (also not very well).
I have a form Salonmanagerdetail wich allows the user to enter the following details Stylist_Id,Stylist_Name,Group_Name &...
|
by: gazsharpe101 |
last post by:
Hi, I have a problem which should have a relatively simple solution, I just cannot find it.
I have a select query which gives me the following results:
Company_Name Staff_FirstName ...
|
by: gazsharpe101 |
last post by:
Hi everyone,
I am creating a query that shows a list of all invoices raised in the last month which shows the date on which they were raised along with the amounts. Each invoices has it's own ID,...
|
by: Leira |
last post by:
Hi,
I have a problem with grouping. My source XML has <record> elements that have a @name and a @group attribute. It looks something like this:
<root>
<result>
<record name="test1"...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |