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 |
#11
|
|||
|
|||
Building Database for Army Helicopter Maintence Program...Need
OK- after looking at what you suggested, i have some questions. 1: how will
i be able to us this to enter info into a form and have it sent to all of the correct tables? 2: the i'm not sue what to do with the personell table. the work i track is requested by units, not people. 3: with the workorders table, i can enter the info i have now, but what about future info that needs to go in. hence, my desire for a single form to send out the info. 4: the tables parts and w/o parts, i am not sure what to do with them, b/c there are way too many parts to list and i don't even know what they all are. thanks for the patience. "Ken Snell (MVP)" wrote: OK - we're going to approach this as a database, not as a "souped-up" spreadsheet g . From the data list that you've posted, you should create the following tables: tblUnits UnitID (primary key) UnitName UnitLocation UnitContactPerson (etc.) tblAircraftModels ModelID (primary key) ModelName (etc.) tblAircrafts AircraftID (primary key -- note that this could be the tail number) AircraftTailNum (if you don't use tail number as primary key) ModelID (foreign key from tblModels table) (etc.) tblParts PartID (foreign key) PartDescription (etc.) tblPersonnel PersonnelID (primary key) PersonFirstName PersonLastName PersonRank PersonSerialNum UnitID (foreign key to tblUnits) (etc.) tblWOPriorities WOPriorityNum (foreign key) WOPriorityDescription tblWorkOrders WONumber (primary key -- this would be the support work order number) AircraftID (foreign key to tblAircrafts) PersonnelID (foreign key to tblPersonnel -- this is person requesting the work) UnitWONumber FaultDescription WONotes WOPriorityNum (foreign key to tblWOPriorities) DateOpened DateAccepted DateClosed (etc.) tblWOParts WOPartsID (primary key) WONumber (foreign key to tblWorkOrders) PartID (foreign key to tblParts) WOPartQuantity (etc.) The above table structure allows the following things to be done in the database file (this prevents the entry of slightly misspelled names for the same person, for example; and it prevents the entry of invalid values for priorities, for example): 1) Use a combo box on a form in order to select the person requesting the work 2) Use a combo box on a form in order to select the aircraft to be repaired 3) Use a combo box on a form in order to select the work order priority 4) Use a combo box on a form to select the part for the work order 5) Have multiple parts for each work order 6) Track which orders are "pending" (not accepted yet), "in process" (opened but not closed), and "closed" 7) Track which persons have requested work orders 8) Track which units have requested work orders 9) Show all work orders performed on an aircraft 10) Show all work orders performed on an aircraft model (etc. etc. etc.) Your comment about the "military" date having to be "Julian". Personally, I would store dates in the database in the "normal" manner (Date/Time data type) that ACCESS uses (namely, a long integer number representing the number of days since December 30, 1899). You can always display the dates in whatever format you wish, although it's likely that you'll need a custom function to convert the normal date to the military Julian date, and vice-versa. This is a bit tricky for beginners, but very doable. The reason I recommend this is because ACCESS queries are going to expect dates to be in the normal format (e.g., "mm/dd/yyyy") in order to select data records for your reports. If you store the dates as military Julian dates, you then will need to have a function that converts the Julian date to a "mm/dd/yyyy" date just to run the query... and you'll not want to do that all the time because it will slow your queries down as you gain more data. In the above table structure, where I indicate "foreign key to xxx table", that indicates a Relationship exists between the two tables -- a "join line" in the Relationships window. Through these relationships, you can store a data value in just one table and then be able to look it up via query at any time. Thus, by putting the UnitID in the tblPersonnel, and then by using PersonnelID in tblWorkOrders, you can display the UnitName for the unit that has requested the work order through the PersonnelID link to tblPersonnel and then through the UnitID link to tblUnits. After you've had a chance to look this over, post back with questions. -- Ken Snell MS ACCESS MVP "M. Parker" wrote in message ... ok, here goes.... data i need: units, aircraft tail numbers, aircraft model, unit workorder number, part description, work requested by, serial number, fault description, priority of work order, opened date, accepted date, support workorder number, and closed date. dates need to be military julian i.e, 13 april 06 = 6114. i want to do a master table that will automatically put the info from it onto the corresponding units table. thanks for the help, you rock! matt snipped |
#12
|
|||
|
|||
Building Database for Army Helicopter Maintence Program...Need
The tables that I suggest are based on what I am understanding (ok,
inferring grin ) from your list of data that you want to store. Your list included work requested by and serial number. I was assuming that these two items refer to a person who is making the request, and the serial number of that person. If this is not correct, please clarify what these two items are. The data design that I'm suggesting is using what are called "lookup" tables -- a table that stores unique data that will be used throughout the database -- e.g., tblUnits. In order to enter data into them, typically you'd create a form that is based on that table and then you can enter new records and edit existing ones. No one form should be considered for all the tables that I suggest. However, you will be able to use a single form for entering records into tblWorkOrders table -- and, if you were to use it, tblWOParts could be populated using a subform within this form. Note that my suggestions are just that -- suggestions, based on my interpretation of what you listed. I assume that a repair work order may involve more than just one part for the repair, which is why I suggest a separate "child" table for the parts used on a work order. From you list of data items, and from my suggested table structure, you now can make decisions about what the database will actually hold, and what will be the "business" rules for it (e.g, only one part per work order; no person makes a request for a work order but instead a unit makes a request; etc.). Creating a database should always involve a lot of upfront paper/pencil time as you consider the data, the characteristics, the future needs, etc. -- only after that should one begin to create the database. We're still in the "paper/pencil" mode -- we're hampered a bit by the fact that our communications are via this written medium, so our questions/answers will go back and forth without the benefit of seeing "live" the types of things you already have or want to do. Doing the design on paper first is more likely to produce a workable database for reports, entry, etc. So, consider the business rules and the data again, and then let's determine the items that should be stored so that you can use them in reports later on. -- Ken Snell MS ACCESS MVP "M. Parker" wrote in message ... OK- after looking at what you suggested, i have some questions. 1: how will i be able to us this to enter info into a form and have it sent to all of the correct tables? 2: the i'm not sue what to do with the personell table. the work i track is requested by units, not people. 3: with the workorders table, i can enter the info i have now, but what about future info that needs to go in. hence, my desire for a single form to send out the info. 4: the tables parts and w/o parts, i am not sure what to do with them, b/c there are way too many parts to list and i don't even know what they all are. thanks for the patience. "Ken Snell (MVP)" wrote: OK - we're going to approach this as a database, not as a "souped-up" spreadsheet g . From the data list that you've posted, you should create the following tables: tblUnits UnitID (primary key) UnitName UnitLocation UnitContactPerson (etc.) tblAircraftModels ModelID (primary key) ModelName (etc.) tblAircrafts AircraftID (primary key -- note that this could be the tail number) AircraftTailNum (if you don't use tail number as primary key) ModelID (foreign key from tblModels table) (etc.) tblParts PartID (foreign key) PartDescription (etc.) tblPersonnel PersonnelID (primary key) PersonFirstName PersonLastName PersonRank PersonSerialNum UnitID (foreign key to tblUnits) (etc.) tblWOPriorities WOPriorityNum (foreign key) WOPriorityDescription tblWorkOrders WONumber (primary key -- this would be the support work order number) AircraftID (foreign key to tblAircrafts) PersonnelID (foreign key to tblPersonnel -- this is person requesting the work) UnitWONumber FaultDescription WONotes WOPriorityNum (foreign key to tblWOPriorities) DateOpened DateAccepted DateClosed (etc.) tblWOParts WOPartsID (primary key) WONumber (foreign key to tblWorkOrders) PartID (foreign key to tblParts) WOPartQuantity (etc.) The above table structure allows the following things to be done in the database file (this prevents the entry of slightly misspelled names for the same person, for example; and it prevents the entry of invalid values for priorities, for example): 1) Use a combo box on a form in order to select the person requesting the work 2) Use a combo box on a form in order to select the aircraft to be repaired 3) Use a combo box on a form in order to select the work order priority 4) Use a combo box on a form to select the part for the work order 5) Have multiple parts for each work order 6) Track which orders are "pending" (not accepted yet), "in process" (opened but not closed), and "closed" 7) Track which persons have requested work orders 8) Track which units have requested work orders 9) Show all work orders performed on an aircraft 10) Show all work orders performed on an aircraft model (etc. etc. etc.) Your comment about the "military" date having to be "Julian". Personally, I would store dates in the database in the "normal" manner (Date/Time data type) that ACCESS uses (namely, a long integer number representing the number of days since December 30, 1899). You can always display the dates in whatever format you wish, although it's likely that you'll need a custom function to convert the normal date to the military Julian date, and vice-versa. This is a bit tricky for beginners, but very doable. The reason I recommend this is because ACCESS queries are going to expect dates to be in the normal format (e.g., "mm/dd/yyyy") in order to select data records for your reports. If you store the dates as military Julian dates, you then will need to have a function that converts the Julian date to a "mm/dd/yyyy" date just to run the query... and you'll not want to do that all the time because it will slow your queries down as you gain more data. In the above table structure, where I indicate "foreign key to xxx table", that indicates a Relationship exists between the two tables -- a "join line" in the Relationships window. Through these relationships, you can store a data value in just one table and then be able to look it up via query at any time. Thus, by putting the UnitID in the tblPersonnel, and then by using PersonnelID in tblWorkOrders, you can display the UnitName for the unit that has requested the work order through the PersonnelID link to tblPersonnel and then through the UnitID link to tblUnits. After you've had a chance to look this over, post back with questions. -- Ken Snell MS ACCESS MVP "M. Parker" wrote in message ... ok, here goes.... data i need: units, aircraft tail numbers, aircraft model, unit workorder number, part description, work requested by, serial number, fault description, priority of work order, opened date, accepted date, support workorder number, and closed date. dates need to be military julian i.e, 13 april 06 = 6114. i want to do a master table that will automatically put the info from it onto the corresponding units table. thanks for the help, you rock! matt snipped |
#13
|
|||
|
|||
Templates
Not to discourage you from building a database from scratch, but there are
some templates available on the Microsoft site, that could either be modified for what you need, or at the very least give you an idea of how to structure your own database. The first one is an Orders Management Database, which is good for handling discrete items: http://office.microsoft.com/en-us/te...CT011366681033 The other one, which is perhaps more applicable to what you are doing, is a Service Call Management Database. This one can also track parts. http://office.microsoft.com/en-us/te...CT011366681033 I would suggest that you download these, look at the tables and relationships, and see how the queries, forms and reports are laid out. They are set up as civilian business templates, but I believe they could be modified (in some cases by just renaming fields) to fit the information you would require in the military. Remember, before modifying any database, make a back-up. A database can be very unforgiving of errors (many processes do not allow you to back-up once they are completed). It can be very frustrating to just do one final change to a form, and have it blow up and stop working. "M. Parker" wrote: I am building a database to track workorders to a support unit. I am completely new to this and need help with pretty much everything. I need to be able to track by open/closed workorders, by company i.e. A Co, B Co, C Co, D Co, and by priority of the workorder. I also need to be able to print reports that show the info I need to track. Any help would be awesome. |
#14
|
|||
|
|||
Building Database for Army Helicopter Maintence Program...Need
ULLS has never been well received. In any case you really need to decide if
you want to take the time to learn database fundamentals and Access. I do not want to discourage you; however there is a steep learning curve that requires years to reach the level the Access MVP are cabable of producing. OTH you have a an excellent mentor who is willing to share his time and expertise. You should consider yourself fortunate. I am familiar with the 2405. I am an old soldier who retired years ago. I applaud your initiative and highly encourage you to pursue learning Access if for nothing else but your own knowledge for future use. You mentioned you only work with unit information. Has the Aviation field changed where they are no longer using TI's? I am sure they are still using the 1687 Sig cards. I would include this field and the date of expiration on the card. You can also send reminders to the individuals when the card will expire. This will keep you one step ahead of the inspection. I would definetly have the personnel table that Ken recommended. It is also highly recommended to sharpen your pencil you will have to design the draft on paper. Once you have this you should consult with your co-workers and some of the old timers for their input. Again if you want to learn you have an excellent mentor. It will be a long road ahead. You can build something very basic with Excel for the 2405. The down side is the reporting features as well as capabilities do not even come close to the task at hand. OTH if you decide Access you will enjoy and appreciate the application once you discover the power of Access. I have seen one of Tom Wickeraths (another Access MVP) databases and it really far surpasses anything I have ever seen, and I have been using Access for 10 years. These guys really know their craft. I can help where I can with the logistics side. Your decision, good luck. "M. Parker" wrote: Actually, we are using ulls-a...the replacement system will be ulls scp6. ulls is working fine, i just hate using it. i am doing this to replace the da2405 log book. there is some good info in your post, are you aviation as well, if so, where? i am at hood. i won't need to track hours, b/c our bamo set up an excel spreadsheet that does that. if you are here, maybe we can link up. "Apache" wrote: I am not sure if you have ULLS-G, and is it not working? There is also another system that will soon replace ULLS since it is way overdue. In any case since you are interested in learning access this is a great. You may want to add another table for the Technical Inspectors or TI's this would serve as a lookup up table. You could probably use the Personnel table. I would also add an archive table to store the repair history of the aircraft. You could have a form where when any of the serial or aircraft numbers are entered the form populates the required fields. You will also require a field for the TI or test piliot who cleared the repairs. Since aircraft NMC is recorded in hours, you may also need the ability to track the hours? When developing a db it is important to research the information and place it on paper, prior to implementation. I would highly recommend finding a copy of "Database Design for Mere Mortals" author Mike Hernandez. This will provide some insight to the task. I would also attempt to find the FUG (Functonal Users Guide) to ULLS-G to see what fields they are capturing. You maynot need all of them; however, it may provoke some thought when designing the db. There is a lot of interesting information on the web and in the NG for Access. The Access gurus (Access MVP's) like Ken Snell, Tom Wickerath or many others provide awesome assistance; however, they are also assisting several posters as yourself. HTH "Ken Snell (MVP)" wrote: OK - we're going to approach this as a database, not as a "souped-up" spreadsheet g . From the data list that you've posted, you should create the following tables: tblUnits UnitID (primary key) UnitName UnitLocation UnitContactPerson (etc.) tblAircraftModels ModelID (primary key) ModelName (etc.) tblAircrafts AircraftID (primary key -- note that this could be the tail number) AircraftTailNum (if you don't use tail number as primary key) ModelID (foreign key from tblModels table) (etc.) tblParts PartID (foreign key) PartDescription (etc.) tblPersonnel PersonnelID (primary key) PersonFirstName PersonLastName PersonRank PersonSerialNum UnitID (foreign key to tblUnits) (etc.) tblWOPriorities WOPriorityNum (foreign key) WOPriorityDescription tblWorkOrders WONumber (primary key -- this would be the support work order number) AircraftID (foreign key to tblAircrafts) PersonnelID (foreign key to tblPersonnel -- this is person requesting the work) UnitWONumber FaultDescription WONotes WOPriorityNum (foreign key to tblWOPriorities) DateOpened DateAccepted DateClosed (etc.) tblWOParts WOPartsID (primary key) WONumber (foreign key to tblWorkOrders) PartID (foreign key to tblParts) WOPartQuantity (etc.) The above table structure allows the following things to be done in the database file (this prevents the entry of slightly misspelled names for the same person, for example; and it prevents the entry of invalid values for priorities, for example): 1) Use a combo box on a form in order to select the person requesting the work 2) Use a combo box on a form in order to select the aircraft to be repaired 3) Use a combo box on a form in order to select the work order priority 4) Use a combo box on a form to select the part for the work order 5) Have multiple parts for each work order 6) Track which orders are "pending" (not accepted yet), "in process" (opened but not closed), and "closed" 7) Track which persons have requested work orders 8) Track which units have requested work orders 9) Show all work orders performed on an aircraft 10) Show all work orders performed on an aircraft model (etc. etc. etc.) Your comment about the "military" date having to be "Julian". Personally, I would store dates in the database in the "normal" manner (Date/Time data type) that ACCESS uses (namely, a long integer number representing the number of days since December 30, 1899). You can always display the dates in whatever format you wish, although it's likely that you'll need a custom function to convert the normal date to the military Julian date, and vice-versa. This is a bit tricky for beginners, but very doable. The reason I recommend this is because ACCESS queries are going to expect dates to be in the normal format (e.g., "mm/dd/yyyy") in order to select data records for your reports. If you store the dates as military Julian dates, you then will need to have a function that converts the Julian date to a "mm/dd/yyyy" date just to run the query... and you'll not want to do that all the time because it will slow your queries down as you gain more data. In the above table structure, where I indicate "foreign key to xxx table", that indicates a Relationship exists between the two tables -- a "join line" in the Relationships window. Through these relationships, you can store a data value in just one table and then be able to look it up via query at any time. Thus, by putting the UnitID in the tblPersonnel, and then by using PersonnelID in tblWorkOrders, you can display the UnitName for the unit that has requested the work order through the PersonnelID link to tblPersonnel and then through the UnitID link to tblUnits. After you've had a chance to look this over, post back with questions. -- Ken Snell MS ACCESS MVP "M. Parker" wrote in message ... ok, here goes.... data i need: units, aircraft tail numbers, aircraft model, unit workorder number, part description, work requested by, serial number, fault description, priority of work order, opened date, accepted date, support workorder number, and closed date. dates need to be military julian i.e, 13 april 06 = 6114. i want to do a master table that will automatically put the info from it onto the corresponding units table. thanks for the help, you rock! matt snipped |
#15
|
|||
|
|||
Building Database for Army Helicopter Maintence Program...Need
Hi M. Parker,
Apache wrote: OTH if you decide Access you will enjoy and appreciate the application once you discover the power of Access. I have seen one of Tom Wickeraths (another Access MVP) databases and it really far surpasses anything I have ever seen, and I have been using Access for 10 years. Begin Blush I believe my friend "Apache" is talking about the Query-by-Form (QBF) technique. I alerted this friend to your post, because I think it would be a great opportunity for Apache to continue learning Access, while helping you, if he has the time available. One of the best ways to learn any subject, in my opinion, is to make an attempt to tutor others in the same subject. /End Blush In any case, if you would like to see a sample of QBF in action, send me a private e-mail message with a valid reply-to address. My e-mail address is available at the bottom of the Contributor's page, indicated in my signature below. Whatever you do, please do not post your e-mail address (or mine) to a newsgroup message. Good Luck on your project, Tom Wickerath, Microsoft Access MVP http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Apache" wrote: ULLS has never been well received. In any case you really need to decide if you want to take the time to learn database fundamentals and Access. I do not want to discourage you; however there is a steep learning curve that requires years to reach the level the Access MVP are cabable of producing. OTH you have a an excellent mentor who is willing to share his time and expertise. You should consider yourself fortunate. I am familiar with the 2405. I am an old soldier who retired years ago. I applaud your initiative and highly encourage you to pursue learning Access if for nothing else but your own knowledge for future use. You mentioned you only work with unit information. Has the Aviation field changed where they are no longer using TI's? I am sure they are still using the 1687 Sig cards. I would include this field and the date of expiration on the card. You can also send reminders to the individuals when the card will expire. This will keep you one step ahead of the inspection. I would definetly have the personnel table that Ken recommended. It is also highly recommended to sharpen your pencil you will have to design the draft on paper. Once you have this you should consult with your co-workers and some of the old timers for their input. Again if you want to learn you have an excellent mentor. It will be a long road ahead. You can build something very basic with Excel for the 2405. The down side is the reporting features as well as capabilities do not even come close to the task at hand. OTH if you decide Access you will enjoy and appreciate the application once you discover the power of Access. I have seen one of Tom Wickeraths (another Access MVP) databases and it really far surpasses anything I have ever seen, and I have been using Access for 10 years. These guys really know their craft. I can help where I can with the logistics side. Your decision, good luck. |
#16
|
|||
|
|||
Building Database for Army Helicopter Maintence Program...Need
sorry for the slow reply...work has been very hectic the past two days. ok,
now to business. work requested by would be either an aircraft tail number, or tech supply. not a single person. the serial number would either be the aircrafts full tail number, or the serial number of the single part off of the aircraft being repaired. the downfall of the single part is that i won't have the serial number until the part is in my hand, so i couldn't make a table of parts..or at least as far as i can tell, i can't. for the "business rule" of it, units make request. the tail number of the aircraft, or if it is tech supply, lets me know the unit. i am thinking, maybe a table for each company with the tail numbers, or tech supply, as a field? the one part per workorder is a correct assumption. those are pretty much the rules. the data i need remain the same, minus the corrections i made in this post. units(a;b;c;d co's), aircraft tail numbers, aircraft model(uh-60 or ch-47), unit workorder number(workorder number we generate with each request), part description(what the part is or what type of aircraft it is), work requested by( being either tail number or tech supply), serial number(being either the single parts number or the aircrafts full serial number), fault description(what the issue is), priority of work order(high or normal priority), opened date, accepted date, support workorder number(work order number the supportng unit generates), and closed date. i hope this helps clarify things. once again, i really appreciate the patience. "Ken Snell (MVP)" wrote: The tables that I suggest are based on what I am understanding (ok, inferring grin ) from your list of data that you want to store. Your list included work requested by and serial number. I was assuming that these two items refer to a person who is making the request, and the serial number of that person. If this is not correct, please clarify what these two items are. The data design that I'm suggesting is using what are called "lookup" tables -- a table that stores unique data that will be used throughout the database -- e.g., tblUnits. In order to enter data into them, typically you'd create a form that is based on that table and then you can enter new records and edit existing ones. No one form should be considered for all the tables that I suggest. However, you will be able to use a single form for entering records into tblWorkOrders table -- and, if you were to use it, tblWOParts could be populated using a subform within this form. Note that my suggestions are just that -- suggestions, based on my interpretation of what you listed. I assume that a repair work order may involve more than just one part for the repair, which is why I suggest a separate "child" table for the parts used on a work order. From you list of data items, and from my suggested table structure, you now can make decisions about what the database will actually hold, and what will be the "business" rules for it (e.g, only one part per work order; no person makes a request for a work order but instead a unit makes a request; etc.). Creating a database should always involve a lot of upfront paper/pencil time as you consider the data, the characteristics, the future needs, etc. -- only after that should one begin to create the database. We're still in the "paper/pencil" mode -- we're hampered a bit by the fact that our communications are via this written medium, so our questions/answers will go back and forth without the benefit of seeing "live" the types of things you already have or want to do. Doing the design on paper first is more likely to produce a workable database for reports, entry, etc. So, consider the business rules and the data again, and then let's determine the items that should be stored so that you can use them in reports later on. -- Ken Snell MS ACCESS MVP "M. Parker" wrote in message ... OK- after looking at what you suggested, i have some questions. 1: how will i be able to us this to enter info into a form and have it sent to all of the correct tables? 2: the i'm not sue what to do with the personell table. the work i track is requested by units, not people. 3: with the workorders table, i can enter the info i have now, but what about future info that needs to go in. hence, my desire for a single form to send out the info. 4: the tables parts and w/o parts, i am not sure what to do with them, b/c there are way too many parts to list and i don't even know what they all are. thanks for the patience. "Ken Snell (MVP)" wrote: OK - we're going to approach this as a database, not as a "souped-up" spreadsheet g . From the data list that you've posted, you should create the following tables: tblUnits UnitID (primary key) UnitName UnitLocation UnitContactPerson (etc.) tblAircraftModels ModelID (primary key) ModelName (etc.) tblAircrafts AircraftID (primary key -- note that this could be the tail number) AircraftTailNum (if you don't use tail number as primary key) ModelID (foreign key from tblModels table) (etc.) tblParts PartID (foreign key) PartDescription (etc.) tblPersonnel PersonnelID (primary key) PersonFirstName PersonLastName PersonRank PersonSerialNum UnitID (foreign key to tblUnits) (etc.) tblWOPriorities WOPriorityNum (foreign key) WOPriorityDescription tblWorkOrders WONumber (primary key -- this would be the support work order number) AircraftID (foreign key to tblAircrafts) PersonnelID (foreign key to tblPersonnel -- this is person requesting the work) UnitWONumber FaultDescription WONotes WOPriorityNum (foreign key to tblWOPriorities) DateOpened DateAccepted DateClosed (etc.) tblWOParts WOPartsID (primary key) WONumber (foreign key to tblWorkOrders) PartID (foreign key to tblParts) WOPartQuantity (etc.) The above table structure allows the following things to be done in the database file (this prevents the entry of slightly misspelled names for the same person, for example; and it prevents the entry of invalid values for priorities, for example): 1) Use a combo box on a form in order to select the person requesting the work 2) Use a combo box on a form in order to select the aircraft to be repaired 3) Use a combo box on a form in order to select the work order priority 4) Use a combo box on a form to select the part for the work order 5) Have multiple parts for each work order 6) Track which orders are "pending" (not accepted yet), "in process" (opened but not closed), and "closed" 7) Track which persons have requested work orders 8) Track which units have requested work orders 9) Show all work orders performed on an aircraft 10) Show all work orders performed on an aircraft model (etc. etc. etc.) Your comment about the "military" date having to be "Julian". Personally, I would store dates in the database in the "normal" manner (Date/Time data type) that ACCESS uses (namely, a long integer number representing the number of days since December 30, 1899). You can always display the dates in whatever format you wish, although it's likely that you'll need a custom function to convert the normal date to the military Julian date, and vice-versa. This is a bit tricky for beginners, but very doable. The reason I recommend this is because ACCESS queries are going to expect dates to be in the normal format (e.g., "mm/dd/yyyy") in order to select data records for your reports. If you store the dates as military Julian dates, you then will need to have a function that converts the Julian date to a "mm/dd/yyyy" date just to run the query... and you'll not want to do that all the time because it will slow your queries down as you gain more data. In the above table structure, where I indicate "foreign key to xxx table", that indicates a Relationship exists between the two tables -- a "join line" in the Relationships window. Through these relationships, you can store a data value in just one table and then be able to look it up via query at any time. Thus, by putting the UnitID in the tblPersonnel, and then by using PersonnelID in tblWorkOrders, you can display the UnitName for the unit that has requested the work order through the PersonnelID link to tblPersonnel and then through the UnitID link to tblUnits. After you've had a chance to look this over, post back with questions. -- Ken Snell MS ACCESS MVP "M. Parker" wrote in message ... ok, here goes.... data i need: units, aircraft tail numbers, aircraft model, unit workorder number, part description, work requested by, serial number, fault description, priority of work order, opened date, accepted date, support workorder number, and closed date. dates need to be military julian i.e, 13 april 06 = 6114. i want to do a master table that will automatically put the info from it onto the corresponding units table. thanks for the help, you rock! matt snipped |
#17
|
|||
|
|||
Building Database for Army Helicopter Maintence Program...Need
My turn to apologize for slow answering... have been tied up all day and
night on work duties, so it'll be tomorrow night at the earliest when I can reply. Stay tuned.... g -- Ken Snell MS ACCESS MVP "M. Parker" wrote in message ... sorry for the slow reply...work has been very hectic the past two days. ok, now to business. work requested by would be either an aircraft tail number, |
#18
|
|||
|
|||
Building Database for Army Helicopter Maintence Program...Need
My apologies again... ran completely out of hours tonite. I'll be back as
soon as possible..... -- Ken Snell MS ACCESS MVP "M. Parker" wrote in message ... sorry for the slow reply...work has been very hectic the past two days. ok, now to business. work requested by would be either an aircraft tail number, |
#19
|
|||
|
|||
Building Database for Army Helicopter Maintence Program...Need
OK - So what you want is the ability to store the unit number and name, and
the serial number (is that the same as the tail number?) of the airplane. And you don't need to store information about any personnel assigned to a unit. Also, you're not maintaining a table of parts, but we can still capture information about the part that is being repaired for the database. Then, I would modify the table structure that I'd proposed just a bit: tblUnits UnitID (primary key) UnitName UnitLocation UnitContactPerson (etc.) tblAircraftModels ModelID (primary key) ModelName (etc.) tblAircrafts AircraftID (primary key -- note that this could be the tail number or the serial number) AircraftTailNum (if you don't use tail number as the primary key) AircraftSerialNum (if this is not the same thing as the tail number, and if you don't use serial number as the primary key) ModelID (foreign key from tblModels table) (etc.) tblWOPriorities WOPriorityNum (foreign key) WOPriorityDescription tblWorkOrders WONumber (primary key -- this would be the support work order number) AircraftID (foreign key to tblAircrafts) UnitID (foreign key to tblUnits -- this is unit requesting the work) UnitWONumber FaultDescription WONotes WOPriorityNum (foreign key to tblWOPriorities) DateOpened DateAccepted DateClosed (etc.) tblWOParts (allows multiple parts for each work order) WOPartsID (primary key) WONumber (foreign key to tblWorkOrders) PartDescription (memo field to allow free-form text entry of information about the part; this can be split into separate fields if you know the specific details that you want to store about each part that is being repaired for the work order) (etc.) How does this look? -- Ken Snell MS ACCESS MVP "M. Parker" wrote in message ... sorry for the slow reply...work has been very hectic the past two days. ok, now to business. work requested by would be either an aircraft tail number, or tech supply. not a single person. the serial number would either be the aircrafts full tail number, or the serial number of the single part off of the aircraft being repaired. the downfall of the single part is that i won't have the serial number until the part is in my hand, so i couldn't make a table of parts..or at least as far as i can tell, i can't. for the "business rule" of it, units make request. the tail number of the aircraft, or if it is tech supply, lets me know the unit. i am thinking, maybe a table for each company with the tail numbers, or tech supply, as a field? the one part per workorder is a correct assumption. those are pretty much the rules. the data i need remain the same, minus the corrections i made in this post. units(a;b;c;d co's), aircraft tail numbers, aircraft model(uh-60 or ch-47), unit workorder number(workorder number we generate with each request), part description(what the part is or what type of aircraft it is), work requested by( being either tail number or tech supply), serial number(being either the single parts number or the aircrafts full serial number), fault description(what the issue is), priority of work order(high or normal priority), opened date, accepted date, support workorder number(work order number the supportng unit generates), and closed date. i hope this helps clarify things. once again, i really appreciate the patience. snipped |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I Link an XL Program to a Access Database | kwozy | Database Design | 1 | February 7th, 2006 05:57 AM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
stop access program database from growing | doglover | Database Design | 2 | August 24th, 2004 11:00 PM |
What is MDE | Charlie | General Discussion | 4 | August 24th, 2004 04:15 PM |
Database for Church - Childrens Program | RTforNewHope | General Discussion | 3 | August 17th, 2004 02:47 AM |