SQL's with complex queries
I must admit, I'm stumped on how to do this in a SINGLE query. I don't know if
I could even do it in multiple queries. Let me ponder on this a bit and perhaps
ask some of my fellow MVPs for help.
Your response to my request for additional information was very good and has
defined the problem well.
Now, I hope I can respond to the challenge. Here is one possible start of a
solution. I'll try to test this later and see if I've gotten all this right,
but I'm pressed for time right now as I have a few task to do here at work.
SELECT (SELECT Count(*) From BaseTable as Tmp
WHERE Tmp.Marks >= BaseTable.Marks
AND Tmp.ID <= BaseTable.ID) as tmpOrder,
Marks, Income
FROM BaseTable
Query Two uses that as its base
SELECT Partition(tmpOrder,1,40000,4000) as NamedRange
Sum(Income) as SumIncome, Max(Marks) as MaxMark, Min(Marks) as MinMark
FROM QueryOne
GROUP BY Partition(tmpOrder,1,40000,4000)
The problem is
(A) it might not work
(B) you have to manually insert the ranges for the Partition function,
(C) NamedRange comes out as something like " 1 - 4000", " 4001- 8000", etc
You could use VBA to build the partition query.
Quote:
> Hi,
> I am using Access 2000
> Table structure
> id number
> name text
> region text
> marks number
> income number (with 2 digit decimals)
> If there are 40000 records, I need the sum of income of
> 1st 10% of 40000 records having the highest marks, then
> the sum of next 10% of 40000 records having hightest marks
> (excluding the previous 10%)... & so on till 100% in a
> recordset or a table. If the marks are same it sorts
> based on the ID.
> it creates the resultant table will have 10 records
> completing the 100%.
> For eg:
> base Table data
> id name region marks income
> -------------------------------------------
> 1 abc CA 80 20000.00
> 2 nbv LO 50 10000.00
> 3 ghv IL 20 20000.00
> 4 ggg MA 60 15000.00
> 5 dff MA 90 25000.00
> 6 abc CA 70 20000.00
> 7 nbv LO 50 10000.00
> 8 ghv IL 20 25000.00
> 9 ggg MA 60 10000.00
> 10 dff MA 90 20000.00
> 11 ghv IL 20 25000.00
> 12 ghv IL 20 25000.00
> 13 ghv IL 20 25000.00
> 14 ghv IL 20 25000.00
> 15 ghv IL 20 25000.00
> 16 dff MA 90 20000.00
> 17 ghv IL 20 25000.00
> 18 ghv IL 20 25000.00
> 19 ghv IL 50 25000.00
> 20 ghv IL 20 25000.00
> resultant statistical report shall contain foll fields:
> 'hierarchy' 'sum(income)' 'Upper limit marks' 'lower limit'
> ----------------------------------------------------------
> 1st 10% 45000.00 90 90
> 2nd 10% 40000.00 90 80
> 3rd 10% 35000.00 70 60
> 4rth 10% 20000.00 60 50
> 5th 10% 35000.00 50 50
> 6th 10% 45000.00 20 20
> 7th 10% 45000.00 20 20
> 8th 10% 40000.00 20 20
> 9th 10% 40000.00 20 20
> 10th 10% 40000.00 20 20
> I need to write a single query to produce this table...
> is there any optimum way for this?
> >-----Original Message-----
> >First, let's clear up your requirements.
> > Do you want the first 10 percent or the first 10 items,
> etc.
> >Second, let's look at your record structure.
> > Is there ONE record per student or multiple records per
> student? In other
> >words do you need to combine the multiple records for a
> student to get one
> >record per student? IF you do need to combine the
> records what do you want to
> >do with the marks field - average it, sum it?
> > When you say Integer do you mean number? Integer in
> terms of a database means
> >a whole number (no decimal portion) between 32767 and -
> 32767. There are various
> >kinds of number fields - integer, long integer, single,
> double, currency ...
> >Once we have this information, perhaps someone can tackle
> this as a query. If
> >you want to do this in VBA then you could create an
> ordered recordset and step
> >through the recordset to get your figures and as you do
> that you could populate
> >a table. But even there we need answers to the questions
> to design a procedure
> >to do this.
> >ALSO, what version of Access are you using? Access 97,
> 2000, 2002, XP, 95?
> >> hi,
> >> I have an access file with a table having 40000
> records. I
> >> need to retrieve the sum of 1st 10 records .. then the
> >> next 10 records...then the next 10 ... upto 100 %
> >> Is there ant simpler was to create such a statistical
> >> report with a combined qry into a new table?
> >> table structure:
> >> student
> >> --------------
> >> id integer
> >> name text
> >> region text
> >> marks integer
> >> income integer
> >> I need to extract the % based on the total marks..
> >> eg.....
> >> the table should contain...
> >> sum of total income for 1st 10% (highest) marks secured
> by
> >> students
> >> sum of total income for next 10% (highest) marks secured
> >> by students
> >> sum of total income for next 10% (highest) marks secured
> >> by students
> >> till it reaches 100 %...
> >> Is it possible to group in a single query & generate a
> new
> >> table with 10 records thru a vba module / queries ?
> >.
