I need help on a query.

[Iram]

Hello.

I have a query with the following fields...
Team
WorkerID
MonitoredByTeamMembers

In the table both "Team" and "WorkerID" are text fields.
"MonitoredByTeamMembers" is a Yes/No checkbox field.

I need to write a query that will pull two records.
I want it to pull up the record that has my "WorkerID" in the "WorkerID"
field (easy enough I just put my workerID in the criteria)

And the other record needs to be a record that has the same "Team" as mine,
and the "MonitoredByTeamMembers" field has to have -1 (which means Yes in the
table). I know that this would go into the Or area but I don't know what to
put where. I need this to be dynamic in case I change my "Team" name. How do
I get the query to see what team I belong to to make it part of the criteria?

Can you help me with this logic?

You guys are awesome!

Iram/mcp

 

[KARL DEWEY]

Try this --
SELECT YourTable.WorkerID, YourTable_1.WorkerID
FROM YourTable INNER JOIN YourTable AS YourTable_1 ON YourTable.Team =
YourTable_1.Team
WHERE (((YourTable_1.MonitoredByTeamMembers)=-1));

 

[Iram]

How would I write this in the query instead of using the SQL format?

 

[Iram]

By the way the three fields below are all in the same table.

Iram

 

[KARL DEWEY]

You will need to do this in design view as the wizard can not do it.

In design view add your table twice. Access will add a sufix to the second
instance of the table using _1 as the sufix.

Click on the field Team in first table and drag to Team in the second table.

Click on field WorkerID in first table and drag it to the Field row of the
grid. In the criteria row enter [Enter WorkerID] so it will prompt for
ID.

Click on field WorkerID in second table and drag it to the Field row of the
grid.

Click on field MonitoredByTeamMembers in second table and drag it to the
Field row of the grid. Uncheck the check box in the Show row. In the
criteria row enter -1 (minus one).

Save the query and run.

 

[Iram]

Hi KARL.
Your instructions are awesome and it works however there is one caveate.
Now that I barely got the chance to test your instructions I see that other
people (other WorkerIDs) monitor the same Team so they are appearing in the
query as well. How do eliminate them from the query?

Your help is greatly appreciated.

Iram/mcp

You will need to do this in design view as the wizard can not do it.

In design view add your table twice. Access will add a sufix to the second
instance of the table using _1 as the sufix.

Click on the field Team in first table and drag to Team in the second table.

Click on field WorkerID in first table and drag it to the Field row of the
grid. In the criteria row enter [Enter WorkerID] so it will prompt for
ID.

Click on field WorkerID in second table and drag it to the Field row of the
grid.

Click on field MonitoredByTeamMembers in second table and drag it to the
Field row of the grid. Uncheck the check box in the Show row. In the
criteria row enter -1 (minus one).

Save the query and run.

By the way the three fields below are all in the same table.

Iram

 

[KARL DEWEY]

I see that other people (other WorkerIDs) monitor the same Team so they are
appearing in the query as well.

In what field does the 'other WorkerIDs' appear?

Perhaps copy the criteria [Enter WorkerID] and paste under that field
and on the same row as the first criteria.

Hi KARL.
Your instructions are awesome and it works however there is one caveate.
Now that I barely got the chance to test your instructions I see that other
people (other WorkerIDs) monitor the same Team so they are appearing in the
query as well. How do eliminate them from the query?

Your help is greatly appreciated.

Iram/mcp

You will need to do this in design view as the wizard can not do it.

In design view add your table twice. Access will add a sufix to the second
instance of the table using _1 as the sufix.

Click on the field Team in first table and drag to Team in the second table.

Click on field WorkerID in first table and drag it to the Field row of the
grid. In the criteria row enter [Enter WorkerID] so it will prompt for
ID.

Click on field WorkerID in second table and drag it to the Field row of the
grid.

Click on field MonitoredByTeamMembers in second table and drag it to the
Field row of the grid. Uncheck the check box in the Show row. In the
criteria row enter -1 (minus one).

Save the query and run.

By the way the three fields below are all in the same table.

Iram



:

Try this --
SELECT YourTable.WorkerID, YourTable_1.WorkerID
FROM YourTable INNER JOIN YourTable AS YourTable_1 ON YourTable.Team =
YourTable_1.Team
WHERE (((YourTable_1.MonitoredByTeamMembers)=-1));


:

Hello.

I have a query with the following fields...
Team
WorkerID
MonitoredByTeamMembers

In the table both "Team" and "WorkerID" are text fields.
"MonitoredByTeamMembers" is a Yes/No checkbox field.

I need to write a query that will pull two records.
I want it to pull up the record that has my "WorkerID" in the "WorkerID"
field (easy enough I just put my workerID in the criteria)

And the other record needs to be a record that has the same "Team" as mine,
and the "MonitoredByTeamMembers" field has to have -1 (which means Yes in the
table). I know that this would go into the Or area but I don't know what to
put where. I need this to be dynamic in case I change my "Team" name. How do
I get the query to see what team I belong to to make it part of the criteria?

Can you help me with this logic?

You guys are awesome!

Iram/mcp