 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2005Query help!
I am developing a small CMMS database. I have a table named PM SCHEDUAL, it has one field for Equipment ID and 52 fields for work weeks i.e. (WW01,WW02, WW03ect.). Under each work week field I have listed the inspection type for that week on each unit. I am trying to create a single query that will display only the field that matches the current work week -DatePart("ww",Date ())-so that i can run an append querie weekly to update my service records. Is there a way I can select the field in the query using code in the field box in design view? I dont want to seperate select and append queries for each week. Your problem is the table design, not the query. If you use a table that has a record for each work week rather than a column for each work week, the query would be easy. HTH; Amy >I am developing a small CMMS database. I have a table named PM SCHEDUAL, it > has one field for Equipment ID and 52 fields for work weeks i.e. [quoted text clipped - 9 lines] > box in design view? I dont want to seperate select and append queries for > each week. Amy I have already created a seperate table that has a record for each work week and a field for each equipment ID. The problem with that is that when I go to create the append query to create the weeks service records I need both the equipment ID and the inspection type to create a usable record. >Your problem is the table design, not the query. If you use a table that >has a record for each work week rather than a column for each work week, the [quoted text clipped - 9 lines] >> box in design view? I dont want to seperate select and append queries for >> each week. Why don't you describe more in detail what you need the system to do? You might want to move this discussion to the tabledesign newsgroup. HTH; Amy > Amy > I have already created a seperate table that has a record for each work [quoted text clipped - 21 lines] >>> for >>> each week. What I am trying to do is create cyclical preventive maintenance service records in my database. The cycles are weekly, monthly, quarterly, tri- annually, semiannually, and annually. There are several reasons I felt that making a table that contained a unit field and 52 work week fields then querying off it to append the service record table would be the simplest and best way to go. 1.I want to be able to enter new units, and in a short period of time be able to schedule their maintenance intervals. The types of inspections vary from unit to unit; also the man hours required to complete them varies. 2.I want to be able to control the work per week so that I don’t end up with 80 hours of work scheduled for one week and 20 hours of work scheduled for another. 3.I only want to generate a record if an inspection is due and then I only want a record for that type of inspection. 4.I want to be able to query the table to convert the inspection types into man-hours so that I can graph the weekly work load and maintain the annual balance by staggering the cycles of newly entered units. 5.I want the schedule “set in stone” I do not want any migration of the cycle do to calculated next due dates. So here’s what I’ve done. I created a table called PM Schedule. It has one unit field and 52 work week fields. UNITNUMBER WW41 WW42 WW43 WW44 TC1 WK WK QRT WK JETWAY1 WK SEMI WK WK OB4 MTH WK WK WK This allows me to quickly enter new units as my contract expands and control the annual cycle to place the man-hours at the most efficient intervals. This balances my weekly work load and it doesn’t migrate over any period of time. It also eliminates the need for numerous calculations on date/ time. I don’t have to worry that 52 doesn’t divide well by 3 or that every third monthly cycle is five weeks long. I also don’t have to worry about generating a weekly ,monthly, quarterly, and Semiannual work order for say Jetway1 in WW42 in the example, when all I need is the Semi. What I NEED is a Simple way to query this table so that the query will show the unit number field values and only the work week field values for the work week field that is equal to the current work week. I don’t think this should be too hard but I’m not as good at code as I wish I was. >Why don't you describe more in detail what you need the system to do? You >might want to move this discussion to the tabledesign newsgroup. [quoted text clipped - 8 lines] >>>> for >>>> each week. I don't think you can do what you want with the data structure you have. The problem is that the data structure you need: WorkWeeks: WorkWeekID WorkWeekName Inspections WorkWeekNumber UnitNumber InspectionTypeID InspectionType InspectionTypeID InspectionTypeName InspectionHoursNeeded OtherInspectionInfoThatMayBeRelevant Unit UnitNumber UnitName OtherUnitInfo (You'd also need a table for inspections completed, if you want to record that, but that's an aside from your question) Is very hard to get access to represent with forms in Access. I imagine the reason you've gone to all one table is that it's easy to represent all 52 weeks on the page at one time in the form when that's what you do, even if there aren't any records for the weeks yet. Unfortunately this is just a limitation of the tool you can't get around without some heavy duty coding and querying. However, you can't justify a data design that can't be queried in the way you've needed just because you have a hard time writing forms to get the data *IN.* There is no point making it easy to get the data in if you can't get it back out. So I guess you have several choices: 1) leave the data structure as it is, and write a separate query for each week. 2) look for an off the shelf product that does what you want 3) change the data structure, but be ready to get your hands dirty with some serious code 4) find a consultant who can change the data structure and write the forms that work like the one that feeds your current structure HTH; Amy > What I am trying to do is create cyclical preventive maintenance service > records in my database. The cycles are weekly, monthly, quarterly, tri- [quoted text clipped - 66 lines] >>>>> for >>>>> each week. Thanks Amy I will just do 52 querys with 52 append querys and create a form PM Generator with 52 comand buttons. >I don't think you can do what you want with the data structure you have. >The problem is that the data structure you need: [quoted text clipped - 52 lines] >>>>>> for >>>>>> each week. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.