SQL not working properly

[Kevin]

Hello,

I'm running the following query in my Acces DB:

select field_num,field2,field_name,SUM(sold_1_,sold_2,sold_3,sold_4) as
total_count from table1 INNER JOIN table2 ON table1.index=table2.foreign
WHERE [table1.update] >= '2007.11.1' AND [table1.update] <='2007.12.1' GROUP
BY field_num,field2,field_name

There are 8 records in the DB with the same field_num. So when I run this
query, I want to get 1 record with the total sum of all the sales counts.
Therefore I group by field_num. However, I still get 8 records. Can someone
tell me what is wrong with this query?

Thanks in advance,

Kevin

 

[Jeff Boyce]

Kevin

Did you copy the SQL and paste it into your newsgroup post? I ask because
there appears to be a typo in the spelling of "sold_1_" -- it doesn't look
like the other "sold_..." fieldnames.

And if you only want to group by [field_num], why does your GROUP BY clause
include 3 fields?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Kevin]

Hi Jeff,

Thanks for your response. I changed the field and table names before
posting. So the typo isn't present in the actual SQL. When I don't add the
other 3 fields in the GROUP BY, I get an SQL error saying that I need to add
non funtional fields to the group by.

Regards,

Kevin

Kevin

Did you copy the SQL and paste it into your newsgroup post? I ask because
there appears to be a typo in the spelling of "sold_1_" -- it doesn't look
like the other "sold_..." fieldnames.

And if you only want to group by [field_num], why does your GROUP BY
clause include 3 fields?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,

I'm running the following query in my Acces DB:

select field_num,field2,field_name,SUM(sold_1_,sold_2,sold_3,sold_4) as
total_count from table1 INNER JOIN table2 ON table1.index=table2.foreign
WHERE [table1.update] >= '2007.11.1' AND [table1.update] <='2007.12.1'
GROUP BY field_num,field2,field_name

There are 8 records in the DB with the same field_num. So when I run this
query, I want to get 1 record with the total sum of all the sales counts.
Therefore I group by field_num. However, I still get 8 records. Can
someone tell me what is wrong with this query?

Thanks in advance,

Kevin

 

[Dale Fye]

You cannot SUM(Sold_1, Sold_2, Sold_3, Sold_4). You must use syntax similiar
to:

SUM(NZ(Sold_1) + NZ(Sold_2) + NZ(Sold_3) + NZ(Sold_4))

Notice that I have wrapped each of your Sold_# fields in the NZ( ) function.
This is to avoid NULL addition (anything added to a NULL value is NULL).

What is the relationship between the values in the three fields
([Field_Num], [Field2], and [Field_Name])? When you Group by these three
fields, you will get 1 record for each of the possible combinations of these
values. If you don't need [Field2] or [Field_Name] remove both of them from
the SELECT and Group By clauses.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hi Jeff,

Thanks for your response. I changed the field and table names before
posting. So the typo isn't present in the actual SQL. When I don't add the
other 3 fields in the GROUP BY, I get an SQL error saying that I need to add
non funtional fields to the group by.

Regards,

Kevin

Kevin

Did you copy the SQL and paste it into your newsgroup post? I ask because
there appears to be a typo in the spelling of "sold_1_" -- it doesn't look
like the other "sold_..." fieldnames.

And if you only want to group by [field_num], why does your GROUP BY
clause include 3 fields?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,

I'm running the following query in my Acces DB:

select field_num,field2,field_name,SUM(sold_1_,sold_2,sold_3,sold_4) as
total_count from table1 INNER JOIN table2 ON table1.index=table2.foreign
WHERE [table1.update] >= '2007.11.1' AND [table1.update] <='2007.12.1'
GROUP BY field_num,field2,field_name

There are 8 records in the DB with the same field_num. So when I run this
query, I want to get 1 record with the total sum of all the sales counts.
Therefore I group by field_num. However, I still get 8 records. Can
someone tell me what is wrong with this query?

Thanks in advance,

Kevin

 

[Ken Sheridan]

Kevin:

You can only sum the values of the Sold columns for each unique combination
of the values in the columns in the GROUP BY clause, so unless the columns
field2 and field_name have the same values for each value of field_num you
are going to get separate summations for each set of unique values, which is
probably why you are getting 8 rows returned rather than 1. You should
remove the other two columns from the query if you want to sum for each value
of field_num.

More fundamentally, I suspect that your table might not be properly
normalized as you have multiple Sold columns. This is what's known as
'encoding data as column headings'. Data should only be stored as values at
column positions in rows in tables however. The solution is to decompose the
table so that there is a single Sold column in a second table which also
includes a foreign key column(s) referencing the primary key column(s) of the
current table. To sum the Sold column you'd then join the tables on the
keys, group by the field_num column and sum the single Sold column in the new
table.

Ken Sheridan
Stafford, England

Hi Jeff,

Thanks for your response. I changed the field and table names before
posting. So the typo isn't present in the actual SQL. When I don't add the
other 3 fields in the GROUP BY, I get an SQL error saying that I need to add
non funtional fields to the group by.

Regards,

Kevin

Kevin

Did you copy the SQL and paste it into your newsgroup post? I ask because
there appears to be a typo in the spelling of "sold_1_" -- it doesn't look
like the other "sold_..." fieldnames.

And if you only want to group by [field_num], why does your GROUP BY
clause include 3 fields?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,

I'm running the following query in my Acces DB:

select field_num,field2,field_name,SUM(sold_1_,sold_2,sold_3,sold_4) as
total_count from table1 INNER JOIN table2 ON table1.index=table2.foreign
WHERE [table1.update] >= '2007.11.1' AND [table1.update] <='2007.12.1'
GROUP BY field_num,field2,field_name

There are 8 records in the DB with the same field_num. So when I run this
query, I want to get 1 record with the total sum of all the sales counts.
Therefore I group by field_num. However, I still get 8 records. Can
someone tell me what is wrong with this query?

Thanks in advance,

Kevin