Customer Service Report - 2 service dates

[northstar]

I need to create a customer report displaying two annual service dates as
well as type of service performed and other measurements. I have no
difficulty in creating the report for a single date, but not for two dates.
What is the best method? Do I create 2 queries, one max the other min for the
calendar year and use both in the report? Of is there a better way? Any help
will be greatly appreciated. Jim

 

[MGFoster]

I need to create a customer report displaying two annual service dates as
well as type of service performed and other measurements. I have no
difficulty in creating the report for a single date, but not for two dates.
What is the best method? Do I create 2 queries, one max the other min for the
calendar year and use both in the report? Of is there a better way? Any help
will be greatly appreciated. Jim

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Depends on your query, which you haven't shown us. It helps to post the
query you are working on so we can offer suggestions. That said....

I'll guess you are using a date range for the year data. All you have
to do is extend that date range to include the second date range. E.g.:

WHERE date_column BETWEEN first_date_begin And second_date_end

Then, in you SUM() functions use an IIf() function to filter out the
appropriate date range. E.g.:

SUM(IIf(date_column BETWEEN first_date_begin And first_date_end,
amount_column,NULL)) As FirstPeriod,

SUM(IIf(date_column BETWEEN second_date_begin And second_date_end,
amount_column,NULL)) As SecondPeriod,

The above SUM() function only sume the amount_column if the date_column
is in the specified date range.

But, that's all surmise without seeing your query!

:-0
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSeO2roechKqOuFEgEQIcjQCgxcLomkW4Z4I+5xm+Gv2fHCFxXPkAoK8G
3ONZZoczJkAPZooChX1C6TDy
=7/o/
-----END PGP SIGNATURE-----

 

[northstar]

MGFoster, thanks for your reply. I regret the delayed response. I was
unexpectedly out of the office for a couple of days. I offer a bit more
explanation and a sample of the SQL statement. Roughly 100 customers
receiving service for FY2008 (Jan-Dec). Some had one service call, some two,
and some three. My report must identify service date and service rendered,
e.g. assume I am a customer and had three service calls. I want a single
report listing the three, not three reports (one for each call). So far my
only action is to apply the date range to the data. Following is the SQL
statement:
SELECT tblSystemServiceRecord.ServiceID, tblSystemServiceRecord.[customer
id], tblSystemServiceRecord.SystemBrand, tblSystemServiceRecord.ServiceDate,
tblSystemServiceRecord.StartTime, tblSystemServiceRecord.FinishTime,
tblSystemServiceRecord.TechID, tblSystemServiceRecord.[MLSS%],
tblSystemServiceRecord.[Aerator amps], tblSystemServiceRecord.[Pump1 amps],
tblSystemServiceRecord.[Pump2 amps], tblSystemServiceRecord.Fecals,
tblSystemServiceRecord.pH, tblSystemServiceRecord.[Dissolved Oxygen],
tblSystemServiceRecord.[Event Counter], tblSystemServiceRecord.[Water Meter],
tblSystemServiceRecord.[Advantex filter pump elapsed time],
tblSystemServiceRecord.[Advantex filter pump high level events],
tblSystemServiceRecord.[Advantex filter pump event counter],
tblSystemServiceRecord.[Advantex drainfield pump elapsed time],
tblSystemServiceRecord.[Advantax drainfield filter pump high level events],
tblSystemServiceRecord.[Advantax drainfield filter pump event counter],
tblSystemServiceRecord.[MediaFilter pump event counter],
tblSystemServiceRecord.[MediaFilter pump elapsed time],
tblSystemServiceRecord.[MediaFilter pump high level events],
tblSystemServiceRecord.[Drainfield pump elapsed time],
tblSystemServiceRecord.[MediaFilter drainfield filter pump high level
events], tblSystemServiceRecord.[MediaFilter drainfield filter pump event
counter], tblSystemServiceRecord.[Mixed Liquor Sample],
tblSystemServiceRecord.[Check Alarm System], tblSystemServiceRecord.[Turn
Power Off], tblSystemServiceRecord.[Rinse/clean surge bowl],
tblSystemServiceRecord.[Inspect effluent quality],
tblSystemServiceRecord.[Vacuum Weir/filters], tblSystemServiceRecord.[Clean
filters], tblSystemServiceRecord.[Replace filters],
tblSystemServiceRecord.[Inspect gaskets], tblSystemServiceRecord.[Inspect
alarm sensor], tblSystemServiceRecord.[Inspect aerator],
tblSystemServiceRecord.[Rinse/clean weir plate],
tblSystemServiceRecord.[Clean air diffuser], tblSystemServiceRecord.[Vacuum
weir], tblSystemServiceRecord.[Inspect air lines],
tblSystemServiceRecord.[Open peat module lid],
tblSystemServiceRecord.[Inspect peat/ponding], tblSystemServiceRecord.[insect
or pest infiltration], tblSystemServiceRecord.[Inspect lateral system],
tblSystemServiceRecord.[Clean effluent filter], tblSystemServiceRecord.[Check
timer settings], tblSystemServiceRecord.[Even effluent distribution],
tblSystemServiceRecord.[Open inspection port],
tblSystemServiceRecord.[Replace bacterial stick],
tblSystemServiceRecord.[Rinse pirana unit], tblSystemServiceRecord.[Open
module lid], tblSystemServiceRecord.[Inspect ponding],
tblSystemServiceRecord.InsectOrPestInfiltation,
tblSystemServiceRecord.InspectLateralSystem, tblSystemServiceRecord.[Inspect
clean air vent], tblSystemServiceRecord.CleanEffluentFilter,
tblSystemServiceRecord.EvenEffluentDistribution,
tblSystemServiceRecord.[Clean bio tube],
tblSystemServiceRecord.CheckTimerSettings, tblSystemServiceRecord.[Inspect
splitter valve], tblSystemServiceRecord.[Rinse/clean plate unit],
tblSystemServiceRecord.[Bleach air tube], tblSystemServiceRecord.[Check unit
for wear], tblSystemServiceRecord.[Clean enclosure],
tblSystemServiceRecord.[Rinse/clean diffuser],
tblSystemServiceRecord.[Inspect air piping],
tblSystemServiceRecord.CheckUnitForWear,
tblSystemServiceRecord.CleanEnclosure, tblSystemServiceRecord.[single pass],
tblSystemServiceRecord.recirculating, tblSystemServiceRecord.sand,
tblSystemServiceRecord.gravel, tblSystemServiceRecord.InspectPonding,
tblSystemServiceRecord.[check distal head], tblSystemServiceRecord.[adjust
lateral valves], tblSystemServiceRecord.[laterals cleaned],
tblSystemServiceRecord.[clean effluent filters],
tblSystemServiceRecord.[adjust recurc rate],
tblSystemServiceRecord.InspectClearnAirVent, tblSystemServiceRecord.[inspect
under drain], tblSystemServiceRecord.[Turn Power On],
tblSystemServiceRecord.Notes, tblSystemServiceRecord.[Notes RE: LGU],
tblSystemServiceRecord.SepticTankSludge,
tblSystemServiceRecord.SepticTankScum, tblSystemServiceRecord.PumpTankSludge,
tblSystemServiceRecord.PumpTankScum, tblSystemServiceRecord.AlarmsAerator,
tblSystemServiceRecord.AlarmsTrashTrap,
tblSystemServiceRecord.AlarmsDrainFieldPump,
tblSystemServiceRecord.TrashTrapPumpAmps,
tblSystemServiceRecord.TrashTrapPumpOperation,
tblSystemServiceRecord.DrainfieldPumpAmps,
tblSystemServiceRecord.DrainfieldOperation,
tblSystemServiceRecord.DrainfieldMLSS,
tblSystemServiceRecord.DrainfieldAerator, tblSampling.BOD, tblSampling.TSS,
tblSampling.Fecal, tblSampling.FOG, tblSampling.MLSS
FROM tblSystemServiceRecord LEFT JOIN tblSampling ON
tblSystemServiceRecord.[customer id] = tblSampling.CustomerID
WHERE (((tblSystemServiceRecord.ServiceDate) Between #1/1/2008# And
#12/31/2008#))
ORDER BY tblSystemServiceRecord.[customer id];

Thank you for taking the time to read this and for whatever suggestions you
offer.