Need help with Count and Grouping

[Guest]

I'm in need of some help with a query and would appreciate any help I can
get. I'll try my best to explain the situation. I have a database that
tracks sewer overflows, in this database I have a table called "CSO Overflow
Visual Inspection" which has a few relationships to other tables, one that
has records of approx. 30 locations around the city which are designated with
a number (i.e 205). These points are checked regularly and through a form
the inspections are entered into the "CSO Overflow Visual Inspection" table.
Some of the fields are Date, Point Number, Overflowoccurred, and PrecipDate.
The "overflowoccured" field is a text field with a only Yes, No, or Other as
an option. The precipDate is a field I have a relationship looking up a
table with precip amount per day.

The query needs to include only the dates that the "overflowoccured" was
"YES" but I would like it to not repeat the date for each Point within the
same day, as there could be up to 30 entries of "YES" for each date. I would
like it to output all the point numbers in one record also or if this isn't
possible then at least the number of points that overflowed for the
particular date. Also, I would like to do this query within a given user date
range.

I can get the query to work and spit out each date with each point that had
a YES for overflow but it seperates this into many lines and isn't very
productive when reviewing the information.

So for example the query output would be similar to: (but of course in a
table format)

Date - 8-9-2007; Points that overflowed - 117, 119, 201, 202, 203; Precip -
1.25";
Date - 8-2-2007; Points that overflowed - 115, 119; Precip - .52";

Let me know if you have any further questions about this situation that I
can answer. Also, I may not be able to reply to any responses until
tomorrow. Thanks for any help.

 

[Guest]

This could be done in a query by grouping it on the date and calling a VBA
function which concatenates all the point numbers for that date into a text
string. I wouldn't do it that way, however, but via a report with a
subreport embedded in it. This will be easy to create and visually a lot
more pleasing and easier to read than the result set of a query in datasheet
view.

Firstly create a query which simply returns a distinct list of all dates and
precip values on dates on which an overflow occurred. BTW I'd suggest you
change the name of the Date column to something like InspectionDate as Date
is the name of a built in function, so could cause confusion. If you do use
Date as a column name be sure to enclose it in brackets [Date] in the
queries. I'll assume you do change it for the purpose of the examples below.
So the query for the main parent report would be along these lines:

SELECT DISTINCT InspectionDate, Precip
FROM [CSO Overflow Visual Inspection] INNER JOIN Precips
ON Precips.PrecipDate = [CSO Overflow Visual Inspection].InspectionDate
WHERE OverflowOccurred = "Yes";

I've assumed, possibly incorrectly, in the above that OverflowOccurred is a
text data type column as you've shown its value as a string "YES". A Boolean
(Yes/No) data type would be better in which case the WHERE clause would be:

WHERE OverflowOccurred = TRUE

The query for the subreport need to return a distinct list of all inspection
dates and points on which an overflow occurred so would be something like
this:

SELECT DISTINCT InspectionDate, [Point Number]
FROM [CSO Overflow Visual Inspection]
WHERE OverflowOccurred = "Yes";

Same caveat re data type of OverflowOccurred applies to above query.

Create a report based on the query above for use as the subreport and keep
the width of its detail section just wide enough to accommodate the point
number and just one line deep. Put a text box in the detail section bound to
the point number. Don't put a control in it bound to the InspectionDate as
this is merely there to link it to the parent report.

In the Sorting and Grouping dialogue sort the report on the Point Number
field.

From the File | Page Setup menu item in report design view select the
Columns tab in the dialogue enter as the Number of Columns however many point
numbers you want on each line in the final report, allowing for the column
spacing. You can always go back and change it if you don't get it quite
right at first. Check the Same as Detail check box for the column size.
Select Across then Down as the Column Layout. Save the report under a
suitable name; I usually prefix reports for use as a subreport with 'rptsub'
and main reports with 'rpt'.

Now design the main parent report based on the first query above. In the
detail section put text boxes bound to the InspectionDate and Precip fields.
In the Sorting and Grouping dialogue sort the main report by InspectionDate.
Add a subreport control with the report created above as its Source Object.
Set the LinkMasterFields and LinkChildFields properties of the subreport
control to InspectionDate. To reproduce the layout shown in your post the
detail section would be like so, with all controls on one line, the bracketed
items being the bound controls, the unbracketted ones labels:

Date - [InspectionDate] Points that overflowed - [The subreport control]
Precip – [Precip]

You can lay the controls out however you wish, however. Be sure to set the
CanGrow properties of both the detail section of the main report and of its
subreport control to True (Yes in the properties sheet) to allow for any
dates when the number of points might be such that they need to wrap onto
more than one line.

Although the above sounds quite long winded it probably takes less time to
implement than to describe, and I think you'll find the result a lot better
than the much cruder appearance of a query in datasheet view. You can also
add other things to the parent report, of course, such as a report and/or
page header and/or footer.

Ken Sheridan
Stafford, England