how do i find the min of a row?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DanD - 30 Oct 2005 04:59 GMT

I am trying to make a simple data base for my work. We do everything on paper
and it would be easier to do it on a computer. What we do is measure the
gauge of film in milimeters. What we do is take 5 sample readings of each
roll of film we make. For instance, if we were making a gauge of 2.0, it
would mic out at (2.1 ,2.2, 1.9, 2.1, 2.0, 1.8 ). From those 5 numbers, i
need the sum which i know how to get. then the average, which is the sum / 5,
and the range. The range is the bigest number (2.2) minus the smallest number
(1.8). What i want to know is how to get the range in access, if field 1 is
1x, field 2 is 2x, field 3 is 3x and so on. (1x, 2x, and 3x....) are just
field names.
P.S
I don't want to use a excel spreadsheet format.

Reply to this Message

Ed Warren - 30 Oct 2005 05:27 GMT

You need to change your table structure.

Your current table is in the form:

1x, 2x, 3x, 4x, 5x

You need to have a table (table1)
with one and only one entry for each film roll made.

Table1
FilmID (key) (FilmDate), ...... other stuff about the film
1 10/29/2005
2 10/29/2005

Table2
MeasurementID FilmID Measurement
1 1 2.1
2 1 2.2
3 1 1.9
4 1 2.1
5 1 2.0
6 1 1.8
7 2 2.0
8 2 3.0
etc.

Table1 should be linked using the relationships using a 1:many relationship

Now you can find the Max, Min, Average, and Range for film #1 using a
'groupby' query.

Ed Warren.

Then a table (table2) with an entry for each observation, and a key
referring back to the film roll

>I am trying to make a simple data base for my work. We do everything on
>paper
[quoted text clipped - 12 lines]
> P.S
> I don't want to use a excel spreadsheet format.

Reply to this Message

Tom Lake - 30 Oct 2005 05:57 GMT

>I am trying to make a simple data base for my work. We do everything on
>paper
> and it would be easier to do it on a computer. What we do is measure the
> gauge of film in milimeters. What we do is take 5 sample readings of each
> roll of film we make. For instance, if we were making a gauge of 2.0, it
> would mic out at (2.1 ,2.2, 1.9, 2.1, 2.0, 1.8 ). From those 5 numbers, i

Five numbers? I count six! My example will use the first five.

> need the sum which i know how to get. then the average, which is the sum /
> 5,
[quoted text clipped - 6 lines]
> P.S
> I don't want to use a excel spreadsheet format.

It's better NOT to put them all in the same row.

Try using two tables. The first field in the first table is the RollID
(Primary Key) and any info about the roll that doesn't
vary such as a lot#, date of manufacture, etc.

The next table would consist of the RollID (an index but NOT a Primary Key)
and the gauge. The relationship between the tables is one Roll Info Table
record to many Gauge Table records.

Here's the SQL for a query that will give you your stats:

SELECT [Main Roll Table].RollID, [Main Roll Table].[Lot#], [Main Roll
Table].DateofMfg, [Gauge Table].Gauge, DMin("Gauge","Gauge
Table","[RollID]='" & [Gauge Table].RollID & "'") AS [Min],
DMax("Gauge","Gauge Table","[RollID]='" & [Gauge Table].RollID & "'") AS
[Max], DSum("Gauge","Gauge Table","[RollID]='" & [Gauge Table].RollID & "'")
AS GaugeSum, DCount("Gauge","Gauge Table","[RollID]='" & [Gauge
Table].RollID & "'") AS NoOfEntries, [GaugeSum]/[NoOfEntries] AS [Avg]
FROM [Main Roll Table] LEFT JOIN [Gauge Table] ON [Main Roll Table].RollID =
[Gauge Table].RollID;

Tom Lake

Reply to this Message