If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
report design
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! |
#3
|
|||
|
|||
report design
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! |
#4
|
|||
|
|||
report design
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! |
#5
|
|||
|
|||
report design
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! |
Thread Tools | |
Display Modes | |
|
|