forms and "archiving"

  • Thread starter Thread starter Michael C via AccessMonster.com
  • Start date Start date
M

Michael C via AccessMonster.com

I have a production form where there is one record per date. Each record
consists of 7 different production lines, with several listed products per
line, and their perspective weights and measures. I currently have all this
recorded on one table. The problem arises when I want to run a query
summarizing for example all products run for a certain line for a given time
frame. Since all of the lines data for one date is on one line, I am having
problems seperating the data for individual queries.

What am I missing? Is my overall structure wrong, or is there a way to write
a query that will seperate the recorded data?

Thanks,

Perry
 
While you haven't really given that much detail about your table, from the
sounds of it, it's wrong.

It sounds as though you have fields with the production line and product
built into their names: Line1ProdA, Line1ProdB, Line2Prod1 and so on.

If that's the case, you're storing data in the names of the fields. Rather
than:

Date Line1ProdA Line1ProdB Line2ProdA Line2ProdB
2005-12-10 1 5 18
10

you should have:

Date Line Prod Quantity
2005-12-10 1 A 1
2005-12-10 1 B 5
2005-12-10 2 A 18
2005-12-10 2 B 10

- -
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
(same writer, additional question...)
Would a union query be able to seperate all the data? Say create one union
query per each production line and product per line, which would mean having
about about 35 union queries which i would access with another seperate query
that would access the data based on what results the user wants to see...

Perry
 
The table is structured similiar to your not-to-do example: Below is
regarding the north production line, which runs approx 5 products a day, each
product having certain numbers that need to be recorded:

ID# Date northProd1 NorthLbl1 NorthTgt1 NorthGrand1
NorthProduced1 NorthMLL1 NorthProd2...


There are 6 other lines with the same info needed. These columns really add
up as there are approx 7 production lines total, 2-5 products running per
line per day, and I want to record all data for one record (one day) on one
page. This comes to nearly 200 columns of data. I could break it down as
one actual product per line as you stated, but that would really rack up the
records.

Is the best recommendation the Survey database? If so, would I need to
create 7 seperate subforms to incorporate the 7 different production lines
data?

Perry
How about posting the details about what the table looks like!
(same writer, additional question...)
Would a union query be able to seperate all the data? Say create one
Click to expand...
[quoted text clipped - 25 lines]
Click to expand...
 
The table is structured similiar to your not-to-do example: Below is
regarding the north production line, which runs approx 5 products a day, each
product having certain numbers that need to be recorded:

ID# Date northProd1 NorthLbl1 NorthTgt1 NorthGrand1
NorthProduced1 NorthMLL1 NorthProd2...


There are 6 other lines with the same info needed. These columns really add
up as there are approx 7 production lines total, 2-5 products running per
line per day, and I want to record all data for one record (one day) on one
page. This comes to nearly 200 columns of data. I could break it down as
one actual product per line as you stated, but that would really rack up the
records.
Click to expand...

This is a good spreadsheet... but a badly non-normalized table.
Storing data in fieldnames, as you are doing, is NEVER a good idea.

A much better design would be to have a "tall-thin" table with fields
like

ID#
ProductionDate <don't use Date as a fieldname, it's reserved>
ProductionLine <e.g. North>
ProductID <I presume that the 1 is first product, 2 second...?>
Quantity

I don't understand the "Prod", "Lbl", "Tgt", "Grand", "Produced",
"MLL" field names - but if any of them refer to calculated fields (is
Grand the grand total?) then they should NOT EXIST in your table;
calculate them on the fly instead.

John W. Vinson[MVP]
 
I can do the "tall thin" structure. The only problem left is that I want to
record all 30 products run that day on one form at one time. Would this
require the use of multiple subforms similiar to your survey database example?


Also, all of those other fields are just label and target weights pre-
calculated through a different program. My form is simply for data entry and
retrieval.

Perry


John said:
The table is structured similiar to your not-to-do example: Below is
regarding the north production line, which runs approx 5 products a day, each
Click to expand...
[quoted text clipped - 10 lines]
one actual product per line as you stated, but that would really rack up the
records.
Click to expand...

This is a good spreadsheet... but a badly non-normalized table.
Storing data in fieldnames, as you are doing, is NEVER a good idea.

A much better design would be to have a "tall-thin" table with fields
like

ID#
ProductionDate <don't use Date as a fieldname, it's reserved>
ProductionLine <e.g. North>
ProductID <I presume that the 1 is first product, 2 second...?>
Quantity

I don't understand the "Prod", "Lbl", "Tgt", "Grand", "Produced",
"MLL" field names - but if any of them refer to calculated fields (is
Grand the grand total?) then they should NOT EXIST in your table;
calculate them on the fly instead.

John W. Vinson[MVP]
Click to expand...
 
I can do the "tall thin" structure. The only problem left is that I want to
record all 30 products run that day on one form at one time. Would this
require the use of multiple subforms similiar to your survey database example?
Click to expand...

Well... it's not MY survey database.
Also, all of those other fields are just label and target weights pre-
calculated through a different program. My form is simply for data entry and
retrieval.
Click to expand...

I'd suggest running an Append query, then, and not using the form for
this purpose at all. Can you import or link to the file generated by
the other program?

John W. Vinson[MVP]
 
I wish. It's on a completely different network that isn't compatible with
ours. Different OS...
I could break up the original table into seperate tables by line, create
subforms that link to those tables, stick them all on one form and then
query them all together for reporting.

Thank you for your help and suggestions,

Perry
 
I wish. It's on a completely different network that isn't compatible with
ours. Different OS...
I could break up the original table into seperate tables by line, create
subforms that link to those tables, stick them all on one form and then
query them all together for reporting.
Click to expand...

YOW. That would NOT seem to make any sense. Why subforms at all? Forms
and subforms are for human interaction; it appears that you want to do
this in an automated way. Why one *table* per line? It should be one
*record* per line.

Can you export the data to a comma-separated or fixed-width Text file
(which can be imported readily)? What is in fact the nature of the
data?

John W. Vinson[MVP]
 
Actually the form I want is for human interaction. Since we have no way to
interact with our vendor program that generates weight values, we have to
rely on paper reports, which numbers we take and re-input into forms that
will give us the numbers WE want. Granted, not the most efficient way of
doing things, but it's less expensive to have the vendors program do most of
the work...

I was trying to get ideas from the survey link you provided at:
http://www.rogersaccesslibrary.com/duanehookom/duanehookom.htm
where they used subforms.

The data we are recording is used for quality compliance at a food processing
plant, mainly for internal auditing purposes. The employee weighs product 6x
every half hour on a scale, which records the data in the vendor program. At
the end of the day, the program takes all the weighs and calculates grand
averages, which products didn't hit minimum legal weights (mll), how close
they were to target and label weights, and generates a paper report which we
get.
We just want to expand on what our vendor program will not do. We just want
to create something that will summarize daily data on each product run, their
grand average weight results, target and label weight averages, and from that
determine the amount of money lost or gained that day. Granted, If I could
import that data from the canned program, all problems would be solved.

My idea of one record is all results from all lines for one given day. Since
putting that on one table would not help with reporting, I was thinking of
your "thin table" idea, by dividing the data into simpler tables that are
all populating from the same form, so that querying and reporting will be
simpler. It seems that you can't shove data from a form into more than one
table normally, unless I'm missing something.

Is that making sense, or am I just going around in circles with this?
 
Back
Top