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
|
|||
|
|||
Sugguestions on new db design
Hello,
I am designing an Access database and want some experts opinion & sugguestions. The database will be used to record/track daily production activies data such as passdown info between shifts, machine issues, manpower issues, etc in one database to be stored in a network server. I need sugguestions on how to link everything together so I am able to pull up reports in any criteria such as filter the data and show all the activties (machine, manpower, etc..) for that date range or filter by machine number, etc... Should I create one table for machine issue, manpower issue, passdown, etc.. or one table to include all with one field to select machine issue or manpower, etc..? Thank you. |
#2
|
|||
|
|||
Sugguestions on new db design
I recently created a passdown notes system for the facility where I work. I
think it is much better to create this through an intranet but the tables would be similar with Access. I put all the notes in a single table. There are fields for shift, date, author, location, and comments/notes. I use the location field for production line or all lines (factory wide). I don't find it necessary to include a field for machine. The users can search on any text within the comments or by date or location. I also keep a table of employees and another of employee involvement. The employee involvement table joins specific employees with one or more location. That way, when an employee loads the web page, they see only the locations they are interested in. They of course can search across all locations. -- Duane Hookom Microsoft Access MVP "Cam" wrote: Hello, I am designing an Access database and want some experts opinion & sugguestions. The database will be used to record/track daily production activies data such as passdown info between shifts, machine issues, manpower issues, etc in one database to be stored in a network server. I need sugguestions on how to link everything together so I am able to pull up reports in any criteria such as filter the data and show all the activties (machine, manpower, etc..) for that date range or filter by machine number, etc... Should I create one table for machine issue, manpower issue, passdown, etc.. or one table to include all with one field to select machine issue or manpower, etc..? Thank you. |
#3
|
|||
|
|||
Sugguestions on new db design
Cam wrote:
Should I create one table for machine issue, manpower issue, passdown, etc.. or one table to include all with one field to select machine issue or manpower, etc..? Thank you. One of my mottos is "You can never have enough tables." smile So you will want tables for machines and employees. I'm not sure if I'd want one table for both machine issues and manpower issues or two tables. It depends on how much data is common to both and would you generally want one report showing you both machine and manpower issues. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
#4
|
|||
|
|||
Sugguestions on new db design
Tony,
There are approx. 6-10 fields for each table and usually about 2-3 fields are common fields. Yes, I do want to see one report filtered by date to show all machine & manpower issue and passdown, etc... Question is then if I create one table for each of these categories, then how do I tie then together so I am able to create variety of reports based on date, machine issue, passdown, etc.? Thank you. "Tony Toews [MVP]" wrote: Cam wrote: Should I create one table for machine issue, manpower issue, passdown, etc.. or one table to include all with one field to select machine issue or manpower, etc..? Thank you. One of my mottos is "You can never have enough tables." smile So you will want tables for machines and employees. I'm not sure if I'd want one table for both machine issues and manpower issues or two tables. It depends on how much data is common to both and would you generally want one report showing you both machine and manpower issues. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
#5
|
|||
|
|||
Sugguestions on new db design
Cam wrote:
There are approx. 6-10 fields for each table and usually about 2-3 fields are common fields. Yes, I do want to see one report filtered by date to show all machine & manpower issue and passdown, etc... Question is then if I create one table for each of these categories, then how do I tie then together so I am able to create variety of reports based on date, machine issue, passdown, etc.? You can use a UNION query to combine multiple similar tables. But it can be a pain in the you know what. So I'd be seriously considering an "Issues and Passdowns" table. And if some records only have machines on them and others only have manpower then that's just fine. This also simplifies the data entry somewhat as now the user can sit in front of one form and enter data without having to switch between multiple forms. For example I will commonly use an InventoryTransactions table. This will be used for all transactions involving inventory such as Request for Quote, Purchase Order, Receipt, Issuing, Adjustments, whatever. Some fields will be used and some won't depending on the transaction type. In turn this makes inquiry and reporting much easier. So the more I think about it use one table. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
Thread Tools | |
Display Modes | |
|
|