Number Range Query

[Lisa W.]

I am attempting to create a query that will pull a range of numbers and for
each row, including other data fields that are default values.

Below is an example of the range (Example1).

Example1
Begin Position ID 72 End Position ID
7210001 7210199

I want the query results to include the Begin and End Position ID, as well
as those numbers that fall within that range, along with that list of numbers
would be additional values as below (Example2)

Example2
Loc Value Departments Cost Center Division PositionID
3005 0E75 A03 R07 7210001
3005 0E75 A03 R07 7210002
3005 0E75 A03 R07 7210003
3005 0E75 A03 R07 7210004
3005 0E75 A03 R07 7210005


Is this possible?

Thanks,
Lisa W.

 

[Al Campagna]

Lisa,
If I understand correctly... and assuming PositionID is numeric...
Using a parameter query, you could use this criteria against your
PositionID field...

= [Beginning Position] and <= [Ending Position]

OR
Between [Beginning Position] and [Ending Position]

The "Between" operator can sometimes confuse a new user. It sounds like
it won't include the start and stop values... but it does
When the query runs, you be prompted to enter the two values in your
range.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[pietlinden]

I am attempting to create a query that will pull a range of numbers and for
each row, including other data fields that are default values.

Below is an example of the range (Example1).

Example1
Begin Position ID 72    End Position ID
    7210001       7210199

I want the query results to include the Begin and End Position ID, as well
as those numbers that fall within that range, along with that list of numbers
would be additional values as below (Example2)

Example2
Loc Value       Departments     Cost Center     Division        PositionID
3005    0E75                         A03        R07     7210001
3005    0E75    A03     R07     7210002
3005    0E75    A03     R07     7210003
3005    0E75    A03     R07     7210004
3005    0E75    A03     R07     7210005

Is this possible?

Thanks,
Lisa W.

SELECT [Field1], [Field2]
FROM [MyTable]
WHERE [Division] BETWEEN 7210001 AND 7210199;

 

[Dale Fye]

This works for a single set of values, but if you have multiple [Begin
Position ID] and [End Position ID] values you want to include in the query,
your best bet will be to create another table that contains those values.
tbl_Position_Ranges might only contain those 2 fields, or it could contain
others.

[Begin Position ID] [End Position ID]
7210001 7210199
7210200 7210399
....

With just those two fields, the query might look like:

Select T.*, PR.[Begin Position ID], PR.[End Position ID]
FROM yourTable as T, tbl_Position_Ranges as PR
WHERE T.Division BETWEEN PR.[Begin Position ID]
AND PR.[End Position ID]

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I am attempting to create a query that will pull a range of numbers and for
each row, including other data fields that are default values.

Below is an example of the range (Example1).

Example1
Begin Position ID 72 End Position ID
7210001 7210199

I want the query results to include the Begin and End Position ID, as well
as those numbers that fall within that range, along with that list of numbers
would be additional values as below (Example2)

Example2
Loc Value Departments Cost Center Division PositionID
3005 0E75 A03 R07 7210001
3005 0E75 A03 R07 7210002
3005 0E75 A03 R07 7210003
3005 0E75 A03 R07 7210004
3005 0E75 A03 R07 7210005

Is this possible?

Thanks,
Lisa W.

SELECT [Field1], [Field2]
FROM [MyTable]
WHERE [Division] BETWEEN 7210001 AND 7210199;

 

[Lisa W.]

I will try this. Can you tell me if creating the query this way will pull in
my default values I need as well??

Thanks!

Lisa W.

This works for a single set of values, but if you have multiple [Begin
Position ID] and [End Position ID] values you want to include in the query,
your best bet will be to create another table that contains those values.
tbl_Position_Ranges might only contain those 2 fields, or it could contain
others.

[Begin Position ID] [End Position ID]
7210001 7210199
7210200 7210399
...

With just those two fields, the query might look like:

Select T.*, PR.[Begin Position ID], PR.[End Position ID]
FROM yourTable as T, tbl_Position_Ranges as PR
WHERE T.Division BETWEEN PR.[Begin Position ID]
AND PR.[End Position ID]

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I am attempting to create a query that will pull a range of numbers and for
each row, including other data fields that are default values.

Below is an example of the range (Example1).

Example1
Begin Position ID 72 End Position ID
7210001 7210199

I want the query results to include the Begin and End Position ID, as well
as those numbers that fall within that range, along with that list of numbers
would be additional values as below (Example2)

Example2
Loc Value Departments Cost Center Division PositionID
3005 0E75 A03 R07 7210001
3005 0E75 A03 R07 7210002
3005 0E75 A03 R07 7210003
3005 0E75 A03 R07 7210004
3005 0E75 A03 R07 7210005

Is this possible?

Thanks,
Lisa W.

SELECT [Field1], [Field2]
FROM [MyTable]
WHERE [Division] BETWEEN 7210001 AND 7210199;

 

[Lisa W.]

I still cannot get this to work for me listing the full range of numbers I
need. Can anyone help?? Please start reading from the first post.

Thanks!

 

[Al Campagna]

Lisa,
Because you have 2 sets of Start/End values, you'll need to use an OR
[Begin Position1 ID] [End Position1 ID]
7210001 7210199
[Begin Position2ID] [End Position2 ID]
7210200 7210399

....BETWEEN PR.[Begin Position1 ID]
AND PR.[End Position1 ID] OR
BETWEEN PR.[Begin Position2 ID]
AND PR.[End Position2 ID]
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I will try this. Can you tell me if creating the query this way will pull
in
my default values I need as well??

Thanks!

Lisa W.

This works for a single set of values, but if you have multiple [Begin
Position ID] and [End Position ID] values you want to include in the
query,
your best bet will be to create another table that contains those values.
tbl_Position_Ranges might only contain those 2 fields, or it could
contain
others.

[Begin Position ID] [End Position ID]
7210001 7210199
7210200 7210399
...

With just those two fields, the query might look like:

Select T.*, PR.[Begin Position ID], PR.[End Position ID]
FROM yourTable as T, tbl_Position_Ranges as PR
WHERE T.Division BETWEEN PR.[Begin Position ID]
AND PR.[End Position ID]

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I am attempting to create a query that will pull a range of numbers
and for
each row, including other data fields that are default values.

Below is an example of the range (Example1).

Example1
Begin Position ID 72 End Position ID
7210001 7210199

I want the query results to include the Begin and End Position ID, as
well
as those numbers that fall within that range, along with that list of
numbers
would be additional values as below (Example2)

Example2
Loc Value Departments Cost Center Division
PositionID
3005 0E75 A03 R07 7210001
3005 0E75 A03 R07 7210002
3005 0E75 A03 R07 7210003
3005 0E75 A03 R07 7210004
3005 0E75 A03 R07 7210005

Is this possible?

Thanks,
Lisa W.

SELECT [Field1], [Field2]
FROM [MyTable]
WHERE [Division] BETWEEN 7210001 AND 7210199;

 

[Lisa W.]

Al,

Would I do this in the query design or SQL View? Also I'm assuming my
results will list every number in that range; correct??

Thanks in Advance!

Lisa,
Because you have 2 sets of Start/End values, you'll need to use an OR
[Begin Position1 ID] [End Position1 ID]
7210001 7210199
[Begin Position2ID] [End Position2 ID]
7210200 7210399

....BETWEEN PR.[Begin Position1 ID]
AND PR.[End Position1 ID] OR
BETWEEN PR.[Begin Position2 ID]
AND PR.[End Position2 ID]
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I will try this. Can you tell me if creating the query this way will pull
in
my default values I need as well??

Thanks!

Lisa W.

This works for a single set of values, but if you have multiple [Begin
Position ID] and [End Position ID] values you want to include in the
query,
your best bet will be to create another table that contains those values.
tbl_Position_Ranges might only contain those 2 fields, or it could
contain
others.

[Begin Position ID] [End Position ID]
7210001 7210199
7210200 7210399
...

With just those two fields, the query might look like:

Select T.*, PR.[Begin Position ID], PR.[End Position ID]
FROM yourTable as T, tbl_Position_Ranges as PR
WHERE T.Division BETWEEN PR.[Begin Position ID]
AND PR.[End Position ID]

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I am attempting to create a query that will pull a range of numbers
and for
each row, including other data fields that are default values.

Below is an example of the range (Example1).

Example1
Begin Position ID 72 End Position ID
7210001 7210199

I want the query results to include the Begin and End Position ID, as
well
as those numbers that fall within that range, along with that list of
numbers
would be additional values as below (Example2)

Example2
Loc Value Departments Cost Center Division
PositionID
3005 0E75 A03 R07 7210001
3005 0E75 A03 R07 7210002
3005 0E75 A03 R07 7210003
3005 0E75 A03 R07 7210004
3005 0E75 A03 R07 7210005

Is this possible?

Thanks,
Lisa W.

SELECT [Field1], [Field2]
FROM [MyTable]
WHERE [Division] BETWEEN 7210001 AND 7210199;

 

[Al Campagna]

Lisa,
If your not a power SQL user, use the query design grid with that OR
criteria against your ID field.
If you want the SQL, just do View/SQL, and cut and paste the SQL string
developed by the design grid.
That criteria worked in a similar query I tested.

Eventually, you may want to get the two ranges (or more) from an open
dialog form, where the user just fills in text contols with range values,
and the query looks to that open form for it's criteria values when it
runs.

But for now... just get your current setup working.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Al,

Would I do this in the query design or SQL View? Also I'm assuming my
results will list every number in that range; correct??

Thanks in Advance!

Lisa,
Because you have 2 sets of Start/End values, you'll need to use an OR
[Begin Position1 ID] [End Position1 ID]
7210001 7210199
[Begin Position2ID] [End Position2 ID]
7210200 7210399

....BETWEEN PR.[Begin Position1 ID]
AND PR.[End Position1 ID] OR
BETWEEN PR.[Begin Position2 ID]
AND PR.[End Position2 ID]
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I will try this. Can you tell me if creating the query this way will
pull
in
my default values I need as well??

Thanks!

Lisa W.

:

This works for a single set of values, but if you have multiple [Begin
Position ID] and [End Position ID] values you want to include in the
query,
your best bet will be to create another table that contains those
values.
tbl_Position_Ranges might only contain those 2 fields, or it could
contain
others.

[Begin Position ID] [End Position ID]
7210001 7210199
7210200 7210399
...

With just those two fields, the query might look like:

Select T.*, PR.[Begin Position ID], PR.[End Position ID]
FROM yourTable as T, tbl_Position_Ranges as PR
WHERE T.Division BETWEEN PR.[Begin Position ID]
AND PR.[End Position ID]

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I am attempting to create a query that will pull a range of
numbers
and for
each row, including other data fields that are default values.

Below is an example of the range (Example1).

Example1
Begin Position ID 72 End Position ID
7210001 7210199

I want the query results to include the Begin and End Position ID,
as
well
as those numbers that fall within that range, along with that list
of
numbers
would be additional values as below (Example2)

Example2
Loc Value Departments Cost Center Division
PositionID
3005 0E75 A03 R07 7210001
3005 0E75 A03 R07 7210002
3005 0E75 A03 R07 7210003
3005 0E75 A03 R07 7210004
3005 0E75 A03 R07 7210005

Is this possible?

Thanks,
Lisa W.

SELECT [Field1], [Field2]
FROM [MyTable]
WHERE [Division] BETWEEN 7210001 AND 7210199;