Partition function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have data of about 50.000 goods all with different weight. I would like to
group them into less than 1 kg between 1 and 5 kg between 5 and 10 kg and
above 10 Kg. How do I go about that.

I know i should enter Partition([amount],0,10,5) but where do I enter that
(field, Table, criteria, sort.....). Please screenshoot the design view of
the query or something. I am a bit lost. Thank you.

PS: when I tried the answer was always the expression you entered contains
invalid syntax. (WHAT???)
 
I do not think partion will do what you want.
The formula Partition([amount],0,10,5) you posted will break it down into 0
- 5 and >5 - 10 but will not give a grouping of less than 1 kg. I think you
need nested IIF statement.

Post a sample of your data, how you want it to display, and the SQL of a
totals query approaching that results. With that information it can be
analyzed for you.
 
Hi,


You can define a table that define the groups:

MyGroups 'table
FromThis ToThis GroupIs ' fields
-999 1 "less than 1 kg"
1 5 "between 1 and 5 kg"
5 10 "between 5 and 10kg"
10 9999 "more than 10 kg"




then, use an inner join, like:


SELECT ... , b.GroupIs
FROM myTable INNER JOIN myGroups As b
ON myTable.weight >=b.FromThis AND myTable.weight <b.ToThis
GROUP BY b.GroupIs


NOTES: I used < for the upper bound, so a weight of exactly 1 kg does not
belong to two groups.

You can use numerical groups, I only use text for illustration.

I you use a crosstab, probably you would use the "groups" in the PIVOT
clause, not in the GROUP BY clause.




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top