C
cesemj
Hello,
Please suggest what my next step should be:
reference Link : http://members.cox.net/cesemj (same as below with pictures)
I have One table that I read into a DAO record set array to find out how
many times did a Part (Example: part number: AC12345) associated with autoID
(company ID: 12345=Chevy) fail from the date the selected or all parts
associated with a major system was installed (System_Installed_Date).
PART# AUTOID System_Install_Date Date_service_start
Date_service_closed
ac12345 12345 1/1/94 6/28/98
8/16/98
ac12345 12345 1/1/94 5/01/00
6/19/00
ac12345 12345 1/1/94 2/15/03
12/3/03
ac12345 67899 1/1/04 4/12/05
5/3/05
General Overview:
1) For each Part# in the tblJunction, check to see how many times the part
failed. Do this by selecting a record from a form frmPart#Lookup. The
record set will compute the number of failures for that part, the
Analysis_man_hours & Repair_man_hours and devide the total by number of
failures to get MTBF in hours, the record set will also calculate the date
difference between date and devide the total sum by the number of failures to
get the MTBF and MTBF in hrs.
The datediff is between the system_install_date for each AUTOID
and the date opening. If the AUTOID appears more than once, the current
autoID, Part#, Date_service_start , and Date_service_closed is moved to
previous and the next record that has a matching AUTOID and PART# moved to
previous and the datediff is between the Previous Date_service_closed from
and Current Date_service_start . The loop continues until all the records
associated with that Part# is analyzed.
2) If the Part# has a PC number value (part change number of 4,8,9,10,11,12,
13,14,15, or 16) on the same row then the datediff will be between the
ec_install_date and date_service_start for that row (record). If the AUTOID
appears more than once, the current autoID, Part#, Date_service_start , and
Date_service_closed is moved to previous and the next record that has a
matching AUTOID and PART# moved to previous and the datediff is between the
Previous Date_service_closed from and Current Date_service_start . The
loop continues until all the records associated with that Part# is analyzed.
Output
Output is also written to a table that lets me use it for reports and/or
cross tab queries, and/or pivot tables/charts. The table should have the same
fields as frmPart#Lookup.
tblOutputTwo will be used to show which autoID are having problems. I will
be able to create a pivot table and/or crosstab query to filter by Part#.
Sample Report:
This part was broke and fixed this date
This part was broke and fixed this date and by the way it was upgraded by
this PCpart on this date.
MOTE: The Spreadsheet and tblMTBF is the current manual way I perform this
process.
Please suggest what my next step should be:
reference Link : http://members.cox.net/cesemj (same as below with pictures)
I have One table that I read into a DAO record set array to find out how
many times did a Part (Example: part number: AC12345) associated with autoID
(company ID: 12345=Chevy) fail from the date the selected or all parts
associated with a major system was installed (System_Installed_Date).
PART# AUTOID System_Install_Date Date_service_start
Date_service_closed
ac12345 12345 1/1/94 6/28/98
8/16/98
ac12345 12345 1/1/94 5/01/00
6/19/00
ac12345 12345 1/1/94 2/15/03
12/3/03
ac12345 67899 1/1/04 4/12/05
5/3/05
General Overview:
1) For each Part# in the tblJunction, check to see how many times the part
failed. Do this by selecting a record from a form frmPart#Lookup. The
record set will compute the number of failures for that part, the
Analysis_man_hours & Repair_man_hours and devide the total by number of
failures to get MTBF in hours, the record set will also calculate the date
difference between date and devide the total sum by the number of failures to
get the MTBF and MTBF in hrs.
The datediff is between the system_install_date for each AUTOID
and the date opening. If the AUTOID appears more than once, the current
autoID, Part#, Date_service_start , and Date_service_closed is moved to
previous and the next record that has a matching AUTOID and PART# moved to
previous and the datediff is between the Previous Date_service_closed from
and Current Date_service_start . The loop continues until all the records
associated with that Part# is analyzed.
2) If the Part# has a PC number value (part change number of 4,8,9,10,11,12,
13,14,15, or 16) on the same row then the datediff will be between the
ec_install_date and date_service_start for that row (record). If the AUTOID
appears more than once, the current autoID, Part#, Date_service_start , and
Date_service_closed is moved to previous and the next record that has a
matching AUTOID and PART# moved to previous and the datediff is between the
Previous Date_service_closed from and Current Date_service_start . The
loop continues until all the records associated with that Part# is analyzed.
Output
Output is also written to a table that lets me use it for reports and/or
cross tab queries, and/or pivot tables/charts. The table should have the same
fields as frmPart#Lookup.
tblOutputTwo will be used to show which autoID are having problems. I will
be able to create a pivot table and/or crosstab query to filter by Part#.
Sample Report:
This part was broke and fixed this date
This part was broke and fixed this date and by the way it was upgraded by
this PCpart on this date.
MOTE: The Spreadsheet and tblMTBF is the current manual way I perform this
process.