combine data into ranges

[veespike]

I need to combine data in a field down to ranges.

Lets say I have a series of records, each with a field that contains a number between 1-100. Sometimes I need to see the exact number, but there are times when I would like to combine that data into a range - usually in reports. SO lets say that I would need to see all records where Size is 6, Gauge is between 18 and 25, and then a sum of Weight.

I have created queries that will find the individual records, but I cannot figure out how to make either access or excel combine that data into ranges. What I am trying to do is make it easy to see at a glance how much material I have of a given size (or all sizes) that fits into various ranges of Gauge.

I hope that makes sense

 

[SydneyGeek]

Build a Totals query.

Add the Size and Gauge fields, as well as the description.
Click the Sigma button to add the Totals row. Leave the Size and Gauge as Group By; change the description to Count.

Just noticed -- ranges, not sizes. OK, create a dummy table with 2 fields. First field has numbers 1 to 100. Second field has your ranges, entered as text.
Join this table to your main one on the size field, and use the Range field (instead of Size) in the query.

Denis

 

[jasmith4]

In Excel it's a little simpler. First, put all your data onto one sheet -- I'll assume Size, Gauge and Weight are your only three fields, so those go in columns A:C with headers "Size", "Gauge" and "Weight" in A1:C1. Next, copy A1 to E1, and copy B1 to F1:G1 so that E1:G1 have "Size", "Gauge" and "Gauge" again. If you want, enter "Total Weight" in H1.

Now, in H2, enter the following formula: =DSUM(A:C,3,E1:G2) This formula will add up the Weight column (#3) in records from the database (A:C) which match the criteria (E1:G2). You'll initially get zero, because there are no criteria yet.

To get instant results for size 6, gauge 18-25, enter "6" in E2, ">=18" in F2 and "<=25" in G2. Those complete the criteria you want. If you want just one gauge, enter just the number in F2 and blank out G2.

Here's the tricky part: if you want several results, you have to copy both headers and criteria downward. That is, select E1:H2, a 2x4 range, then use the auto-fill handle on the lower-right corner -- where the cursor turns into a thin black cross -- to copy everything down as far as you want. E3:H3 will have headers, so will E5:H5, E7:H7, etc., and you have to have them, because criteria ranges work by matching headers. But now in E2:G2, E4:G4, etc., you can enter many criteria.