query won't Sum correctly

funseeker

New member
Local time
Today, 05:21
Joined
Nov 5, 2010
Messages
8
Hi
In Access 2003 using a query to group by and sum on 2 fields [BUY SIDE QUANTITY] , and a seperate field to sum on [SELL SIDE QUANTITY]

Am importing data from 2 sources and trying to sum within access

I have the following: see attached

The Fileds are data type "Long Integer" in the table
I should get one line in the output
I'm getting two lines...

:(
I'm confused...
 

Attachments

  • group_by_sum_not_working.JPG
    group_by_sum_not_working.JPG
    20.4 KB · Views: 210
If you Groupby the 1st 3 fields and Sum the Buy and Sell it should work

Brian
 
SQL of query:
SELECT tblPhilipsPos.[OFFICE NUMBER],
tblPhilipsPos.[ACCOUNT NUMBER],
tblPhilipsPos.[EXCHANGE CODE],
tblPhilipsPos.[FUTURES CODE],
tblPhilipsPos.[CONTRACT YR/MON YYYYMM],
tblPhilipsPos.[SECURITY SUB-TYPE],
tblPhilipsPos.[STRIKE PRICE (IIIIIIIDDDDDDDDD)],
tblPhilipsPos.[SECURITY DESCRIPTION LINE 1],
Sum(tblPhilipsPos.[BUY SIDE QUANTITY]) AS [SumOfBUY SIDE QUANTITY],
Sum(tblPhilipsPos.[SELL SIDE QUANTITY]) AS [SumOfSELL SIDE QUANTITY]
FROM tblPhilipsPos
GROUP BY tblPhilipsPos.[OFFICE NUMBER], tblPhilipsPos.[ACCOUNT NUMBER],
tblPhilipsPos.[EXCHANGE CODE], tblPhilipsPos.[FUTURES CODE],
tblPhilipsPos.[CONTRACT YR/MON YYYYMM],
tblPhilipsPos.[SECURITY SUB-TYPE],
tblPhilipsPos.[STRIKE PRICE (IIIIIIIDDDDDDDDD)],
tblPhilipsPos.[SECURITY DESCRIPTION LINE 1]
HAVING (((tblPhilipsPos.[ACCOUNT NUMBER])<>'')
AND ((tblPhilipsPos.[EXCHANGE CODE])="02")
AND ((tblPhilipsPos.[FUTURES CODE])="48")
AND ((tblPhilipsPos.[CONTRACT YR/MON YYYYMM])="201108"))
ORDER BY tblPhilipsPos.[OFFICE NUMBER], tblPhilipsPos.[ACCOUNT NUMBER],
tblPhilipsPos.[EXCHANGE CODE], tblPhilipsPos.[FUTURES CODE],
tblPhilipsPos.[CONTRACT YR/MON YYYYMM]
 
Hi Brian

that didn't work
not sure why...
Am grouping on 7 fields (although I did reduce to three for this test)... and then summing on the last two...
still getting two lines .... should only be one
:(
 
Are the Office numbers the same?
All fields you're not displaying... are they also the same?
The group by will work on all fields even if your not displaying them
 
When you asked me are the office numbers the same?
I'm not sure Im understanding your question...
The Office number field is a text field with numeric characters
In this query the office number is the same...
The account numbers have differences and are also text numeric characters
 
When you group by multiple fields.. any fields for any records need to be the same as the other records for that field.. you essentially are saying.
"Show me 1 record where field X, Y and Z all have the same values." - in their respective fields, not x = y = z

You have fields which you are not displaying, but because they are part of the query, they are included in thr group by.
if the Account numbers are different, you will get 2 lines on your query, 1 for each account number.
 
This is where I really get confused.... But I'll try to explain

I've removed the additional uneeded fields by taking the data from the query and created a seperate table using only the required fileds...
I then try to Group by on the seperate table and I'm still getting two lines

oh ... and yes... this is by account so I do want a seperate sum based on account...
However,...
this query is only for the one account and still shows two lines instead of one
 
This is where I really get confused.... But I'll try to explain

I've removed the additional uneeded fields by taking the data from the query and created a seperate table using only the required fileds...
I then try to Group by on the seperate table and I'm still getting two lines

oh ... and yes... this is by account so I do want a seperate sum based on account...
However,...
this query is only for the one account and still shows two lines instead of one

Is this a new query that you have made since your original post? or are you simply outlining the process you have taken thus far?

If this is a new query, could you take another image of the results as well as post the design??

If this is just an outline to your process and we are still talking about the same query, then you may want to try making all fields that you referenc ein your query visible and then posting an image of that.
 
outlining the process I've taken thus far...

not sure what else I can do...
:confused:
 
Hi Brian

(although I did reduce to three for this test:(

Actually it is 4 I'd try taking each 1 out of the query until you find which is causing the problem, it could be a simple data entry problem with somebody stuttering on the space bar as all fields are text.

If that fails I think that we need the DB

Brian
 
ok, so make ALL the fields visible, in your image at the top, you can see that there are only 6 fields visible.
However in your query you actually refer to 10 different fields.
Once you display all the fields you will be able to see the difference between your first and second record. if you want 1 record, you need to remove the fields that have different values.
 
now I'm just going on randum data mining hunts on this issue...
this really has me miffed.... but I'm not going to let this lick me....

am now removing one of the fields in the query
and re-running (am doing this to all the fields in the query)

HEY.... I found something ( i think ?)

one of the fields that am using in the query is called [SECURITY_SUB-TYPE] ... when I removed it.... it works...

NULL ? not sure

I need that field as one of the group by

suggestions ?
 
GOT IT !!!

YAHOO!!!!.... this was a wopper....

Oh Sorry... Here's my fix to the query from "hell"

PUT_CALL: IIf([SECURITY SUB-TYPE] Is Null,'',IIf([SECURITY SUB-TYPE]='','',[SECURITY SUB-TYPE]))

That worked...
The [SECURITY SUB-TYPE] field has (so I thought) 3 distinct postings
1. C
2. P
3. ' ' (Blank)

Now I know that I'll have to look for a 4th one...
4. NULL

You GUY's ROCK!
You made me think it through... very frustrating... but gratifying
 

Users who are viewing this thread

Back
Top Bottom