URGENT!!! parameter queries

  • Thread starter Thread starter Colleen
  • Start date Start date
C

Colleen

Is there a way to list parameters.

In other words I have an employee table that shows what
position they are in.

I need to show who is in a position from a list of 10 or
20 positions. Is there a way to list the positions you
are looking for without going the word merge way?

Thanks
Please reply to (e-mail address removed)
 
You might try a query whose SQL looks something like this:

SELECT
[Your Employee Table].*
FROM
[Your Employee Table]
WHERE
[Your Employee Table].[Position] In ("Position 1", "Position 2", "Position
3")

In query design view, you can do this by simply creating a new table based
on your table and placing criteria something like

In ("Position 1", "Position 2", "Position 3")

on your position field.

Alternatively, you might create a second table (say named "Selected
Positions") that lists the positions you're interested in, as in:

Position
Position 1
Position 2
Position 3
..
..
..

and then join this table to your employee table in a query whose SQL looks
something like this:

SELECT
[Your Employee Table].*
FROM
[Your Employee Table]
INNER JOIN
[Selected Positions]
ON
[Your Employee Table].[Position] = [Selected Positions].[Position]
 
I think I need to be more clear. First the list of
position numbers changes week to week and an employee is
only in one position.
-----Original Message-----
You might try a query whose SQL looks something like this:

SELECT
[Your Employee Table].*
FROM
[Your Employee Table]
WHERE
[Your Employee Table].[Position] In ("Position 1", "Position 2", "Position
3")

In query design view, you can do this by simply creating a new table based
on your table and placing criteria something like

In ("Position 1", "Position 2", "Position 3")

on your position field.

Alternatively, you might create a second table (say named "Selected
Positions") that lists the positions you're interested in, as in:

Position
Position 1
Position 2
Position 3
..
..
..

and then join this table to your employee table in a query whose SQL looks
something like this:

SELECT
[Your Employee Table].*
FROM
[Your Employee Table]
INNER JOIN
[Selected Positions]
ON
[Your Employee Table].[Position] = [Selected Positions]. [Position]


Is there a way to list parameters.

In other words I have an employee table that shows what
position they are in.

I need to show who is in a position from a list of 10 or
20 positions. Is there a way to list the positions you
are looking for without going the word merge way?

Thanks
Please reply to (e-mail address removed)
Click to expand...


.
Click to expand...
 
Maybe it would help if you posted a brief example showing a few records of
what you have and what you want to get.

In any case, if the list of positions you are looking for changes from week
to week, it may make sense to store those in a table (the "Selected
Positions" table in my previous post). That way, you don't have to change
the query when the positions you are looking for change.

I've already assumed that each person was only in one position (identified
by a field named "Position" in a table of employees named "Your Employee
Table").

Colleen said:
I think I need to be more clear. First the list of
position numbers changes week to week and an employee is
only in one position.
-----Original Message-----
You might try a query whose SQL looks something like this:

SELECT
[Your Employee Table].*
FROM
[Your Employee Table]
WHERE
[Your Employee Table].[Position] In ("Position 1", "Position 2", "Position
3")

In query design view, you can do this by simply creating a new table based
on your table and placing criteria something like

In ("Position 1", "Position 2", "Position 3")

on your position field.

Alternatively, you might create a second table (say named "Selected
Positions") that lists the positions you're interested in, as in:

Position
Position 1
Position 2
Position 3
..
..
..

and then join this table to your employee table in a query whose SQL looks
something like this:

SELECT
[Your Employee Table].*
FROM
[Your Employee Table]
INNER JOIN
[Selected Positions]
ON
[Your Employee Table].[Position] = [Selected Positions]. [Position]


Is there a way to list parameters.

In other words I have an employee table that shows what
position they are in.

I need to show who is in a position from a list of 10 or
20 positions. Is there a way to list the positions you
are looking for without going the word merge way?

Thanks
Please reply to (e-mail address removed)
Click to expand...


.
Click to expand...
Click to expand...
 
I am using a similar query to that suggested, but I want
to extend it (sample below)

SELECT [All assets].PLANT_NO, [All assets].ITEM_NAME_1
FROM [All assets] INNER JOIN [Track walks]
ON Left([All assets].PLANT_NO,3 )= [Track walks].ELR
AND
ON MID([All assets].PLANT_NO,10,8)<=[Track walk].[Segment
from]
;

but I get a "Syntax error (missing operator) in query
expression" message. Any ideas. Thanks.
-----Original Message-----
You might try a query whose SQL looks something like this:

SELECT
[Your Employee Table].*
FROM
[Your Employee Table]
WHERE
[Your Employee Table].[Position] In ("Position 1", "Position 2", "Position
3")

In query design view, you can do this by simply creating a new table based
on your table and placing criteria something like

In ("Position 1", "Position 2", "Position 3")

on your position field.

Alternatively, you might create a second table (say named "Selected
Positions") that lists the positions you're interested in, as in:

Position
Position 1
Position 2
Position 3
..
..
..

and then join this table to your employee table in a query whose SQL looks
something like this:

SELECT
[Your Employee Table].*
FROM
[Your Employee Table]
INNER JOIN
[Selected Positions]
ON
[Your Employee Table].[Position] = [Selected Positions]. [Position]


Is there a way to list parameters.

In other words I have an employee table that shows what
position they are in.

I need to show who is in a position from a list of 10 or
20 positions. Is there a way to list the positions you
are looking for without going the word merge way?

Thanks
Please reply to (e-mail address removed)
Click to expand...


.
Click to expand...
 
I think you have an extra ON after the AND. You might try:

SELECT [All assets].PLANT_NO, [All assets].ITEM_NAME_1
FROM [All assets] INNER JOIN [Track walks]
ON Left([All assets].PLANT_NO,3 )= [Track walks].ELR
AND
MID([All assets].PLANT_NO,10,8)<=[Track walk].[Segment from]

If that doesn't work, it might be because Access (Jet) is being fussy about
having anything other than plain fields in the INNER JOIN operation. In
that case, you might do the "join" using the WHERE clause instead, as in
something like:

SELECT
[All assets].PLANT_NO,
[All assets].ITEM_NAME_1
FROM
[All assets],
[Track walks]
WHERE
Left([All assets].PLANT_NO,3 )=[Track walks].ELR
AND
MID([All assets].PLANT_NO,10,8)<=[Track walk].[Segment from]

In the long run, you might (if feasible) consider splitting PLANT_NO in "All
assets" into separate fields to identify the ELR and whatever else (track
ID, milepost, yardage, etc.) is buried in there.

Jim said:
I am using a similar query to that suggested, but I want
to extend it (sample below)

SELECT [All assets].PLANT_NO, [All assets].ITEM_NAME_1
FROM [All assets] INNER JOIN [Track walks]
ON Left([All assets].PLANT_NO,3 )= [Track walks].ELR
AND
ON MID([All assets].PLANT_NO,10,8)<=[Track walk].[Segment
from]
;

but I get a "Syntax error (missing operator) in query
expression" message. Any ideas. Thanks.
-----Original Message-----
You might try a query whose SQL looks something like this:

SELECT
[Your Employee Table].*
FROM
[Your Employee Table]
WHERE
[Your Employee Table].[Position] In ("Position 1", "Position 2", "Position
3")

In query design view, you can do this by simply creating a new table based
on your table and placing criteria something like

In ("Position 1", "Position 2", "Position 3")

on your position field.

Alternatively, you might create a second table (say named "Selected
Positions") that lists the positions you're interested in, as in:

Position
Position 1
Position 2
Position 3
..
..
..

and then join this table to your employee table in a query whose SQL looks
something like this:

SELECT
[Your Employee Table].*
FROM
[Your Employee Table]
INNER JOIN
[Selected Positions]
ON
[Your Employee Table].[Position] = [Selected Positions]. [Position]


Is there a way to list parameters.

In other words I have an employee table that shows what
position they are in.

I need to show who is in a position from a list of 10 or
20 positions. Is there a way to list the positions you
are looking for without going the word merge way?

Thanks
Please reply to (e-mail address removed)
Click to expand...


.
Click to expand...
Click to expand...
 
Brian

Sorry I have only just replied. Thanks for your help, that
fixed it.
-----Original Message-----
I think you have an extra ON after the AND. You might try:

SELECT [All assets].PLANT_NO, [All assets].ITEM_NAME_1
FROM [All assets] INNER JOIN [Track walks]
ON Left([All assets].PLANT_NO,3 )= [Track walks].ELR
AND
MID([All assets].PLANT_NO,10,8)<=[Track walk].[Segment from]

If that doesn't work, it might be because Access (Jet) is being fussy about
having anything other than plain fields in the INNER JOIN operation. In
that case, you might do the "join" using the WHERE clause instead, as in
something like:

SELECT
[All assets].PLANT_NO,
[All assets].ITEM_NAME_1
FROM
[All assets],
[Track walks]
WHERE
Left([All assets].PLANT_NO,3 )=[Track walks].ELR
AND
MID([All assets].PLANT_NO,10,8)<=[Track walk].[Segment from]

In the long run, you might (if feasible) consider splitting PLANT_NO in "All
assets" into separate fields to identify the ELR and whatever else (track
ID, milepost, yardage, etc.) is buried in there.

I am using a similar query to that suggested, but I want
to extend it (sample below)

SELECT [All assets].PLANT_NO, [All assets].ITEM_NAME_1
FROM [All assets] INNER JOIN [Track walks]
ON Left([All assets].PLANT_NO,3 )= [Track walks].ELR
AND
ON MID([All assets].PLANT_NO,10,8)<=[Track walk]. [Segment
from]
;

but I get a "Syntax error (missing operator) in query
expression" message. Any ideas. Thanks.
-----Original Message-----
You might try a query whose SQL looks something like this:

SELECT
[Your Employee Table].*
FROM
[Your Employee Table]
WHERE
[Your Employee Table].[Position] In ("Position 1", "Position 2", "Position
3")

In query design view, you can do this by simply
Click to expand...
creating
a new table based
on your table and placing criteria something like

In ("Position 1", "Position 2", "Position 3")

on your position field.

Alternatively, you might create a second table (say named "Selected
Positions") that lists the positions you're interested in, as in:

Position
Position 1
Position 2
Position 3
..
..
..

and then join this table to your employee table in a query whose SQL looks
something like this:

SELECT
[Your Employee Table].*
FROM
[Your Employee Table]
INNER JOIN
[Selected Positions]
ON
[Your Employee Table].[Position] = [Selected
Click to expand...
Positions].
[Position]
"Colleen" <[email protected]> wrote
Click to expand...
in
message
Is there a way to list parameters.

In other words I have an employee table that shows what
position they are in.

I need to show who is in a position from a list of 10 or
20 positions. Is there a way to list the positions you
are looking for without going the word merge way?

Thanks
Please reply to (e-mail address removed)



.
Click to expand...
Click to expand...


.
Click to expand...
 
Glad I could help.

Jim said:
Brian

Sorry I have only just replied. Thanks for your help, that
fixed it.
-----Original Message-----
I think you have an extra ON after the AND. You might try:

SELECT [All assets].PLANT_NO, [All assets].ITEM_NAME_1
FROM [All assets] INNER JOIN [Track walks]
ON Left([All assets].PLANT_NO,3 )= [Track walks].ELR
AND
MID([All assets].PLANT_NO,10,8)<=[Track walk].[Segment from]

If that doesn't work, it might be because Access (Jet) is being fussy about
having anything other than plain fields in the INNER JOIN operation. In
that case, you might do the "join" using the WHERE clause instead, as in
something like:

SELECT
[All assets].PLANT_NO,
[All assets].ITEM_NAME_1
FROM
[All assets],
[Track walks]
WHERE
Left([All assets].PLANT_NO,3 )=[Track walks].ELR
AND
MID([All assets].PLANT_NO,10,8)<=[Track walk].[Segment from]

In the long run, you might (if feasible) consider splitting PLANT_NO in "All
assets" into separate fields to identify the ELR and whatever else (track
ID, milepost, yardage, etc.) is buried in there.

I am using a similar query to that suggested, but I want
to extend it (sample below)

SELECT [All assets].PLANT_NO, [All assets].ITEM_NAME_1
FROM [All assets] INNER JOIN [Track walks]
ON Left([All assets].PLANT_NO,3 )= [Track walks].ELR
AND
ON MID([All assets].PLANT_NO,10,8)<=[Track walk]. [Segment
from]
;

but I get a "Syntax error (missing operator) in query
expression" message. Any ideas. Thanks.
-----Original Message-----
You might try a query whose SQL looks something like this:

SELECT
[Your Employee Table].*
FROM
[Your Employee Table]
WHERE
[Your Employee Table].[Position] In ("Position
1", "Position 2", "Position
3")

In query design view, you can do this by simply creating
a new table based
on your table and placing criteria something like

In ("Position 1", "Position 2", "Position 3")

on your position field.

Alternatively, you might create a second table (say
named "Selected
Positions") that lists the positions you're interested
in, as in:

Position
Position 1
Position 2
Position 3
..
..
..

and then join this table to your employee table in a
query whose SQL looks
something like this:

SELECT
[Your Employee Table].*
FROM
[Your Employee Table]
INNER JOIN
[Selected Positions]
ON
[Your Employee Table].[Position] = [Selected Positions].
[Position]


message
Is there a way to list parameters.

In other words I have an employee table that shows what
position they are in.

I need to show who is in a position from a list of 10 or
20 positions. Is there a way to list the positions you
are looking for without going the word merge way?

Thanks
Please reply to (e-mail address removed)



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top