Problem creating a query with calculated value

R

rich

hello,

I am trying to write a Select query that computes a value. My Sales table
has 5 columns, IDNumber, Q1Sales - Q4Sales. I want the query to compute a
total (Q1Sales + Q2Sales.....). Right now, my query is:

SELECT tblSales.Q1Sales, tblSales.Q2Sales, tblSales.Q3Sales,
tblSales.Q4sales, [Total] AS [Total Sales]
FROM tblSales
WHERE ((([Total])="q1sales"+"q2sales"+"q3sales"+"q4sales"));

When I run the query, I am prompted for "Total". Can anyone tell me why
this is happening?

Thanks,
Rich
 
R

rich

I figured it out. I had the formula to compute the total in the Criteria box
rather than the Field box.

thanks,
Rich
 
G

George Nicholson

SELECT tblSales.Q1Sales, tblSales.Q2Sales, tblSales.Q3Sales,
tblSales.Q4sales, [q1sales]+[q2sales]+[q3sales]+[q4sales] AS [Total Sales]
FROM tblSales

"WHERE" clause is used to specify a criteria, i.e., which records to select:
"WHERE Q1Sales > 1000" for example. WHERE is the wrong place for
calculations.
When I run the query, I am prompted for "Total".

Because you were telling it to SELECT a field called Total, and the field
doesn't exist.

HTH,


rich said:
hello,

I am trying to write a Select query that computes a value. My Sales table
has 5 columns, IDNumber, Q1Sales - Q4Sales. I want the query to compute a
total (Q1Sales + Q2Sales.....). Right now, my query is:

SELECT tblSales.Q1Sales, tblSales.Q2Sales, tblSales.Q3Sales,
tblSales.Q4sales, [Total] AS [Total Sales]
FROM tblSales
WHERE ((([Total])="q1sales"+"q2sales"+"q3sales"+"q4sales"));

When I run the query, I am prompted for "Total". Can anyone tell me why
this is happening?

Thanks,
Rich
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top