If you are only including data from the Shipments table in the query's result
table you can use the SELECT DISTINCT option, e.g.
SELECT DISTINCT jobref, weight
FROM Shipments INNER JOIN INVOICES
ON Shipments.jobref = Invoices.jobref
WHERE YEAR([billing date]) = [Enter year:]
AND MONTH([billing date]) = [Enter month as a number:];
The above would prompt for the year and month when the query, or any form or
report based on it, is opened.
If you also want to include data from Invoices, however, you can't show the
jobref and weight per jobref only once in the query's result table per se,
but you can do it in a report based on the query. A query in this case might
be something like this:
SELECT jobref, weight, [billing date], [billed amount]
FROM Shipments INNER JOIN INVOICES
ON Shipments.jobref = Invoices.jobref
WHERE YEAR([billing date]) = [Enter year:]
AND MONTH([billing date]) = [Enter month as a number:];
Base a report on this query and group it first on on jobref, then on billing
date giving the jobref group a Group Header. Put the controls bound to
jobref and weight in the group header, and those bound to billing date and
billed amount (or whatever) in the detail section. The report will show the
jobref and weight just once, with multiple lines for billing date and billed
amount per job ref.
If in the report you want the job ref and weight on the same line as the
first billing date and billed amount for the jobref then position the job ref
and weight controls to the left of the header section, and the billing date
and billed amount further to the right in the detailed section so there is no
overlap. In the group header's Format event procedure enter:
MoveLayout = False
If you want to aggregate data, e.g. sum the weights in the report footer
then with a report based on the first query you'd just put a text box,
txtTotalWeight say, in the report footer, with a ControlSource property of:
=Sum([weight])
You can't do this with a report based on the second query, however, as it
would sum the (unseen) multiple weight values, not just the seen values.
There are various ways around this. One is to leave the ControlSource of the
txtTotalWeight text box blank and populate it in code like so:
In the report header's Print event procedure put:
' initialize txtTotalWeight control to zero
txtTotalWeight = 0
In the Print event procedure of the group header put:
' increment txtTotalWeight, examining
' the PrintCount property to avoid any
' inadvertent double counting
If PrintCount = 1 Then
txtTotalWeight = txtTotalWeight + weight
End If
One thing to be aware of when using the Print event procedure, however, is
that if in print preview you skip over pages with the navigation buttons the
Print events for the skipped pages won't fire, so the sum will be wrong.
Paging down through all pages in print preview or sending the complete report
to a printer will be fine, though.
Ken Sheridan
Stafford, England
CW said:
I need to analyse and report on our shipments by weight on a monthly basis,
in the month within which they are billed. Trouble is, we often raise more
than one bill per shipment, but I only want to report the weight of each
shipment once.
The weight is held in a Shipments table along with the unique jobref (and
other data).
The billing date is held in the Invoices table along with the jobref and
other data.
There's a one-to-many relationship from Shipments.jobref to Invoices.jobref.
If I do a query looking for the weights of all shipments that had an invoice
raised in a month, where a shipment has had 2 or 3 invoices raised of course
I get the weight 2 or 3 times too.
How can I exclude repeating values, or somehow ensure that I pull the weight
for a particular jobref only once?
Many thanks
CW
