Calculate highest months data

  • Thread starter Thread starter jeison
  • Start date Start date
J

jeison

I have 5 months of data. Each months data is in a seperate field. Can i
write a query that will sum the top 3 months?

In the example below, i need a query that can sum Month 2, Month 4, and
Month 5.
Example:
Month 1: 100
Month 2: 300
Month 3: 200
Month 4: 600
Month 5: 600
 
Jeison,

Normally such data is stored in rows instead of seperate fields.
If you really need it in seperate fields then a CrossTab query might help
you at least get started on the problem.

Jack Cannon
 
Rows...fields...tomato....tamato...thats not really the question. My
question is...how do i write a query to find the top 3 values and sum those
values. i dont want to sum every month, only month 2, 4, and 5.

Just joking around with the tomato/tamato reference. i understand what you
mean...
 
If you have fields named Month1, Month2, etc, then this will be a nightmare.

Instead, create a table with fields:
TheMonth which month this record is for
TheValue what number was for this month.

You can now create a query and just select the top 3 months by opening the
Properties box in query design. Looking at the properties of the query (not
of a field), set Top values to: 3

You can then do other things like saving that query, and using another one
to sum its values.

Any time you see repeating fields (like Month1, Month2, ...), it *always*
means that you need a table of related records, instead of having many
repeating fields in the one table.

Read up on 'normalization' of you need to know the technical reasons behind
that. Here's a starting point:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101
 
Jack is absolutely correct.

This task is very easy if you have the data stored in a normalized fashion in
rows.

It is much, much more difficult when you have the data stored as you describe,
in spreadsheet fashion with multiple datapoints in each record. See below for
a suggestion.
Rows...fields...tomato....tamato...thats not really the question. My
question is...how do i write a query to find the top 3 values and sum those
values. i dont want to sum every month, only month 2, 4, and 5.

Just joking around with the tomato/tamato reference. i understand what you
mean...
Click to expand...

You can normalize the data with a "Normalizing Union Query". In the SQL window
enter

SELECT 1 As TheMonth, [Month 1] AS Amount FROM yourspreadsheet
UNION ALL
SELECT 2, [Month 2] FROM yourspreadsheet
UNION ALL
SELECT 3, [Month 3] FROM yourspreadsheet
<and so on through all the fields>

Store this query as uniAllMonths.

THEN create a second query based on this query:

SELECT Sum(Amount) FROM (SELECT TOP 3 Amount FROM uniAllMonths ORDER BY
Amount)
 
Back
Top