averaging across columns

P

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.
 
K

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;
 
P

Paul

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


karl dewey said:
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


Paul said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top