With the report open in design view, click View >> Sorting and Grouping. If
you want to group by CustType (or whatever the field is called), select the
field, then in the bottom part of the dialog select Yes for Group Header and
Group Footer. In the Group Header put a text box bound to CustType. This
will be the section label. In the Detail section put an unbound text box.
Set its Control Source to =1, and its Running Sum property to Over Group. I
will call it txtCount; give it whatever name you like. Make it invisible,
if you like, or keep it visible to number the records.
In the group footer, add an unbound text box. Set its Control Source to
=[txtCount].
I will leave you to experiment with Sorting and Grouping. If you want to
add a date range criteria you can do that with Parameter prompts in the
query. There are a number of ways to add the date range to the header, if
you would like to do that. One way is to use an unbound form to specify the
date range. If the form is frmDateRange with text boxes txtFrom and txtTo,
the date criteria in the query could be:
BETWEEN Forms!frmDateRange!txtFrom AND Forms!frmDateRange!txtTo
Unbound text boxes in the report header could use the same text boxes as
their control source:
=Forms!frmDateRange!txtFrom
=Forms!frmDateRange!txtTo
You need to leave the form open if you do this. You can hide the form when
you open the report, then close it in the form's Close event.
I hope you are getting the general idea of how flexibly you can arrange data
on reports. Post again if you have specific questions.
DMWM said:
In addition to this i have a seperate question along th same lines. I am
trying to produce a report which show how many orders have been made by
each
category of customer.
In the customer form you can select via check box what type of customer
they
are either school, coporate, private, or retail. I need the report to say
how
many orders in a particular date range have been from each sector.
I hope someone can help
Thanks
DMWM said:
Hi Dave,
I'm not sure what you mean by group the report. I am using the wizard to
do
it at the moment. Is that the best way?
Thanks
:
The Group By query may not be getting what you are wanting. I suggest
you
take the grouping out of the query and use the sorting and grouping in
the
report. Then, rather than using a parameter query, do the filtering in
the
report. My favorite way to do that is to use the Where argument of the
OpenReport method to filter the dates.
--
Dave Hargis, Microsoft Access MVP
:
SELECT tblProducts.Product_ID, tblProducts.Product_Name,
tblProducts.Product_Description, tblProducts.Product_Size,
tblProducts.Product_Colour, Sum(tblProducts_Quoted.Quantity) AS
SumOfQuantity, tblProducts_Quoted.Actual_Price,
tblJob.Date_of_Enquiry
FROM (tblProducts INNER JOIN tblProducts_Quoted ON
tblProducts.Product_ID =
tblProducts_Quoted.Product_ID) INNER JOIN tblJob ON
tblProducts_Quoted.Products_Quoted_ID = tblJob.Products_Quoted_ID
GROUP BY tblProducts.Product_ID, tblProducts.Product_Name,
tblProducts.Product_Description, tblProducts.Product_Size,
tblProducts.Product_Colour, tblProducts_Quoted.Actual_Price,
tblJob.Date_of_Enquiry
HAVING (((tblJob.Date_of_Enquiry) Between [Enter Start Date] And
[Enter End
Date]));
Thankyou in advance
:
Post the SQL of your query so we can see what might be causing it.
--
Dave Hargis, Microsoft Access MVP
:
Hi,
This could be an interesting one!
I have a database that has tblCustomer, tblProducts,
tblProducts_Quoted,
tblJob
tblProducts_Quoted holds all info for products as per the job
(quantity,
price etc) and tblJob holds the date of the enquiry.
I'm trying to create a report that will show the quantity of
items sold in a
particular month/and or quarter (i feel it'll be the same code
just change
the way the date is entered)
I can get the query to group the products to show how many are
sold but when
i add the date into the query, the joining of the tables is
thrown off and no
data is displayed.
Anyone with any ideas? If you need more info or a better
explanation please
let me know.
Many Thanks
DMWM
