Help with Reports

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

Thanks,

Kirsten.

 

[humvie]

Give this a try

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

It's the same idea with your average. Reserved word for average is AVG.

 

[henp]

Thanks for your help and i will try this, although I'm not sure what a combo box is - I do have a form though.

Is there no way of using an expression in a report to do this or is the above the expression i would use on the report. If not do I creat a query and use that in my report.

I know I'm an amateur and am asking alot.

Thanks

Kirsten.

 

[humvie]

It all depends how and what you're goal is with respect to reports and queries.

I once read that close to 80% of what people read DO NOT need to print their report. Therefore the majority of my applications are designed for query and read with the ability to copy the results and dump it into excel spreadsheet.

To answer your question, yes you can insert the SQL code into a field on the report. But you'll have to have a field for every type of PRODUCT CODE. Not a good idea if you have 10,000 sku's. (which is why you should have a form that will find the information first THEN you decide to print or not.

Take a field from your report, right click and chose properties. Click on ALL tab an then add the following code in the Control Source property: SELECT SUM(AMOUNT) FROM YourTableName WHERE [PRODUCT CODE]= "VEHICLE"