How do you categorize sum of fields

[Guest]

Hello,
I put togheater a database with the help of some of your responses. For this
I thank you.
I need to be able to categorize the results of fields on a table, based on a
preset criteria.
For example: if the sum of Paper%+OtherMaterial%+OtherPlastic% >=26 it
should show 2 on a text box in the form. Otherwise it would show other values
not necessarily number.
I have been trying to get this with different methods such as Sum, Dsum, IF,
Iif and can't get it to work.
any help you could give me is much appreciated.

Evert

 

[Jeff Boyce]

Evert

Since "how" depends on "what", what is your underlying data structure?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff sorry it took so long to get back to you.
Here is what I've done:
Created a table (tbl PO) with the following fields:
PO#(As primary field), Vendor,material type, inspected by, location
With exception of PO# the rest get their info from other tables
My second table (tbl Percent) has the following:
PO#, Ptype%, paper%,H20%, other materials%, other plastics%
Its primary relationship is PO#
I've vreated a query as follow:
SELECT DISTINCTROW [tbl PO].[PO#], Avg([tbl percent].[Type%]) AS [Avg Of
Type%], Avg([tbl percent].[Paper%]) AS [Avg Of Paper%], Avg([tbl
percent].[H2O%]) AS [Avg Of H2O%], Avg([tbl percent].[Other Materials]) AS
[Avg Of Other Materials], Avg([tbl percent].[Other Plastic]) AS [Avg Of Other
Plastic]
FROM [tbl PO] INNER JOIN [tbl percent] ON [tbl PO].[PO#] = [tbl percent].[PO#]
GROUP BY [tbl PO].[PO#];

I've also created the following:
frm 1 - its record source is tbl PO. In this form I have a sub form that
inputs data to tbl percent.
frm Total - its record source is : SELECT [tbl PO].[PO#], [tbl PO].Vendor,
[tbl PO].[Material Type], [tbl PO].[Number of Bales], [tbl PO].[Inspected
by], [tbl PO].Location, [tbl PO].Notes, [Table1 Query].[Avg Of Type%],
[Table1 Query].[Avg Of Paper%], [Table1 Query].[Avg Of H2O%], [Table1
Query].[Avg Of Other Materials], [Table1 Query].[Avg Of Other Plastic] FROM
[Table1 Query] INNER JOIN [tbl PO] ON [Table1 Query].[PO#]=[tbl PO].[PO#];

Now everything is working as planned but I don't know how to get the sum from
the fields Avg Of Paper% + avg Of Other Materials + avg Of Other Plastics
that is in tbl Total. Also, How do I point this total to a preset criteria
based on the material type? this criteria varies between percentages of
contaminants.
Any help would be great! Please remember I'm still wet behind the ears when
it comes to Access.

Thanks in advance

Evert

 

[Jeff Boyce]

I'm concerned on a couple counts. First, by using "repeating fields" (e.g.,
"% of X", "% of Y", "% of Z", ...) you are treating Access as if it were a
spreadsheet. You will not get anywhere near the full use of the features
and functions Access offers if you feed it 'sheet data.

Second, since those field names suggest that you have percentages in the
field, I must point out an error in math.

If your first percent field is based on a sample of 2, and the next percent
field is based on a sample of 1000, and the next ..., trying to "average"
your percentages is mathematically unsound.

Or have I still misunderstood what you are trying to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff sorry it took so long to get back to you.
Here is what I've done:
Created a table (tbl PO) with the following fields:
PO#(As primary field), Vendor,material type, inspected by, location
With exception of PO# the rest get their info from other tables
My second table (tbl Percent) has the following:
PO#, Ptype%, paper%,H20%, other materials%, other plastics%
Its primary relationship is PO#
I've vreated a query as follow:
SELECT DISTINCTROW [tbl PO].[PO#], Avg([tbl percent].[Type%]) AS [Avg Of
Type%], Avg([tbl percent].[Paper%]) AS [Avg Of Paper%], Avg([tbl
percent].[H2O%]) AS [Avg Of H2O%], Avg([tbl percent].[Other Materials]) AS
[Avg Of Other Materials], Avg([tbl percent].[Other Plastic]) AS [Avg Of
Other
Plastic]
FROM [tbl PO] INNER JOIN [tbl percent] ON [tbl PO].[PO#] = [tbl
percent].[PO#]
GROUP BY [tbl PO].[PO#];

I've also created the following:
frm 1 - its record source is tbl PO. In this form I have a sub form that
inputs data to tbl percent.
frm Total - its record source is : SELECT [tbl PO].[PO#], [tbl PO].Vendor,
[tbl PO].[Material Type], [tbl PO].[Number of Bales], [tbl PO].[Inspected
by], [tbl PO].Location, [tbl PO].Notes, [Table1 Query].[Avg Of Type%],
[Table1 Query].[Avg Of Paper%], [Table1 Query].[Avg Of H2O%], [Table1
Query].[Avg Of Other Materials], [Table1 Query].[Avg Of Other Plastic]
FROM
[Table1 Query] INNER JOIN [tbl PO] ON [Table1 Query].[PO#]=[tbl PO].[PO#];

Now everything is working as planned but I don't know how to get the sum
from
the fields Avg Of Paper% + avg Of Other Materials + avg Of Other Plastics
that is in tbl Total. Also, How do I point this total to a preset criteria
based on the material type? this criteria varies between percentages of
contaminants.
Any help would be great! Please remember I'm still wet behind the ears
when
it comes to Access.

Thanks in advance

Evert

Evert

Since "how" depends on "what", what is your underlying data structure?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

The information (percentages) that are entered in the tables would come from
physical inspections and other mechanical methods.
Also, I'm not sure what you mean by "mathematically unsound" these
percentages would come from the query. I guess the nomenclature of my fields
need to be more specific.

Let me explain this better:
We collect 5 samples of material from bulk loads and analyze it, the
results are the fields are logged on a spread sheet. One spread sheet for
each bulk load.
Out of these samples we average the result of the 5 analysis in each
category(material type, paper content, water content, etc). Each value is
separate, however, the result or Average from these values will determine the
grade of the material.
My thoughs when I started this database was how to make it useful for the
technician, as well as be able to query the database to trend specific
fields. Does this sound too far fetch or can it be accomplished better on a
spreadsheet?

Any suggestions would be immensly appreciated.
Evert

I'm concerned on a couple counts. First, by using "repeating fields" (e.g.,
"% of X", "% of Y", "% of Z", ...) you are treating Access as if it were a
spreadsheet. You will not get anywhere near the full use of the features
and functions Access offers if you feed it 'sheet data.

Second, since those field names suggest that you have percentages in the
field, I must point out an error in math.

If your first percent field is based on a sample of 2, and the next percent
field is based on a sample of 1000, and the next ..., trying to "average"
your percentages is mathematically unsound.

Or have I still misunderstood what you are trying to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff sorry it took so long to get back to you.
Here is what I've done:
Created a table (tbl PO) with the following fields:
PO#(As primary field), Vendor,material type, inspected by, location
With exception of PO# the rest get their info from other tables
My second table (tbl Percent) has the following:
PO#, Ptype%, paper%,H20%, other materials%, other plastics%
Its primary relationship is PO#
I've vreated a query as follow:
SELECT DISTINCTROW [tbl PO].[PO#], Avg([tbl percent].[Type%]) AS [Avg Of
Type%], Avg([tbl percent].[Paper%]) AS [Avg Of Paper%], Avg([tbl
percent].[H2O%]) AS [Avg Of H2O%], Avg([tbl percent].[Other Materials]) AS
[Avg Of Other Materials], Avg([tbl percent].[Other Plastic]) AS [Avg Of
Other
Plastic]
FROM [tbl PO] INNER JOIN [tbl percent] ON [tbl PO].[PO#] = [tbl
percent].[PO#]
GROUP BY [tbl PO].[PO#];

I've also created the following:
frm 1 - its record source is tbl PO. In this form I have a sub form that
inputs data to tbl percent.
frm Total - its record source is : SELECT [tbl PO].[PO#], [tbl PO].Vendor,
[tbl PO].[Material Type], [tbl PO].[Number of Bales], [tbl PO].[Inspected
by], [tbl PO].Location, [tbl PO].Notes, [Table1 Query].[Avg Of Type%],
[Table1 Query].[Avg Of Paper%], [Table1 Query].[Avg Of H2O%], [Table1
Query].[Avg Of Other Materials], [Table1 Query].[Avg Of Other Plastic]
FROM
[Table1 Query] INNER JOIN [tbl PO] ON [Table1 Query].[PO#]=[tbl PO].[PO#];

Now everything is working as planned but I don't know how to get the sum
from
the fields Avg Of Paper% + avg Of Other Materials + avg Of Other Plastics
that is in tbl Total. Also, How do I point this total to a preset criteria
based on the material type? this criteria varies between percentages of
contaminants.
Any help would be great! Please remember I'm still wet behind the ears
when
it comes to Access.

Thanks in advance

Evert

Evert

Since "how" depends on "what", what is your underlying data structure?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hello,
I put togheater a database with the help of some of your responses. For
this
I thank you.
I need to be able to categorize the results of fields on a table, based
on
a
preset criteria.
For example: if the sum of Paper%+OtherMaterial%+OtherPlastic% >=26
it
should show 2 on a text box in the form. Otherwise it would show other
values
not necessarily number.
I have been trying to get this with different methods such as Sum,
Dsum,
IF,
Iif and can't get it to work.
any help you could give me is much appreciated.

Evert

 

[Jeff Boyce]

I've been away for a few days, sorry for the delayed response...

If you'll re-read my response, I point out that averaging percentages only
works if the "n" (sample size) of EVERY measurement on which a percentage is
calculated is identical. As soon as you have differing "n"s, you cannot
rely on an "average of percentages" to be accurate. By mathematically
unsound, I mean inaccurate.

If you are currently handling this via a spreadsheet, what is it that you
hope to be able to do with Access that you cannot do now?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

The information (percentages) that are entered in the tables would come
from
physical inspections and other mechanical methods.
Also, I'm not sure what you mean by "mathematically unsound" these
percentages would come from the query. I guess the nomenclature of my
fields
need to be more specific.

Let me explain this better:
We collect 5 samples of material from bulk loads and analyze it, the
results are the fields are logged on a spread sheet. One spread sheet for
each bulk load.
Out of these samples we average the result of the 5 analysis in each
category(material type, paper content, water content, etc). Each value is
separate, however, the result or Average from these values will determine
the
grade of the material.
My thoughs when I started this database was how to make it useful for the
technician, as well as be able to query the database to trend specific
fields. Does this sound too far fetch or can it be accomplished better on
a
spreadsheet?

Any suggestions would be immensly appreciated.
Evert

I'm concerned on a couple counts. First, by using "repeating fields"
(e.g.,
"% of X", "% of Y", "% of Z", ...) you are treating Access as if it were
a
spreadsheet. You will not get anywhere near the full use of the features
and functions Access offers if you feed it 'sheet data.

Second, since those field names suggest that you have percentages in the
field, I must point out an error in math.

If your first percent field is based on a sample of 2, and the next
percent
field is based on a sample of 1000, and the next ..., trying to "average"
your percentages is mathematically unsound.

Or have I still misunderstood what you are trying to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff sorry it took so long to get back to you.
Here is what I've done:
Created a table (tbl PO) with the following fields:
PO#(As primary field), Vendor,material type, inspected by, location
With exception of PO# the rest get their info from other tables
My second table (tbl Percent) has the following:
PO#, Ptype%, paper%,H20%, other materials%, other plastics%
Its primary relationship is PO#
I've vreated a query as follow:
SELECT DISTINCTROW [tbl PO].[PO#], Avg([tbl percent].[Type%]) AS [Avg
Of
Type%], Avg([tbl percent].[Paper%]) AS [Avg Of Paper%], Avg([tbl
percent].[H2O%]) AS [Avg Of H2O%], Avg([tbl percent].[Other Materials])
AS
[Avg Of Other Materials], Avg([tbl percent].[Other Plastic]) AS [Avg Of
Other
Plastic]
FROM [tbl PO] INNER JOIN [tbl percent] ON [tbl PO].[PO#] = [tbl
percent].[PO#]
GROUP BY [tbl PO].[PO#];

I've also created the following:
frm 1 - its record source is tbl PO. In this form I have a sub form
that
inputs data to tbl percent.
frm Total - its record source is : SELECT [tbl PO].[PO#], [tbl
PO].Vendor,
[tbl PO].[Material Type], [tbl PO].[Number of Bales], [tbl
PO].[Inspected
by], [tbl PO].Location, [tbl PO].Notes, [Table1 Query].[Avg Of Type%],
[Table1 Query].[Avg Of Paper%], [Table1 Query].[Avg Of H2O%], [Table1
Query].[Avg Of Other Materials], [Table1 Query].[Avg Of Other Plastic]
FROM
[Table1 Query] INNER JOIN [tbl PO] ON [Table1 Query].[PO#]=[tbl
PO].[PO#];

Now everything is working as planned but I don't know how to get the
sum
from
the fields Avg Of Paper% + avg Of Other Materials + avg Of Other
Plastics
that is in tbl Total. Also, How do I point this total to a preset
criteria
based on the material type? this criteria varies between percentages of
contaminants.
Any help would be great! Please remember I'm still wet behind the ears
when
it comes to Access.

Thanks in advance

Evert
:

Evert

Since "how" depends on "what", what is your underlying data structure?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hello,
I put togheater a database with the help of some of your responses.
For
this
I thank you.
I need to be able to categorize the results of fields on a table,
based
on
a
preset criteria.
For example: if the sum of Paper%+OtherMaterial%+OtherPlastic%
=26
it
should show 2 on a text box in the form. Otherwise it would show
other
values
not necessarily number.
I have been trying to get this with different methods such as Sum,
Dsum,
IF,
Iif and can't get it to work.
any help you could give me is much appreciated.

Evert