SUM NOT INCLUDING ALL RECORDS

[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.

 

[KARL DEWEY]

Post the SQL of your query. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.

 

[Emma Aumack]

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;

 

[KARL DEWEY]

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.

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

Post the SQL of your query. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.

 

[Emma Aumack]

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

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.

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

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.