report design

I need to run one report for each customer. Each customer receives several
service calls a year. The report is based on the final visit (the date varies
with customer). The numeric data presented in the report is based on the
difference between the final service call and the most recent prior to that,
e.g. how many gallons of water were pumped between the last two calls. My
report design currently shows a report for each visit. I want only the final
visit date. I believe the solution lies in grouping, but I haven't figured it
out. Suggestions and direction welcomed!

Post your table structure with field names and datatype. Post sample data
and example of what you want as output data.
--
Build a little, test a little.


"northstar" wrote:

I need to run one report for each customer. Each customer receives several
service calls a year. The report is based on the final visit (the date varies
with customer). The numeric data presented in the report is based on the
difference between the final service call and the most recent prior to that,
e.g. how many gallons of water were pumped between the last two calls. My
report design currently shows a report for each visit. I want only the final
visit date. I believe the solution lies in grouping, but I haven't figured it
out. Suggestions and direction welcomed!

Structure for customer table:
CustomerID(autonum,primary),LastName(text),FirstNa me(text),StreetAddress(text),City(text), State(text), Zip(text), etc for location data
Structure for System table: SystemID(autonum,primary),
ModelType(text),WaterMeterType(text),PumpType(text )
Structure for Service table:
ServiceID(autonum,primary),CustomerID(foreign),Ser viceDate(date),MeterReading(number, fixed, 1 decimal)
What I currently get in my query: Sample Data:
ID ServiceDate PreviousServiceDate CurrentMeterReading PreviousMeterReading
1 11/12/2008 10/8/2008 4587 2400
1 10/8/2008 5/23/2008 2400 2200
1 5/23/2008 5/5/2007 2200 2000
2 5/6/2008 1/3/2008 2850 1748
2 10/8/2008 5/6/2008 3499 2850
3 5/6/2008 3/14/2008 12106 11980
3 10/8/2008 5/6/2008 12798 12106
4 5/6/2008 6/27/2006 134404 132945
4 10/8/2008 5/6/2008 38695 134404
5 6/3/2008 2/3/2008 38370 28122
5 10/8/2008 6/3/2008 46492 38370

I want only the lastest date for each customer, e.g.
1 11/12/2008 10/8/2008 4587 2400
2 10/8/2008 5/6/2008 3499 2850
3 10/8/2008 5/6/2008 12798 12106
4 10/8/2008 5/6/2008 38695 134404
5 10/8/2008 6/3/2008 46492 38370
Thanks, Jim
"KARL DEWEY" wrote:

Post your table structure with field names and datatype. Post sample data
and example of what you want as output data.
--
Build a little, test a little.


"northstar" wrote:

I need to run one report for each customer. Each customer receives several
service calls a year. The report is based on the final visit (the date varies
with customer). The numeric data presented in the report is based on the
difference between the final service call and the most recent prior to that,
e.g. how many gallons of water were pumped between the last two calls. My
report design currently shows a report for each visit. I want only the final
visit date. I believe the solution lies in grouping, but I haven't figured it
out. Suggestions and direction welcomed!

Try these two queries --
LastServiceDate --
SELECT Service.CustomerID, Max(Service.ServiceDate) AS MaxOfServiceDate
FROM Service
GROUP BY Service.CustomerID;

SELECT Service.CustomerID, Service.ServiceDate, Service.PreviousServiceDate,
Service.CurrentMeterReading, Service.PreviousMeterReading
FROM Service INNER JOIN LastServiceDate ON (Service.ServiceDate =
LastServiceDate.MaxOfServiceDate) AND (Service.CustomerID =
LastServiceDate.CustomerID)
ORDER BY Service.CustomerID;

--
Build a little, test a little.


"northstar" wrote:

Structure for customer table:
CustomerID(autonum,primary),LastName(text),FirstNa me(text),StreetAddress(text),City(text), State(text), Zip(text), etc for location data
Structure for System table: SystemID(autonum,primary),
ModelType(text),WaterMeterType(text),PumpType(text )
Structure for Service table:
ServiceID(autonum,primary),CustomerID(foreign),Ser viceDate(date),MeterReading(number, fixed, 1 decimal)
What I currently get in my query: Sample Data:
ID ServiceDate PreviousServiceDate CurrentMeterReading PreviousMeterReading
1 11/12/2008 10/8/2008 4587 2400
1 10/8/2008 5/23/2008 2400 2200
1 5/23/2008 5/5/2007 2200 2000
2 5/6/2008 1/3/2008 2850 1748
2 10/8/2008 5/6/2008 3499 2850
3 5/6/2008 3/14/2008 12106 11980
3 10/8/2008 5/6/2008 12798 12106
4 5/6/2008 6/27/2006 134404 132945
4 10/8/2008 5/6/2008 38695 134404
5 6/3/2008 2/3/2008 38370 28122
5 10/8/2008 6/3/2008 46492 38370

I want only the lastest date for each customer, e.g.
1 11/12/2008 10/8/2008 4587 2400
2 10/8/2008 5/6/2008 3499 2850
3 10/8/2008 5/6/2008 12798 12106
4 10/8/2008 5/6/2008 38695 134404
5 10/8/2008 6/3/2008 46492 38370
Thanks, Jim
"KARL DEWEY" wrote:

Post your table structure with field names and datatype. Post sample data
and example of what you want as output data.
--
Build a little, test a little.


"northstar" wrote:

I need to run one report for each customer. Each customer receives several
service calls a year. The report is based on the final visit (the date varies
with customer). The numeric data presented in the report is based on the
difference between the final service call and the most recent prior to that,
e.g. how many gallons of water were pumped between the last two calls. My
report design currently shows a report for each visit. I want only the final
visit date. I believe the solution lies in grouping, but I haven't figured it
out. Suggestions and direction welcomed!

Karl, thanks for your assistance. I learned several things, among them is
that I am really working with queries, not a report. Why didn't I see that
from the beginning!!!

"KARL DEWEY" wrote:

Try these two queries --
LastServiceDate --
SELECT Service.CustomerID, Max(Service.ServiceDate) AS MaxOfServiceDate
FROM Service
GROUP BY Service.CustomerID;

SELECT Service.CustomerID, Service.ServiceDate, Service.PreviousServiceDate,
Service.CurrentMeterReading, Service.PreviousMeterReading
FROM Service INNER JOIN LastServiceDate ON (Service.ServiceDate =
LastServiceDate.MaxOfServiceDate) AND (Service.CustomerID =
LastServiceDate.CustomerID)
ORDER BY Service.CustomerID;

--
Build a little, test a little.


"northstar" wrote:

Structure for customer table:
CustomerID(autonum,primary),LastName(text),FirstNa me(text),StreetAddress(text),City(text), State(text), Zip(text), etc for location data
Structure for System table: SystemID(autonum,primary),
ModelType(text),WaterMeterType(text),PumpType(text )
Structure for Service table:
ServiceID(autonum,primary),CustomerID(foreign),Ser viceDate(date),MeterReading(number, fixed, 1 decimal)
What I currently get in my query: Sample Data:
ID ServiceDate PreviousServiceDate CurrentMeterReading PreviousMeterReading
1 11/12/2008 10/8/2008 4587 2400
1 10/8/2008 5/23/2008 2400 2200
1 5/23/2008 5/5/2007 2200 2000
2 5/6/2008 1/3/2008 2850 1748
2 10/8/2008 5/6/2008 3499 2850
3 5/6/2008 3/14/2008 12106 11980
3 10/8/2008 5/6/2008 12798 12106
4 5/6/2008 6/27/2006 134404 132945
4 10/8/2008 5/6/2008 38695 134404
5 6/3/2008 2/3/2008 38370 28122
5 10/8/2008 6/3/2008 46492 38370

I want only the lastest date for each customer, e.g.
1 11/12/2008 10/8/2008 4587 2400
2 10/8/2008 5/6/2008 3499 2850
3 10/8/2008 5/6/2008 12798 12106
4 10/8/2008 5/6/2008 38695 134404
5 10/8/2008 6/3/2008 46492 38370
Thanks, Jim
"KARL DEWEY" wrote:

Post your table structure with field names and datatype. Post sample data
and example of what you want as output data.
--
Build a little, test a little.


"northstar" wrote:

I need to run one report for each customer. Each customer receives several
service calls a year. The report is based on the final visit (the date varies
with customer). The numeric data presented in the report is based on the
difference between the final service call and the most recent prior to that,
e.g. how many gallons of water were pumped between the last two calls. My
report design currently shows a report for each visit. I want only the final
visit date. I believe the solution lies in grouping, but I haven't figured it
out. Suggestions and direction welcomed!