[AccessD] Report Grouping

Hi Gina

Assuming that

  STALLWORTH - 1080400000
  CHS - between "108175*" and "108176*" or like "40811*"
  VCHA - between "108100*" and "108210*"

should be something like this as no ranges overlap:

  STALLWORTH - 1080400000
  CHS - between "108175*" and "108176*" or like "40811*"
  VCHA - between "108100*" and "108120*"

you could create a table with the specific ranges:

Center      AreaMin         AreaMax
CHS         1081750000      1081769999
STALLWORTH  1080400000      1080400000
VCHA        1081000000      1081209999
CHS         4081100000      4081199999

Then modify your report source like this (only basic fields shown):

<SQL>

  SELECT
    Area,
    Center,
    Report
  FROM
    tblCenterData,
    tblCenterRange
  WHERE
    Area Between [AreaMin] And [AreaMax];

</SQL>

Note the missing join. Also, grouping and sorting should be left out -
do that in your report.

Thus, this will be returned:

Area            Center          Report
1080400000      STALLWORTH      80025
1080400000      STALLWORTH      80010
1080400000      STALLWORTH      44740
1081000000      VCHA            50400
1081000000      VCHA            50200
1081000000      VCHA            50100
1081750000      CHS             59505
1081750000      CHS             59205
1081750000      CHS             59203

/gustav


> Using a parameter query to prompt the user was your suggestion, never my 
> thought. I just want to run a report that takes 6000 records based on center 
> numbers and groups them by area based on the CenterRange field that 
> corresponds to each Area.

> So, I've got data like:

> 1080400000      44740   INC LONG-TERM INVEST    1296067 730000  1310545
> 1080400000      80010   EXEC ADMIN IDS TAX      8588    7635    13708
> 1080400000      80025   UNIV SERVICE IDS TAX    24149   14232   25551
> 1081750000      59203   NF FBR MED NONHOS STAFF 388     742     97
> 1081750000      59205   NF FBR UNIV STAFF       332     0       94
> 1081750000      59505   FBR AC UNIV STAFF       -172    0       76
> 1081000000      50100   FAC AND EXEC ADMIN      961500  867000  1021511
> 1081000000      50200   FACULTY 280250  55000   467763
> 1081000000      50400   COST SHARING FACULTY    -10482  0       0

> and I want the report to put each of these in their respective Areas, based 
> on the center number at the beginning.  There is no overlap between centers. 
>   One center will fall in only one area.

> So, my report would look like:

> STALLWORTH:
> 1080400000      44740   INC LONG-TERM INVEST    1296067 730000  1310545
> 1080400000      80010   EXEC ADMIN IDS TAX      8588    7635    13708
> 1080400000      80025   UNIV SERVICE IDS TAX    24149   14232   25551

> CHS:
> 1081750000      59203   NF FBR MED NONHOS STAFF 388     742     97
> 1081750000      59205   NF FBR UNIV STAFF       332     0       94
> 1081750000      59505   FBR AC UNIV STAFF       -172    0       76

> VCHA:
> 1081000000      50100   FAC AND EXEC ADMIN      961500  867000  1021511
> 1081000000      50200   FACULTY 280250  55000   467763
> 1081000000      50400   COST SHARING FACULTY    -10482  0       0


> Gina Hoopes <hoopesg at hotmail.com> wrote:

> .. My table of areas and center ranges is as follows:

> STALLWORTH - 1080400000
> CHS - between "108175*" and "108176*" or like "40811*"
> VCHA - between "108100*" and "108210*"

> and so on ...