Parameter Query - Wildcard?

Question

I have a parameter query which, simply put, asks for a salesman code.  So say there are 3 salesmen, I have to produce a report for each salesman - i.e. a total of 3 reports.  What happens if I want the same report for all salesmen grouped together - is there a wildcard character I could use when prompted for a salesman code that would bring all salesmen data up?

Answer 1

You could change the Criteria for the salesman field to

Like [Enter Salesman Code]

(substituting your own text for the parameter prompt) and enter * if you want to include all salesmen.

Or change the Criteria to

[Enter Salesman Code] Or [Enter Salesman Code] Is Null

(again substituting your own text), and leave the prompt empty if you want to include all salesmen.

Answer 2

Is kcode the product group field? And has the report been grouped on kcode?

Answer 3

In the design view window under product group it is Group By Ascending.  Which I think is correct - I want to group my data by Product Group and have it in ascending order.  SQL code is as follows and is based on a union query which is STQ1 where the salesman parameter is set.

SELECT PGT.kcode, PGT.descrip, Round(Sum([InvoicedTonnesThisYear]-[CreditedTonnesThisYear]),3) AS [Current Tonnes], Round(Sum([InvoicedTonnesLastYear]-[CreditedTonnesLastYear]),3) AS [Previous Tonnes], Round([Current Tonnes]-[Previous Tonnes],3) AS [Tonnes Chg], IIf([Previous Tonnes]=0 And [Current Tonnes]>0,"100.00%",IIf([Tonnes Chg]=0,"No Tonnes",Format([Tonnes Chg]/[Previous Tonnes],"Percent"))) AS [Tonnes % Chg], Round(Sum([InvoicedUnitsThisYear]-[CreditedUnitsThisYear]),0) AS [Current Units], Round(Sum([InvoicedUnitsLastYear]-[CreditedUnitsLastYear]),0) AS [Previous Units], [Current Units]-[Previous Units] AS [Units Chg], IIf([Previous Units]=0 And [Current Units]>0,"100.00%",IIf([Units Chg]=0,"No Units",Format([Units Chg]/[Previous Units],"Percent"))) AS [Units % Chg], FormatCurrency(Sum([InvoicedValueThisYear]-[CreditedValueThisYear]),0) AS [Current Value], FormatCurrency(Sum([InvoicedValueLastYear]-[CreditedValueLastYear]),0) AS [Previous Value], FormatCurrency([Current Value]-[Previous Value],0) AS [Value Chg], IIf([Previous Value]=0 And [Current Value]>0,"100.00%",Format([Value Chg]/[Previous Value],"Percent")) AS [Value % Chg], STQ1.name

FROM STQ1 INNER JOIN PGT ON STQ1.pgrp = PGT.kcode

GROUP BY PGT.kcode, PGT.descrip, STQ1.name

ORDER BY PGT.kcode;

Answer 4

Open the report in design view, and check the Group, Sort and Total pane.

Answer 5

OK, so my report which looked like this:-

Rep 1 Sales

Product 1

Product 2

Product 3

Total

now looks like this:-

Sales

Product 1

Product 1

Product 1

Product 2

Product 2

Product 2

Product 3

Product 3

Product 3

Total

Why is it not grouping by product?