Adding Fields in a Form

Alternatively, if you're determined to use the table rather than a query,
you can set the ControlSource of a text box on your report to the same
calculation you used on the form.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Douglas J. Steele" wrote in message
...
Base the report on the query, not the table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Tommy2326" wrote in message
...
Im going to use this calculated value as part of a report. the report
will
show total production levels for each machine. The form has the machine
number entered, stored to table, hourly production levels, 06-07, 07-08
etc,
stored to table. The way i was thinking about doing the report was by
linking it to the table, how do i get the calculated value included in
this?

"Douglas J. Steele" wrote:

There's no need to store the total.

As fellow MVP John Vinson likes to say "Storing derived data such as
this in
your table accomplishes three things: it wastes disk space; it wastes
time
(almost any calculation will be MUCH faster than a disk fetch); and most
importantly, it risks data corruption. If one of the underlying fields
is
subsequently edited, you will have data in your table WHICH IS WRONG,
and no
automatic way to detect that fact."

SImply add a computed field in a query that figures out the date, and
use
the query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Tommy2326" wrote in message
...
Thanks got that working now. I'm trying to store the value from the
calculation in a table. The table is called Data - Production Levels,
field
name is Total. How do I do this?

"Douglas J. Steele" wrote:

Is the result supposed to be a long integer? If so, try:

=CLng([06-07])+CLng([07-08])+CLng([08-09])+CLng([09-10])+CLng([10-11])+CLng([11-12])+CLng([12-13])+CLng([13-14])+CLng([14-15])+CLng([15-16])+CLng([16-17])+CLng([17-18])

If it's supposed to be some other type (Single, Double, etc), replace
CLng
with the appropriate function (CSng, CDbl, etc.)

Actually, if there's a chance that one or more of the text boxes
might be
empty, you should probably use

=CLng(Nz([06-07], 0))+CLng(Nz([07-08], 0))+CLng(Nz([08-09],
0))+CLng(Nz([09-10], 0))+CLng(Nz([10-11], 0))+CLng(Nz([11-12],
0))+CLng(Nz([12-13], 0))+CLng(Nz([13-14], 0))+CLng(Nz([14-15],
0))+CLng(Nz([15-16], 0))+CLng(Nz([16-17], 0))+CLng(Nz([17-18], 0))

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Tommy2326" wrote in message
...
text fields

"scubadiver" wrote:


Are these 12 fields text fields or number fields?

--

The 11th day of every month:

http://truthaction.org/forum/index.php


"Tommy2326" wrote:

I'm trying to add the numbers from 12 fields in a form. The
formula
im
using
is:

=[06-07]+[07-08]+[08-09]+[09-10]+[10-11]+[11-12]+[12-13]+[13-14]+[14-15]+[15-16]+[16-17]+[17-18]

Rather than displayin the the sum of these fields I am getting
the
number
from each field output, ie, 101010101010101010101010.
What is wrong with my formula?

Thanks for the help

Tommy

Thanks, got the reports working now, used the query.

"Douglas J. Steele" wrote:

Alternatively, if you're determined to use the table rather than a query,
you can set the ControlSource of a text box on your report to the same
calculation you used on the form.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Douglas J. Steele" wrote in message
...
Base the report on the query, not the table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Tommy2326" wrote in message
...
Im going to use this calculated value as part of a report. the report
will
show total production levels for each machine. The form has the machine
number entered, stored to table, hourly production levels, 06-07, 07-08
etc,
stored to table. The way i was thinking about doing the report was by
linking it to the table, how do i get the calculated value included in
this?

"Douglas J. Steele" wrote:

There's no need to store the total.

As fellow MVP John Vinson likes to say "Storing derived data such as
this in
your table accomplishes three things: it wastes disk space; it wastes
time
(almost any calculation will be MUCH faster than a disk fetch); and most
importantly, it risks data corruption. If one of the underlying fields
is
subsequently edited, you will have data in your table WHICH IS WRONG,
and no
automatic way to detect that fact."

SImply add a computed field in a query that figures out the date, and
use
the query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Tommy2326" wrote in message
...
Thanks got that working now. I'm trying to store the value from the
calculation in a table. The table is called Data - Production Levels,
field
name is Total. How do I do this?

"Douglas J. Steele" wrote:

Is the result supposed to be a long integer? If so, try:

=CLng([06-07])+CLng([07-08])+CLng([08-09])+CLng([09-10])+CLng([10-11])+CLng([11-12])+CLng([12-13])+CLng([13-14])+CLng([14-15])+CLng([15-16])+CLng([16-17])+CLng([17-18])

If it's supposed to be some other type (Single, Double, etc), replace
CLng
with the appropriate function (CSng, CDbl, etc.)

Actually, if there's a chance that one or more of the text boxes
might be
empty, you should probably use

=CLng(Nz([06-07], 0))+CLng(Nz([07-08], 0))+CLng(Nz([08-09],
0))+CLng(Nz([09-10], 0))+CLng(Nz([10-11], 0))+CLng(Nz([11-12],
0))+CLng(Nz([12-13], 0))+CLng(Nz([13-14], 0))+CLng(Nz([14-15],
0))+CLng(Nz([15-16], 0))+CLng(Nz([16-17], 0))+CLng(Nz([17-18], 0))

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Tommy2326" wrote in message
...
text fields

"scubadiver" wrote:


Are these 12 fields text fields or number fields?

--

The 11th day of every month:

http://truthaction.org/forum/index.php


"Tommy2326" wrote:

I'm trying to add the numbers from 12 fields in a form. The
formula
im
using
is:

=[06-07]+[07-08]+[08-09]+[09-10]+[10-11]+[11-12]+[12-13]+[13-14]+[14-15]+[15-16]+[16-17]+[17-18]

Rather than displayin the the sum of these fields I am getting
the
number
from each field output, ie, 101010101010101010101010.
What is wrong with my formula?

Thanks for the help

Tommy

On Wed, 15 Aug 2007 06:02:04 -0700, Tommy2326
wrote:

The form has the machine
number entered, stored to table, hourly production levels, 06-07, 07-08 etc,
stored to table.

That's another problems, as Roy suggested!

Storing data (a time range) in fieldnames IS INCORRECT design.
Storing numeric data in text fields is a bad idea too.

I'd strongly suggest having one *RECORD* per hour, rather than one field per
hour, in a table structure such as

MachineNumber link to table of machines
TimeStart Date/Time e.g. #8/15/2007 10:00:00
Production Long Integer, Double, or Decimal number as appropriate

You can then use a Totals query to sum up production for all machines over a
date/time range, or to sum up all timesteps for a single machine, or any other
combination. Your "wide-flat" design is much less flexible!

John W. Vinson [MVP]

The data being stored is not a time range, it is the number of units
produced, i read somewhere that storing data as text, even if it is a number,
will take up less space as you can specify the number of characters, in this
case three. Having one record per hour would result in 168 records being
created everyday

"John W. Vinson" wrote:

On Wed, 15 Aug 2007 06:02:04 -0700, Tommy2326
wrote:

The form has the machine
number entered, stored to table, hourly production levels, 06-07, 07-08 etc,
stored to table.

That's another problems, as Roy suggested!

Storing data (a time range) in fieldnames IS INCORRECT design.
Storing numeric data in text fields is a bad idea too.

I'd strongly suggest having one *RECORD* per hour, rather than one field per
hour, in a table structure such as

MachineNumber link to table of machines
TimeStart Date/Time e.g. #8/15/2007 10:00:00
Production Long Integer, Double, or Decimal number as appropriate

You can then use a Totals query to sum up production for all machines over a
date/time range, or to sum up all timesteps for a single machine, or any other
combination. Your "wide-flat" design is much less flexible!

John W. Vinson [MVP]

On Thu, 16 Aug 2007 18:14:12 -0700, Tommy2326
wrote:

The data being stored is not a time range, it is the number of units
produced, i read somewhere that storing data as text, even if it is a number,
will take up less space as you can specify the number of characters, in this
case three.

Read my suggestion again.

Your fieldname [06-07] IS DATA. It's data being stored in a fieldname where
data should *not* be stored, rather than in a field in a table where it
*should*.

Having one record per hour would result in 168 records being
created everyday

Absolutely. What's wrong with that? You're storing the 168 records in a way
that lets you search and sort them correctly.

If you're worried about the table getting too big, be aware that Access can
handle 10,000,000 record tables quite nicely, with proper design.

John W. Vinson [MVP]

This is the first time ive used access for anything like this. How would I
produce reports for production levels on each machine and over a shift with
the information being spread over lots of different records rather than just
one? There are daily, weekly and monthly reports.

"John W. Vinson" wrote:

On Thu, 16 Aug 2007 18:14:12 -0700, Tommy2326
wrote:

The data being stored is not a time range, it is the number of units
produced, i read somewhere that storing data as text, even if it is a number,
will take up less space as you can specify the number of characters, in this
case three.

Read my suggestion again.

Your fieldname [06-07] IS DATA. It's data being stored in a fieldname where
data should *not* be stored, rather than in a field in a table where it
*should*.

Having one record per hour would result in 168 records being
created everyday

Absolutely. What's wrong with that? You're storing the 168 records in a way
that lets you search and sort them correctly.

If you're worried about the table getting too big, be aware that Access can
handle 10,000,000 record tables quite nicely, with proper design.

John W. Vinson [MVP]

You can use a cross-tab query to present your data in the same way you're
currently storing it.

That way, you get the best of both worlds: the data is stored correctly so
that you can manipulate it as easily as you need to, but you can still
present it as desired.

And just in case you need more ammunition for why to store it John's way
rather than your way, write me a query to tell me how many machines produced
fewer than n units an hour for 3 consecutive hours. It's pretty
straight-forward to do it using John's approach.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Tommy2326" wrote in message
...
This is the first time ive used access for anything like this. How would
I
produce reports for production levels on each machine and over a shift
with
the information being spread over lots of different records rather than
just
one? There are daily, weekly and monthly reports.

"John W. Vinson" wrote:

On Thu, 16 Aug 2007 18:14:12 -0700, Tommy2326
wrote:

The data being stored is not a time range, it is the number of units
produced, i read somewhere that storing data as text, even if it is a
number,
will take up less space as you can specify the number of characters, in
this
case three.

Read my suggestion again.

Your fieldname [06-07] IS DATA. It's data being stored in a fieldname
where
data should *not* be stored, rather than in a field in a table where it
*should*.

Having one record per hour would result in 168 records being
created everyday

Absolutely. What's wrong with that? You're storing the 168 records in a
way
that lets you search and sort them correctly.

If you're worried about the table getting too big, be aware that Access
can
handle 10,000,000 record tables quite nicely, with proper design.

John W. Vinson [MVP]