Help with Expressions combo boxes and queries 1

[henp]

Hi,

Please help me, I'm trying to build a report based on the following table

[CUST TYPE] [PRODUCT CODE][DATE][AMOUNT]
296 P/O 06/31/03 (14.95)
587 M 07/06/03 15.95
772 D/A 06/31/03 22.95
824 P/O 05/28/03 (18.95)
772 M 05/07/03 14.95

I can group the report by CUST TYPE and DATE, but need help using expressions.
I'm trying to do two things:

1) Total AMOUNT by PRODUCT CODE e.g M = $38.90
2) Show the percentage per PRODUCT CODE e.g P/O = 40%

Humvie was kind enough to supply me with the following, although I tried it and cannot get it to work. I tried adding a combo box to my form under [PRODUCT CODE] and using the query below but I could not get it to work. I entered the code exactly as it appears except for using MyTable Name. Can anybody tell me where I went wrong or how to do the above through expressions.

Thanks so much in advance of your help.

Tek-Tips Fan.

Kirtsen

Assuming you have a combo box on your form, this is how you would code your query.

DIM SqlName As String

If Not IsNull(Me![PRODUCT CODE]) Then
SqlName = SELECT SUM(AMOUNT) FROM YourTableName WHERE & " [PRODUCT CODE]= '"
& Me![PRODUCT CODE]& "'
End If

 

[KenReay]

Hi

If you want to have sub totals by Product GRoup, you need to group on product group in the report, see sorting and grouping in report design view

one you have the group set, in teh group (footer) section on the report just put a text box with source =Sum([Amount]) wheer [Amount] is the name of teh control holding the [Amount] column in the detail section of the report

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[henp]

Hi,

Can someone help me as I am very confused. I'm trying to come up with a simple expression in my report to calculate the total amount of different products. The products of which there are only five are all held in the same column - as are the totals. I've tried doing several different things but none work. Duane or anybody - Please help.

Thanks

Kirsten.

 

[dhookom]

KenReay suggested you sort/group by the field you want to total. Is there a reason why you didn't do this? You can then display the group footer and place a text box in footer section with a control source like:
=Sum([Amount])
If you are sorting by a different field(s) then you will absolutely have to create a totals query similar to your report recordsource. This query will group by [Product Code] and total the Amount. Then build a subreport based on this query and add it to your report.

Duane
MS Access MVP

 

[henp]

Thank you I finally got it..... although feel a little stupid.


Thanks for all your help.

Kirtsen.