Sugguestions on new db design

[Cam]

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.

 

[Duane Hookom]

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.

 

[Tony Toews [MVP]]

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/

 

[Cam]

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]]

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/