averaging across columns

[Paul]

I have a table set up that looks like this:

Plot# |Name1| |Estimate1| |Name2| |Est.2| |Name3| |Est.3| |Name4| |Est.4|
401 |PC | | 5 | | JD | | 7 | |TQ | | 5 |
| BS | | 7 |
402 ....
403....
430...

Sometimes there were 4 estimates, or sometimes there were 2, or 3. I need to
average the estimate values per plot, but when I run an average on the
columns all that i get is the sum.

 

[karl dewey]

If that is how your table looks, you have a problem. You already see the
problem with data in different columns and the trouble with finding it.

Your table should look like this:
Plot# |Names| |Estimates| | Value |
401 |PC | | 1 | | 5 |
401 |JD | | 2 | | 7 |
401 |TQ | | 3 | | 5 |
401 |BS | | 4 | | 7 |
402 ....
403....
430...

Use a union query to correct --
SELECT [Plot#], [Name1] AS [Names], [Estimate1] AS [Value]
FROM YourTable
UNION ALL SELECT [Plot#], [Name2] AS [Names], [Estimate2] AS [Value]
FROM YourTable
WHERE [Name2] Is Not Null
UNION ALL SELECT [Plot#], [Name3] AS [Names], [Estimate3] AS [Value]
FROM YourTable
WHERE [Name3] Is Not Null
UNION ALL SELECT [Plot#], [Name4] AS [Names], [Estimate4] AS [Value]
FROM YourTable
WHERE [Name4] Is Not Null;

 

[Paul]

Thank you very much for the help. Your solution worked beautifully!
--
Paul

If that is how your table looks, you have a problem. You already see the
problem with data in different columns and the trouble with finding it.

Your table should look like this:
Plot# |Names| |Estimates| | Value |
401 |PC | | 1 | | 5 |
401 |JD | | 2 | | 7 |
401 |TQ | | 3 | | 5 |
401 |BS | | 4 | | 7 |
402 ....
403....
430...

Use a union query to correct --
SELECT [Plot#], [Name1] AS [Names], [Estimate1] AS [Value]
FROM YourTable
UNION ALL SELECT [Plot#], [Name2] AS [Names], [Estimate2] AS [Value]
FROM YourTable
WHERE [Name2] Is Not Null
UNION ALL SELECT [Plot#], [Name3] AS [Names], [Estimate3] AS [Value]
FROM YourTable
WHERE [Name3] Is Not Null
UNION ALL SELECT [Plot#], [Name4] AS [Names], [Estimate4] AS [Value]
FROM YourTable
WHERE [Name4] Is Not Null;

--
KARL DEWEY
Build a little - Test a little

I have a table set up that looks like this:

Plot# |Name1| |Estimate1| |Name2| |Est.2| |Name3| |Est.3| |Name4| |Est.4|
401 |PC | | 5 | | JD | | 7 | |TQ | | 5 |
| BS | | 7 |
402 ....
403....
430...

Sometimes there were 4 estimates, or sometimes there were 2, or 3. I need to
average the estimate values per plot, but when I run an average on the
columns all that i get is the sum.