Query so I can Sort in Reports by Calculated field 1

[ianbrown76]

Hi,

I need to sort a report on a calculated field. I have read that I should put the calculation in the underlying query.

When I put the same code in the SQL I get a message saying that the field cannot be found yet it works within the report.

I'd like to find the sum of items purchased in the 'electrical sales' table where the 'product number' is the same as that in 'electical items'. Here is my coding:-

Dsum ("[quantity purchased]","electrical sales","[product number] =[electrical items].[product number]&quot as qtysold

If anybody can help or offer any alternaltive solutions I'd most appreciate it.

Ian

 

[jebry]

Hi!

Try it like this:

Select [electrical items].[product number], Sum([quantity sold]) as qtysold From [electrical items] Inner Join [electrical sales] On [electrical items].[product number] = [electrical sales].[product number] Group By [electrical items].[product number]

This should produce a query with each product number and the amount sold for that product. You can then use this as a table in the query to base your report on and you should be able to sort on the quantity sold.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[ianbrown76]

Thanks very much, it did the trick.

 

[jebry]

Hi Ian!

Your welcome! Glad to help.

Jeff Bridgham
bridgham@purdue.edu