Help with intervals...

[Chris]

I'm creating a report takes data from a table, then
performs the calculations to get what it is I need. The
problem I have run into is with the intervals that this
data is grouped on. For the most part I want it in
intervals of 50. But there are a few other categories with
are not exactly 50, and this is where my problem is.

I want to group the data like this:
- less than 300
- 300-349
- 350-399
..
..
..
- 900-949
- 950-997
- 998-999

What I've done so far is just create an if statement to
actually display these intervals, and set, in sorting and
grouping the following:
Group Header - Yes
Group Footer - Yes
Group On - Interval
Group Interval - 50
Keep Together - Whole Group

Any help in this getting my report to group this way would
be great.
Thanks

 

[Chris]

Oh and I know that this is the reason why I am total
groups of fifty and no less. I just need a way to do those
3 intervals that are not fifty along with the rest being
by fifty...
Hope that makes sense...

 

[Marshall Barton]

I'm creating a report takes data from a table, then
performs the calculations to get what it is I need. The
problem I have run into is with the intervals that this
data is grouped on. For the most part I want it in
intervals of 50. But there are a few other categories with
are not exactly 50, and this is where my problem is.

I want to group the data like this:
- less than 300
- 300-349
- 350-399
.
.
.
- 900-949
- 950-997
- 998-999

What I've done so far is just create an if statement to
actually display these intervals, and set, in sorting and
grouping the following:
Group Header - Yes
Group Footer - Yes
Group On - Interval
Group Interval - 50
Keep Together - Whole Group

Any help in this getting my report to group this way would
be great.

The way I would approach the problem of irregular group
intervals is to create a table that defines the interval
ranges along with some oter helper value(s?).

Let's say you have a table named CatIntervals with fields
IntervalNum
CategoryID
RangeStart
RangeEnd

some sample data might look like:
IntervalNum CategoryID RangeStart RangeEnd
1 1 0 300
2 1 301 350
. . .
14 1 950 997
15 1 998 999

Now you can add that information to the reports record
source query:

SELECT table.*, CatIntervals.IntervalNum,
CatIntervals.RangeStart, CatIntervals.RangeEnd
FROM table INNER JOIN CatIntervals ON table.fieldx Between
CatIntervals.RangeStart And CatIntervals.RangeEnd
WHERE CatIntervals.CategoryID = 1

The report can now group on the IntervalNum field and the
range limits are available to display in the group header.

 

[Chris]

-----Original Message-----



The way I would approach the problem of irregular group
intervals is to create a table that defines the interval
ranges along with some oter helper value(s?).

Let's say you have a table named CatIntervals with fields
IntervalNum
CategoryID
RangeStart
RangeEnd

some sample data might look like:
IntervalNum CategoryID RangeStart RangeEnd
1
0 300
1 301
350
. . .
14 1 950
997
1 998
999

Now you can add that information to the reports record
source query:

SELECT table.*, CatIntervals.IntervalNum,
CatIntervals.RangeStart, CatIntervals.RangeEnd
FROM table INNER JOIN CatIntervals ON table.fieldx Between
CatIntervals.RangeStart And CatIntervals.RangeEnd
WHERE CatIntervals.CategoryID = 1

The report can now group on the IntervalNum field and the
range limits are available to display in the group header.

Thanks Marsh,
What I did after reading your post was instead of making
an entirely new table to state what the ranges should be I
modified a make-table query which assigned each row in the
table a value between 1 and 16. I did this because my
report runs directly off of the table, no query involved.
I also realized this way basically sucked.
So now I have two questions stemming from your responce.
What exactly are you defining as the CategoryID, in the
CatIntervals table? I understand the other 3 values but
not this one.
Also the SELECT FROM WHERE statements, where do they go?
Thanks

 

[Guest]

-----Original Message-----

The way I would approach the problem of irregular group
intervals is to create a table that defines the interval
ranges along with some oter helper value(s?).

Let's say you have a table named CatIntervals with fields
IntervalNum
CategoryID
RangeStart
RangeEnd

some sample data might look like:
IntervalNum CategoryID RangeStart RangeEnd
1
0 300
1 301
350
. . .
14 1 950
997
1 998
999

Now you can add that information to the reports record
source query:

SELECT table.*, CatIntervals.IntervalNum,
CatIntervals.RangeStart, CatIntervals.RangeEnd
FROM table INNER JOIN CatIntervals ON table.fieldx Between
CatIntervals.RangeStart And CatIntervals.RangeEnd
WHERE CatIntervals.CategoryID = 1

The report can now group on the IntervalNum field and the
range limits are available to display in the group header.

Alright, I know where to enter it now. I've set everything
up like how it has been shown. But I'm consistently
getting an error in the FROM statement telling me there is
no And operator for the Between statement. I have input it
in the same context as the example you have given...

 

[Marshall Barton]

Thanks Marsh,
What I did after reading your post was instead of making
an entirely new table to state what the ranges should be I
modified a make-table query which assigned each row in the
table a value between 1 and 16. I did this because my
report runs directly off of the table, no query involved.
I also realized this way basically sucked.
So now I have two questions stemming from your responce.
What exactly are you defining as the CategoryID, in the
CatIntervals table?

The CategoryID field is what ever your main table is using
the identify a category. I assumed that the category way a
number and that's what I used in my example.

I understand the other 3 values but
not this one.
Also the SELECT FROM WHERE statements, where do they go?

Those are not statements, all those lines together are the
SQL vew of an example query that would supply interval
numbers for the report to group on.

 

[Marshall Barton]

The way I would approach the problem of irregular group

Alright, I know where to enter it now. I've set everything
up like how it has been shown. But I'm consistently
getting an error in the FROM statement telling me there is
no And operator for the Between statement. I have input it
in the same context as the example you have given...

Please post a Copy/Paste of your query's SQL along with a
brief explanation of the tables, their fields and some
sample data.

 

[Chris]

-----Original Message-----
1
0 300
1 301
350 1 950
997
1 998
999

The CategoryID field is what ever your main table is using
the identify a category. I assumed that the category way a
number and that's what I used in my example.

After I posted I realized what they were...thanks

Those are not statements, all those lines together are the
SQL vew of an example query that would supply interval
numbers for the report to group on.

Alright, I've set everything up like how it has been
shown. But I'm consistently getting an error in the FROM
statement "Between operator without And in query
expression 'UticaReturnFile_HO.AttractCTScore Between
Intervals.RangeStart'" I have input it in the same
context as the example you had given.
I'm assuming that the table.fieldx from your example is
the field in the original table that contains the data
with which I will be grouping/sorting on the intervals
that are being setup...

 

[Guest]

-----Original Message-----


Please post a Copy/Paste of your query's SQL along with a
brief explanation of the tables, their fields and some
sample data.

Okay here is the SQL:

SELECT UticaReturnFile_HO.*, Intervals.IntervalNum,
Intervals.RangeStart, Intervals.RangeEnd

FROM UticaReturnFile_HO INNER JOIN Intervals ON
[UticaReturnFile_HO.AttractCTScore] Between
Intervals.RangeStart And Intervals.RangeEnd

WHERE Intervals.CategoryID = 1;

UticaReturnFile_HO is the table which houses all the data
for this particular report. It holds the info on certain
clients, street address, zip, state, etc. The
AttractCTScore is the field in which I am sorting all my
data. It is a 3 digit number broken down into the
intervals I have previously mentioned.
Sample data would be something similar to this...
Street Address - text
State - text (2 character)
zip - text
Losses - Number
AttractCTScore - Number
There are roughly 50 fields so pasting them all is not
possibly, also there may possibly be somewhat personal
info in it...
Intervals is the table which has the definition of the
intervals. Fields are IntervalNum, CategoryID, RangeStart,
RangeEnd.
Sample data would be...
IntervalNum - AutoNumber
CategoryID - "Less than 300" "300-349" "350-399" etc...
RangeStart - 3 digit number
RangeEnd - 3 Digit number

Thanks

 

[Marshall Barton]

-----Original Message-----

Please post a Copy/Paste of your query's SQL along with a
brief explanation of the tables, their fields and some
sample data.

Okay here is the SQL:

SELECT UticaReturnFile_HO.*, Intervals.IntervalNum,
Intervals.RangeStart, Intervals.RangeEnd

FROM UticaReturnFile_HO INNER JOIN Intervals ON
[UticaReturnFile_HO.AttractCTScore] Between
Intervals.RangeStart And Intervals.RangeEnd

WHERE Intervals.CategoryID = 1;

UticaReturnFile_HO is the table which houses all the data
for this particular report. It holds the info on certain
clients, street address, zip, state, etc. The
AttractCTScore is the field in which I am sorting all my
data. It is a 3 digit number broken down into the
intervals I have previously mentioned.
Sample data would be something similar to this...
Street Address - text
State - text (2 character)
zip - text
Losses - Number
AttractCTScore - Number
There are roughly 50 fields so pasting them all is not
possibly, also there may possibly be somewhat personal
info in it...
Intervals is the table which has the definition of the
intervals. Fields are IntervalNum, CategoryID, RangeStart,
RangeEnd.
Sample data would be...
IntervalNum - AutoNumber
CategoryID - "Less than 300" "300-349" "350-399" etc...
RangeStart - 3 digit number
RangeEnd - 3 Digit number

The only thing I can see wrong is the brackets in the ON
expression:

FROM UticaReturnFile_HO INNER JOIN Intervals
ON UticaReturnFile_HO.AttractCTScore
Between Intervals.RangeStart And Intervals.RangeEnd

 

[Guest]

-----Original Message-----

-----Original Message-----

Alright, I know where to enter it now. I've set everything
up like how it has been shown. But I'm consistently
getting an error in the FROM statement telling me

there
is

no And operator for the Between statement. I have

input
it

in the same context as the example you have given...


Please post a Copy/Paste of your query's SQL along with a
brief explanation of the tables, their fields and some
sample data.

Okay here is the SQL:

SELECT UticaReturnFile_HO.*, Intervals.IntervalNum,
Intervals.RangeStart, Intervals.RangeEnd

FROM UticaReturnFile_HO INNER JOIN Intervals ON
[UticaReturnFile_HO.AttractCTScore] Between
Intervals.RangeStart And Intervals.RangeEnd

WHERE Intervals.CategoryID = 1;

UticaReturnFile_HO is the table which houses all the data
for this particular report. It holds the info on certain
clients, street address, zip, state, etc. The
AttractCTScore is the field in which I am sorting all my
data. It is a 3 digit number broken down into the
intervals I have previously mentioned.
Sample data would be something similar to this...
Street Address - text
State - text (2 character)
zip - text
Losses - Number
AttractCTScore - Number
There are roughly 50 fields so pasting them all is not
possibly, also there may possibly be somewhat personal
info in it...
Intervals is the table which has the definition of the
intervals. Fields are IntervalNum, CategoryID, RangeStart,
RangeEnd.
Sample data would be...
IntervalNum - AutoNumber
CategoryID - "Less than 300" "300-349" "350-399" etc...
RangeStart - 3 digit number
RangeEnd - 3 Digit number

The only thing I can see wrong is the brackets in the ON
expression:

FROM UticaReturnFile_HO INNER JOIN Intervals
ON UticaReturnFile_HO.AttractCTScore
Between Intervals.RangeStart And Intervals.RangeEnd

Yeah I had originally written it without the [] but that
did not work either. oh well they should be using the
mainframe to do this db anyways...

 

[Chris]

-----Original Message-----

-----Original Message-----

Alright, I know where to enter it now. I've set everything
up like how it has been shown. But I'm consistently
getting an error in the FROM statement telling me

there
is

no And operator for the Between statement. I have

input
it

in the same context as the example you have given...


Please post a Copy/Paste of your query's SQL along with a
brief explanation of the tables, their fields and some
sample data.

Okay here is the SQL:

SELECT UticaReturnFile_HO.*, Intervals.IntervalNum,
Intervals.RangeStart, Intervals.RangeEnd

FROM UticaReturnFile_HO INNER JOIN Intervals ON
[UticaReturnFile_HO.AttractCTScore] Between
Intervals.RangeStart And Intervals.RangeEnd

WHERE Intervals.CategoryID = 1;

UticaReturnFile_HO is the table which houses all the data
for this particular report. It holds the info on certain
clients, street address, zip, state, etc. The
AttractCTScore is the field in which I am sorting all my
data. It is a 3 digit number broken down into the
intervals I have previously mentioned.
Sample data would be something similar to this...
Street Address - text
State - text (2 character)
zip - text
Losses - Number
AttractCTScore - Number
There are roughly 50 fields so pasting them all is not
possibly, also there may possibly be somewhat personal
info in it...
Intervals is the table which has the definition of the
intervals. Fields are IntervalNum, CategoryID, RangeStart,
RangeEnd.
Sample data would be...
IntervalNum - AutoNumber
CategoryID - "Less than 300" "300-349" "350-399" etc...
RangeStart - 3 digit number
RangeEnd - 3 Digit number

The only thing I can see wrong is the brackets in the ON
expression:

FROM UticaReturnFile_HO INNER JOIN Intervals
ON UticaReturnFile_HO.AttractCTScore
Between Intervals.RangeStart And Intervals.RangeEnd

Any ideas? I cannot get this to run at all. I'll have to
start the research for my internship all over if I can't
get this to work I just found out. Take the brackets out
and still nothing.
The exact error is:

Between operator without And in query
expression 'UticaReturnFile_HO.AttractCTScore Between
Intervals.RangeStart'.

 

[Marshall Barton]

Any ideas? I cannot get this to run at all. I'll have to
start the research for my internship all over if I can't
get this to work I just found out. Take the brackets out
and still nothing.
The exact error is:

Between operator without And in query
expression 'UticaReturnFile_HO.AttractCTScore Between
Intervals.RangeStart'.

What does the entire query look like now? Please use
Copy/Paste so I can see the exact SQL.

 

[Guest]

-----Original Message-----


What does the entire query look like now? Please use
Copy/Paste so I can see the exact SQL.

Here it is:

SELECT UticaReturnFile_HO.*, Intervals.IntervalNum,
Intervals.RangeStart, Intervals.RangeEnd

FROM UticaReturnFile_HO INNER JOIN Intervals ON
UticaReturnFile_HO.AttractCTScore Between
Intervals.RangeStart And Intervals.RangeEnd

WHERE Intervals.CategoryID = 1;


I tried to word the expression another way yesterday,
unsuccessfully, which ended up being this:

SELECT UticaReturnFile_HO.*, Intervals.IntervalNum,
Intervals.RangeStart, Intervals.RangeEnd

FROM UticaReturnFile_HO INNER JOIN Intervals ON
Intervals.RangeStart<=UticaReturnFile_HO.AttractCTScore<=Ra
ngeEnd

WHERE Intervals.CategoryID = Not Null;

The reason for Not Null, is because the Category ID is
listed as text.
This "complied" but just listed the IntervalNum,
RangeStart and RangeEnd in constant repeating order on the
resulting query. So it looked like
1
2
3
4
..
..
..
15
16
1
2
3
4
..
..
..

Over and over again. It basically did not align the
IntervalNum with it's corresponding AttractCTScore.

 

[Marshall Barton]

-----Original Message-----

Here it is:

SELECT UticaReturnFile_HO.*, Intervals.IntervalNum,
Intervals.RangeStart, Intervals.RangeEnd

FROM UticaReturnFile_HO INNER JOIN Intervals ON
UticaReturnFile_HO.AttractCTScore Between
Intervals.RangeStart And Intervals.RangeEnd

WHERE Intervals.CategoryID = 1;


I tried to word the expression another way yesterday,
unsuccessfully, which ended up being this:

SELECT UticaReturnFile_HO.*, Intervals.IntervalNum,
Intervals.RangeStart, Intervals.RangeEnd

FROM UticaReturnFile_HO INNER JOIN Intervals ON
Intervals.RangeStart<=UticaReturnFile_HO.AttractCTScore<=Ra
ngeEnd

That's sort of what I was going to suggest as the next try,
but the syntax is all wrong. Let's try it this way:

FROM UticaReturnFile_HO INNER JOIN Intervals ON
Intervals.RangeStart<=UticaReturnFile_HO.AttractCTScore
AND UticaReturnFile_HO.AttractCTScore<=Intervals.RangeEnd

I have no idea why the Between is not working. Maybe you're
using a different SQL than what I'm familiar with in
Access??

 

[Chris]

-----Original Message-----



That's sort of what I was going to suggest as the next try,
but the syntax is all wrong. Let's try it this way:

FROM UticaReturnFile_HO INNER JOIN Intervals ON
Intervals.RangeStart<=UticaReturnFile_HO.AttractCTScore
AND UticaReturnFile_HO.AttractCTScore<=Intervals.RangeEnd

I have no idea why the Between is not working. Maybe you're
using a different SQL than what I'm familiar with in
Access??

Yup! Ran it right before I left yesterday and the query
ran just like it should. Figured my syntax was wrong, I'm
too used to my math ways.
Now to implement it on the report....

I'm using just regular Access SQL, ran it on both '97 and
2000, on two different computers. Didn't work on either
system.
Thanks a lot Marsh...

 

[Chris]

-----Original Message-----
=

Yup! Ran it right before I left yesterday and the query
ran just like it should. Figured my syntax was wrong, I'm
too used to my math ways.
Now to implement it on the report....

I'm using just regular Access SQL, ran it on both '97 and
2000, on two different computers. Didn't work on either
system.
Thanks a lot Marsh...
.

Okay, now I can't tell if this won't run because of my POS
computer I'm forced to use or because my report is wrong.
Access keeps crashing on me...

All I do is use this query as my 'Record Source' then
format it the way I want, using 'Sorting and Grouping'
with 'IntervalNum' to determine the grouping, and show
the 'CategoryID' in it's header. This constantly locks up
Access...

 

[Marshall Barton]

Okay, now I can't tell if this won't run because of my POS
computer I'm forced to use or because my report is wrong.
Access keeps crashing on me...

All I do is use this query as my 'Record Source' then
format it the way I want, using 'Sorting and Grouping'
with 'IntervalNum' to determine the grouping, and show
the 'CategoryID' in it's header. This constantly locks up
Access...

I can't help you with a system that crashes. Some possible
reasons are:

A bad installation of Access? or something??

Incompatable versions of Access/Jet Service Packs/Releases,
including the libraries you're using.

Corruption in your MDB file(s?)

Check Tony Toews web site for articles on these issues.
http://www.granite.ab.ca/accsmstr.htm