Math in Table?

[Keith]

I'm in the middle of converting what is essentially an
Excel database into an Access db. I work in a production
facility, and the supervisors need to be able to access
data easily, as they have limited computer skills.
I have had them enter data in a basic spreadsheet, and
then I created macros such that they only had to click a
button and the data was automatically pulled from the
sheet and dumped into a separate tab to be stored.

We have 3 shifts that need to be entered daily. We have
12 production lines that need to be tracked
individually. The key components of each line are:
product code, units scheduled, units packed, unit weight,
scheduled pounds, pounds packed, labor hours, pounds per
labor hour, and % acheived.

I think I am going to create a separate table for each
production line... does that sound like a good idea?
I would like for the tables to automatically do the
calculations for the fields "scheduled pounds" (units
scheduled*unit weight), "pounds per labor hour" (pounds
packed/labor hours), etc. Can this be done in a table,
or do I need for it to be done in a query or report
(which would be better?). Bottom line, I don't want
those entering the data to have to type in these simple
values, and I would like something that I can print out
with all of this info.
I'm new to Access. We will need reports for each PC by
day... how do I set up a query or report to select the
correct three rows (one for each shift) automatically?

Thank you for taking the time to help me. I appreciate
it!

 

[John Vinson]

I'm in the middle of converting what is essentially an
Excel database into an Access db.

Excel is a spreadsheet, an excellent one. Access is a relational
database. THEY ARE DIFFERENT! Access is not a "big version" of Excel,
and applying spreadsheet logic will cause you all sorts of grief.

I work in a production
facility, and the supervisors need to be able to access
data easily, as they have limited computer skills.
I have had them enter data in a basic spreadsheet, and
then I created macros such that they only had to click a
button and the data was automatically pulled from the
sheet and dumped into a separate tab to be stored.

Very appropriate design for a spreadsheet; dreadfully non-normalized
for a database. Instead, the users should enter data using a Form into
a single table, which will get very large but which can be easily
separated out using QUeries.

We have 3 shifts that need to be entered daily. We have
12 production lines that need to be tracked
individually. The key components of each line are:
product code, units scheduled, units packed, unit weight,
scheduled pounds, pounds packed, labor hours, pounds per
labor hour, and % acheived.

I think I am going to create a separate table for each
production line... does that sound like a good idea?

No, it does not. One big table with additional fields for the date,
for the shift, and for the production line.

I would like for the tables to automatically do the
calculations for the fields "scheduled pounds" (units
scheduled*unit weight), "pounds per labor hour" (pounds
packed/labor hours), etc. Can this be done in a table,
or do I need for it to be done in a query or report
(which would be better?).

In a Query; you can base the Report on that query. You cannot and
should not do calculations in a Table.

Bottom line, I don't want
those entering the data to have to type in these simple
values, and I would like something that I can print out
with all of this info.
I'm new to Access. We will need reports for each PC by
day... how do I set up a query or report to select the
correct three rows (one for each shift) automatically?

With a Query with criteria on the date. It depends on just how you
have your tables set up.

 

[Duane Hookom]

Don't create a separate table for each production line. Create one table
with a field for Line. You don't do the math in tables. It is quite easy to
display totals or calculations in queries where you create calculated
columns.

There are lots of methods to filter results/records returned in a query,
report, or form.

 

[david epsom dot com dot au]

Keith,

Your first attempt at reporting will probably give you
more rows than you want, and fewer columns: because you
will enter all lines and all shifts into one table, you
will naturally get all lines and all shifts in one big
list.

Don't worry! That is still the best way to do it. It will
require more effort on the query/report side if you want
to put shifts or lines in parallel columns, but it is a
much more flexible and dependable design.

Firstly, you can query separately for each shift or line,
then join up the queries on shift or line to show shift
or line as separate fields.

Secondly, you can use calculated fields in a query or report
to move data into different columns

Thirdly, you can use a crosstab object or query to change
rows to columns

And although you can also go the other way (you can join
little tables together into one big list to get your global
totals), you normally find it is more work dealing with
each of the 12 lines separately, than it would have been
if you had put all 12 lines into one table and just worked
on getting stuff working right with that.

(david)