|
| 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
December 12th, 2009, 05:42 PM
posted to microsoft.public.access.reports
|
|||
|
|||
group check box records
I have a report based on a query. How can I group records by the last time
(max date) a check box is true? -- Thanks for your help! Walter 
|
|
#2
December 13th, 2009, 06:44 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
group check box records
Sorting and grouping in a report is always accomplished by using the Sorting
and Grouping dialog in the report design view. -- Duane Hookom Microsoft Access MVP "Walter" wrote: I have a report based on a query. How can I group records by the last time (max date) a check box is true? -- Thanks for your help! Walter
|
|
#3
December 15th, 2009, 06:06 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
group check box records
I haven't been able to figure out how to accomplish what I want or if it's
possible. My table has 15 yes/no fields to track tasks performed. All tasks may or may not be performed each time. I would like to have a report that shows the last time each task was done as in: Date Field A, Field B, Field C, Field F, Field G, Field J, Field K Date Field D, Field I Date Field E Date Field H Is this possible and if so how would I go about it? -- Thanks for your help! Walter "Duane Hookom" wrote: Sorting and grouping in a report is always accomplished by using the Sorting and Grouping dialog in the report design view. -- Duane Hookom Microsoft Access MVP "Walter" wrote: I have a report based on a query. How can I group records by the last time (max date) a check box is true? -- Thanks for your help! Walter
|
|
#4
December 15th, 2009, 11:30 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
group check box records
Your table structure is not normalized so it causes complications. If you
can't organize it so each task creates its own record then you can use a normalizing union query. Can you provide additional in formation about the primary key and actual field names? -- Duane Hookom Microsoft Access MVP "Walter" wrote: I haven't been able to figure out how to accomplish what I want or if it's possible. My table has 15 yes/no fields to track tasks performed. All tasks may or may not be performed each time. I would like to have a report that shows the last time each task was done as in: Date Field A, Field B, Field C, Field F, Field G, Field J, Field K Date Field D, Field I Date Field E Date Field H Is this possible and if so how would I go about it? -- Thanks for your help! Walter "Duane Hookom" wrote: Sorting and grouping in a report is always accomplished by using the Sorting and Grouping dialog in the report design view. -- Duane Hookom Microsoft Access MVP "Walter" wrote: I have a report based on a query. How can I group records by the last time (max date) a check box is true? -- Thanks for your help! Walter
|
|
#5
December 16th, 2009, 12:11 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
group check box records
I'm tracking truck service records. There is a Repairs table with ServiceID,
Truck #, date, and mileage fields. The service table has RoutineServiceID, OilChange, OilFilter, FuelFilter, TransmissionChecked, etc fields and is related to the Repairs table in a 1 to Many relationship. It seemed at the time these tasks were all related and the correct way to go. I'm not sure how many records would have to be changed to correct this but if that's the best solution it's probably possible. -- Thanks for your help! Walter "Duane Hookom" wrote: Your table structure is not normalized so it causes complications. If you can't organize it so each task creates its own record then you can use a normalizing union query. Can you provide additional in formation about the primary key and actual field names? -- Duane Hookom Microsoft Access MVP "Walter" wrote: I haven't been able to figure out how to accomplish what I want or if it's possible. My table has 15 yes/no fields to track tasks performed. All tasks may or may not be performed each time. I would like to have a report that shows the last time each task was done as in: Date Field A, Field B, Field C, Field F, Field G, Field J, Field K Date Field D, Field I Date Field E Date Field H Is this possible and if so how would I go about it? -- Thanks for your help! Walter "Duane Hookom" wrote: Sorting and grouping in a report is always accomplished by using the Sorting and Grouping dialog in the report design view. -- Duane Hookom Microsoft Access MVP "Walter" wrote: I have a report based on a query. How can I group records by the last time (max date) a check box is true? -- Thanks for your help! Walter
|
|
#6
December 16th, 2009, 02:59 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
group check box records
What field in the service table relates to a what field in the repairs table?
Does the service table have a primary key? Are these your actual table names? -- Duane Hookom Microsoft Access MVP "Walter" wrote: I'm tracking truck service records. There is a Repairs table with ServiceID, Truck #, date, and mileage fields. The service table has RoutineServiceID, OilChange, OilFilter, FuelFilter, TransmissionChecked, etc fields and is related to the Repairs table in a 1 to Many relationship. It seemed at the time these tasks were all related and the correct way to go. I'm not sure how many records would have to be changed to correct this but if that's the best solution it's probably possible. -- Thanks for your help! Walter "Duane Hookom" wrote: Your table structure is not normalized so it causes complications. If you can't organize it so each task creates its own record then you can use a normalizing union query. Can you provide additional in formation about the primary key and actual field names? -- Duane Hookom Microsoft Access MVP "Walter" wrote: I haven't been able to figure out how to accomplish what I want or if it's possible. My table has 15 yes/no fields to track tasks performed. All tasks may or may not be performed each time. I would like to have a report that shows the last time each task was done as in: Date Field A, Field B, Field C, Field F, Field G, Field J, Field K Date Field D, Field I Date Field E Date Field H Is this possible and if so how would I go about it? -- Thanks for your help! Walter "Duane Hookom" wrote: Sorting and grouping in a report is always accomplished by using the Sorting and Grouping dialog in the report design view. -- Duane Hookom Microsoft Access MVP "Walter" wrote: I have a report based on a query. How can I group records by the last time (max date) a check box is true? -- Thanks for your help! Walter
|
|
#7
December 16th, 2009, 10:26 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
group check box records
Sorry I left out the links. The repairs table is tblServiceDetails
withServiceDetailsID as the PK. The service table is tblRoutineService with RoutineServiceID as the PK and also includes the field ServiceDetailsID as FK to tblServiceDetails. -- Thanks for your help! Walter "Duane Hookom" wrote: What field in the service table relates to a what field in the repairs table? Does the service table have a primary key? Are these your actual table names? -- Duane Hookom Microsoft Access MVP "Walter" wrote: I'm tracking truck service records. There is a Repairs table with ServiceID, Truck #, date, and mileage fields. The service table has RoutineServiceID, OilChange, OilFilter, FuelFilter, TransmissionChecked, etc fields and is related to the Repairs table in a 1 to Many relationship. It seemed at the time these tasks were all related and the correct way to go. I'm not sure how many records would have to be changed to correct this but if that's the best solution it's probably possible. -- Thanks for your help! Walter "Duane Hookom" wrote: Your table structure is not normalized so it causes complications. If you can't organize it so each task creates its own record then you can use a normalizing union query. Can you provide additional in formation about the primary key and actual field names? -- Duane Hookom Microsoft Access MVP "Walter" wrote: I haven't been able to figure out how to accomplish what I want or if it's possible. My table has 15 yes/no fields to track tasks performed. All tasks may or may not be performed each time. I would like to have a report that shows the last time each task was done as in: Date Field A, Field B, Field C, Field F, Field G, Field J, Field K Date Field D, Field I Date Field E Date Field H Is this possible and if so how would I go about it? -- Thanks for your help! Walter "Duane Hookom" wrote: Sorting and grouping in a report is always accomplished by using the Sorting and Grouping dialog in the report design view. -- Duane Hookom Microsoft Access MVP "Walter" wrote: I have a report based on a query. How can I group records by the last time (max date) a check box is true? -- Thanks for your help! Walter
|
|
#8
December 17th, 2009, 05:15 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
group check box records
You can create a union query with SQL like:
SELECT RoutineServiceID, ServiceDetailsID, "OilChange" as Service FROM tblRoutineService WHERE OilChange = True UNION ALL SELECT RoutineServiceID, ServiceDetailsID, "OilFilter" FROM tblRoutineService WHERE OilFilter = True UNION ALL SELECT RoutineServiceID, ServiceDetailsID, "FuelFilter" FROM tblRoutineService WHERE FuelFilter = True UNION ALL SELECT RoutineServiceID, ServiceDetailsID, "TransmissionChecked" FROM tblRoutineService WHERE TransmissionChecked = True UNION ALL -- etc -- You can then join this query to other tables to get the dates. This allows grouping by a vehicles and Service with the Max of a Service Date. -- Duane Hookom Microsoft Access MVP "Walter" wrote: Sorry I left out the links. The repairs table is tblServiceDetails withServiceDetailsID as the PK. The service table is tblRoutineService with RoutineServiceID as the PK and also includes the field ServiceDetailsID as FK to tblServiceDetails. -- Thanks for your help! Walter "Duane Hookom" wrote: What field in the service table relates to a what field in the repairs table? Does the service table have a primary key? Are these your actual table names? -- Duane Hookom Microsoft Access MVP "Walter" wrote: I'm tracking truck service records. There is a Repairs table with ServiceID, Truck #, date, and mileage fields. The service table has RoutineServiceID, OilChange, OilFilter, FuelFilter, TransmissionChecked, etc fields and is related to the Repairs table in a 1 to Many relationship. It seemed at the time these tasks were all related and the correct way to go. I'm not sure how many records would have to be changed to correct this but if that's the best solution it's probably possible. -- Thanks for your help! Walter "Duane Hookom" wrote: Your table structure is not normalized so it causes complications. If you can't organize it so each task creates its own record then you can use a normalizing union query. Can you provide additional in formation about the primary key and actual field names? -- Duane Hookom Microsoft Access MVP "Walter" wrote: I haven't been able to figure out how to accomplish what I want or if it's possible. My table has 15 yes/no fields to track tasks performed. All tasks may or may not be performed each time. I would like to have a report that shows the last time each task was done as in: Date Field A, Field B, Field C, Field F, Field G, Field J, Field K Date Field D, Field I Date Field E Date Field H Is this possible and if so how would I go about it? -- Thanks for your help! Walter "Duane Hookom" wrote: Sorting and grouping in a report is always accomplished by using the Sorting and Grouping dialog in the report design view. -- Duane Hookom Microsoft Access MVP "Walter" wrote: I have a report based on a query. How can I group records by the last time (max date) a check box is true? -- Thanks for your help! Walter
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
