SUM NOT INCLUDING ALL RECORDS

  • Thread starter Thread starter Emma Aumack
  • Start date Start date
E

Emma Aumack

I am having trouble with a query that is supposed to sum all sales. For some
reason it is excluding some records, like when I have a sale for the same
product by the same customer and in the same quantity. I want to sum all the
sales for each customer as one entry (combining all sales for that customer).
How do I get it to include all of those sales? All of the columns are group
by with only the sales column and the quantity column being summed.
 
Post the SQL of your query. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.
 
I should probably explain a little better. When I total the Table, I get a
different total. In researching why, it looks like Access is deleting some
records where the data is identical. I need it to sum all the data for each
account even if there are identical records. I even tried to include a
unique identifier thinking that would help, it didn't so I removed it. Here
is the SQL:

SELECT GB240_Data.OFACT AS Account, Tbl_HIN.HIN_ID AS MICRO,
GB240_Data.OFANME AS Name, GB240_Data.OFADD1 AS Address, GB240_Data.OFCITY AS
City, GB240_Data.OFSTATE AS State, GB240_Data.OFZIP AS Zip,
Sum(GB240_Data.[OFCSLS]) AS SumOfSales, GB240_Data.OFGRP AS [Group],
Sum(GB240_Data.[QUANTITY SOLD]) AS SumOfQty
FROM GB240_Data INNER JOIN Tbl_HIN ON GB240_Data.OFACT = Tbl_HIN.ACCT_NO
GROUP BY GB240_Data.OFACT, Tbl_HIN.HIN_ID, GB240_Data.OFANME,
GB240_Data.OFADD1, GB240_Data.OFCITY, GB240_Data.OFSTATE, GB240_Data.OFZIP,
GB240_Data.OFGRP;
 
Try removing --
Tbl_HIN.HIN_ID AS MICRO,
INNER JOIN Tbl_HIN ON GB240_Data.OFACT = Tbl_HIN.ACCT_NO
& Tbl_HIN.HIN_ID,

and then run the query. Maybe all the OFACT do not match ACCT_NO in
all cases.

--
Build a little, test a little.


Emma Aumack said:
I should probably explain a little better. When I total the Table, I get a
different total. In researching why, it looks like Access is deleting some
records where the data is identical. I need it to sum all the data for each
account even if there are identical records. I even tried to include a
unique identifier thinking that would help, it didn't so I removed it. Here
is the SQL:

SELECT GB240_Data.OFACT AS Account, Tbl_HIN.HIN_ID AS MICRO,
GB240_Data.OFANME AS Name, GB240_Data.OFADD1 AS Address, GB240_Data.OFCITY AS
City, GB240_Data.OFSTATE AS State, GB240_Data.OFZIP AS Zip,
Sum(GB240_Data.[OFCSLS]) AS SumOfSales, GB240_Data.OFGRP AS [Group],
Sum(GB240_Data.[QUANTITY SOLD]) AS SumOfQty
FROM GB240_Data INNER JOIN Tbl_HIN ON GB240_Data.OFACT = Tbl_HIN.ACCT_NO
GROUP BY GB240_Data.OFACT, Tbl_HIN.HIN_ID, GB240_Data.OFANME,
GB240_Data.OFADD1, GB240_Data.OFCITY, GB240_Data.OFSTATE, GB240_Data.OFZIP,
GB240_Data.OFGRP;

--
www.bardpv.com
Tempe, Arizona


KARL DEWEY said:
Post the SQL of your query. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.
Click to expand...
Click to expand...
 
Duh, that was it. But I need to include those records so I just had to
change the join type. Thank you!
--
www.bardpv.com
Tempe, Arizona


KARL DEWEY said:
Try removing --
Tbl_HIN.HIN_ID AS MICRO,
INNER JOIN Tbl_HIN ON GB240_Data.OFACT = Tbl_HIN.ACCT_NO
& Tbl_HIN.HIN_ID,

and then run the query. Maybe all the OFACT do not match ACCT_NO in
all cases.

--
Build a little, test a little.


Emma Aumack said:
I should probably explain a little better. When I total the Table, I get a
different total. In researching why, it looks like Access is deleting some
records where the data is identical. I need it to sum all the data for each
account even if there are identical records. I even tried to include a
unique identifier thinking that would help, it didn't so I removed it. Here
is the SQL:

SELECT GB240_Data.OFACT AS Account, Tbl_HIN.HIN_ID AS MICRO,
GB240_Data.OFANME AS Name, GB240_Data.OFADD1 AS Address, GB240_Data.OFCITY AS
City, GB240_Data.OFSTATE AS State, GB240_Data.OFZIP AS Zip,
Sum(GB240_Data.[OFCSLS]) AS SumOfSales, GB240_Data.OFGRP AS [Group],
Sum(GB240_Data.[QUANTITY SOLD]) AS SumOfQty
FROM GB240_Data INNER JOIN Tbl_HIN ON GB240_Data.OFACT = Tbl_HIN.ACCT_NO
GROUP BY GB240_Data.OFACT, Tbl_HIN.HIN_ID, GB240_Data.OFANME,
GB240_Data.OFADD1, GB240_Data.OFCITY, GB240_Data.OFSTATE, GB240_Data.OFZIP,
GB240_Data.OFGRP;

--
www.bardpv.com
Tempe, Arizona


KARL DEWEY said:
Post the SQL of your query. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.
--
Build a little, test a little.


:

I am having trouble with a query that is supposed to sum all sales. For some
reason it is excluding some records, like when I have a sale for the same
product by the same customer and in the same quantity. I want to sum all the
sales for each customer as one entry (combining all sales for that customer).
How do I get it to include all of those sales? All of the columns are group
by with only the sales column and the quantity column being summed.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top