Query criteria that references values of column in another table

I'm new to programming in Access so I apologize if I don't give all the
needed information. I'm working with creating a plan to eliminate/generate
spares for obsolete electronics in a factory. I have 1 table with the
equipment information
Equipment:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00
2 Reliance IQ2000 PDM-30 $1,516.00
3 Reliance IQ2000 PDM-30 $3,500.00
4 Emerson FX455 $1,600.00
5 Emerson FX455 $1,516.00
Model Number is the text field I want to use as a
Option is the primary key used to determine what will be replaced
Another table list the equipment that has been selected to be replaced
Primary Option
1 4
2 2

I was able to generate the following query with relationships:
Option Manufacturer Model Number Cost
2 Reliance IQ2000 PDM-30 $1,516.00
4 Emerson FX455 $1,600.00


I want to generate two more queries:
First - Euipment with model number as an exact match to any model number in
the query I currently have AND not a Option already selected
Ex:
Option Manufacturer Model Number Cost
3 Reliance IQ2000 PDM-30 $3,500.00
5 Emerson FX455 $1,516.00

Second - Equipment that Model Number does not match Model Number of the
criteria I already have.
Ex:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00

I would greatly appreciate any help avaible and am more than happy to give
more information if need. One thing to remember is my database contains
about 500 entries and will be used by multiple people to I don't want to type
model in the criteria.

Dear Tyzon:

For someone new to Access you have a fairly advanced set of problems.

Question 1:

I believe you want to eliminate 1, 3, and 5 from the set 1, 2, 3, 4, 5,
based on 1, 3, and 5 being in "Another table". Now, "Another table" has two
columns, Primary and Option. It would seem then that the set 1, 3, and 5
generated from this includes all values in both columns Primary and Option.
If that is not the case, then please explain why these 3 are the ones
eliminated. If it is the case, then:

SELECT DISTINCT EValue
FROM (
SELECT Primary Evalue
FROM AnotherTable
UNION ALL
SELECT Option Evalue
FROM AnotherTable)

The above will give you the set 1, 3, 5 which are to be eliminate. Then:

SELECT Option, Manufacturer, Model, Number, Cost
From Equipment
WHERE Option NOT IN (
SELECT DISTINCT EValue
FROM (
SELECT Primary Evalue
FROM AnotherTable
UNION ALL
SELECT Option Evalue
FROM AnotherTable)
ORDER BY Option

This incorporates the first query to eliminate those values.

I will post this and go to work on your further questions as time allows.

Tom Ellison


"TyzonZ" wrote in message
...
I'm new to programming in Access so I apologize if I don't give all the
needed information. I'm working with creating a plan to
eliminate/generate
spares for obsolete electronics in a factory. I have 1 table with the
equipment information
Equipment:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00
2 Reliance IQ2000 PDM-30 $1,516.00
3 Reliance IQ2000 PDM-30 $3,500.00
4 Emerson FX455 $1,600.00
5 Emerson FX455 $1,516.00
Model Number is the text field I want to use as a
Option is the primary key used to determine what will be replaced
Another table list the equipment that has been selected to be replaced
Primary Option
1 4
2 2

I was able to generate the following query with relationships:
Option Manufacturer Model Number Cost
2 Reliance IQ2000 PDM-30 $1,516.00
4 Emerson FX455 $1,600.00


I want to generate two more queries:
First - Euipment with model number as an exact match to any model number
in
the query I currently have AND not a Option already selected
Ex:
Option Manufacturer Model Number Cost
3 Reliance IQ2000 PDM-30 $3,500.00
5 Emerson FX455 $1,516.00

Second - Equipment that Model Number does not match Model Number of the
criteria I already have.
Ex:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00

I would greatly appreciate any help avaible and am more than happy to give
more information if need. One thing to remember is my database contains
about 500 entries and will be used by multiple people to I don't want to
type
model in the criteria.

Dear Tyzon:

What do you mean by "the criteria I alread have." Is this a value of the
Model Number entered in a control on a form, in a variable you have stored,
or something else. What is the basis on which you expect only Option 1 to
be selected?

Tom Ellison


"TyzonZ" wrote in message
...
I'm new to programming in Access so I apologize if I don't give all the
needed information. I'm working with creating a plan to
eliminate/generate
spares for obsolete electronics in a factory. I have 1 table with the
equipment information
Equipment:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00
2 Reliance IQ2000 PDM-30 $1,516.00
3 Reliance IQ2000 PDM-30 $3,500.00
4 Emerson FX455 $1,600.00
5 Emerson FX455 $1,516.00
Model Number is the text field I want to use as a
Option is the primary key used to determine what will be replaced
Another table list the equipment that has been selected to be replaced
Primary Option
1 4
2 2

I was able to generate the following query with relationships:
Option Manufacturer Model Number Cost
2 Reliance IQ2000 PDM-30 $1,516.00
4 Emerson FX455 $1,600.00


I want to generate two more queries:
First - Euipment with model number as an exact match to any model number
in
the query I currently have AND not a Option already selected
Ex:
Option Manufacturer Model Number Cost
3 Reliance IQ2000 PDM-30 $3,500.00
5 Emerson FX455 $1,516.00

Second - Equipment that Model Number does not match Model Number of the
criteria I already have.
Ex:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00

I would greatly appreciate any help avaible and am more than happy to give
more information if need. One thing to remember is my database contains
about 500 entries and will be used by multiple people to I don't want to
type
model in the criteria.

Dear Tyzon:

I failed to mention that you must change "AnotherTable" to the actual name
of that table, which I did not see you specifiy.

Tom Ellison


"Tom Ellison" wrote in message
...
Dear Tyzon:

For someone new to Access you have a fairly advanced set of problems.

Question 1:

I believe you want to eliminate 1, 3, and 5 from the set 1, 2, 3, 4, 5,
based on 1, 3, and 5 being in "Another table". Now, "Another table" has
two columns, Primary and Option. It would seem then that the set 1, 3,
and 5 generated from this includes all values in both columns Primary and
Option. If that is not the case, then please explain why these 3 are the
ones eliminated. If it is the case, then:

SELECT DISTINCT EValue
FROM (
SELECT Primary Evalue
FROM AnotherTable
UNION ALL
SELECT Option Evalue
FROM AnotherTable)

The above will give you the set 1, 3, 5 which are to be eliminate. Then:

SELECT Option, Manufacturer, Model, Number, Cost
From Equipment
WHERE Option NOT IN (
SELECT DISTINCT EValue
FROM (
SELECT Primary Evalue
FROM AnotherTable
UNION ALL
SELECT Option Evalue
FROM AnotherTable)
ORDER BY Option

This incorporates the first query to eliminate those values.

I will post this and go to work on your further questions as time allows.

Tom Ellison


"TyzonZ" wrote in message
...
I'm new to programming in Access so I apologize if I don't give all the
needed information. I'm working with creating a plan to
eliminate/generate
spares for obsolete electronics in a factory. I have 1 table with the
equipment information
Equipment:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00
2 Reliance IQ2000 PDM-30 $1,516.00
3 Reliance IQ2000 PDM-30 $3,500.00
4 Emerson FX455 $1,600.00
5 Emerson FX455 $1,516.00
Model Number is the text field I want to use as a
Option is the primary key used to determine what will be replaced
Another table list the equipment that has been selected to be replaced
Primary Option
1 4
2 2

I was able to generate the following query with relationships:
Option Manufacturer Model Number Cost
2 Reliance IQ2000 PDM-30 $1,516.00
4 Emerson FX455 $1,600.00


I want to generate two more queries:
First - Euipment with model number as an exact match to any model number
in
the query I currently have AND not a Option already selected
Ex:
Option Manufacturer Model Number Cost
3 Reliance IQ2000 PDM-30 $3,500.00
5 Emerson FX455 $1,516.00

Second - Equipment that Model Number does not match Model Number of the
criteria I already have.
Ex:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00

I would greatly appreciate any help avaible and am more than happy to
give
more information if need. One thing to remember is my database contains
about 500 entries and will be used by multiple people to I don't want to
type
model in the criteria.

Tom, thank you for posting so quickly. Primary in the Another Table only
means that field is used for the primary key. I do not need the field in any
criteria. I'm a little confused about what Evalue means in your post for the
solution. Looking at your post and considering I only need the option
selected and the drive model I believe I should use the Query Relationship
query in SQL code.

A refined definition of the problem:
1st Query returns entries where Equipment.Model Number=Query
Relationship.Model Number AND NOT Equipment.Option=Query Relationship.Option

2nd Query returns entries where NOT Equipment.Model Number=Query
Relationship.Model Number AND NOT Equipment.Option=Query Relationship.Option
(This what I was trying to say when I mentioned "the criteria I alread have")

I think that should help clear up my question. Thank you again for your
continued help. I should be there with another suggestion of the code and
clarification of what Evalue means.

"Tom Ellison" wrote:

Dear Tyzon:

I failed to mention that you must change "AnotherTable" to the actual name
of that table, which I did not see you specifiy.

Tom Ellison


"Tom Ellison" wrote in message
...
Dear Tyzon:

For someone new to Access you have a fairly advanced set of problems.

Question 1:

I believe you want to eliminate 1, 3, and 5 from the set 1, 2, 3, 4, 5,
based on 1, 3, and 5 being in "Another table". Now, "Another table" has
two columns, Primary and Option. It would seem then that the set 1, 3,
and 5 generated from this includes all values in both columns Primary and
Option. If that is not the case, then please explain why these 3 are the
ones eliminated. If it is the case, then:

SELECT DISTINCT EValue
FROM (
SELECT Primary Evalue
FROM AnotherTable
UNION ALL
SELECT Option Evalue
FROM AnotherTable)

The above will give you the set 1, 3, 5 which are to be eliminate. Then:

SELECT Option, Manufacturer, Model, Number, Cost
From Equipment
WHERE Option NOT IN (
SELECT DISTINCT EValue
FROM (
SELECT Primary Evalue
FROM AnotherTable
UNION ALL
SELECT Option Evalue
FROM AnotherTable)
ORDER BY Option

This incorporates the first query to eliminate those values.

I will post this and go to work on your further questions as time allows.

Tom Ellison


"TyzonZ" wrote in message
...
I'm new to programming in Access so I apologize if I don't give all the
needed information. I'm working with creating a plan to
eliminate/generate
spares for obsolete electronics in a factory. I have 1 table with the
equipment information
Equipment:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00
2 Reliance IQ2000 PDM-30 $1,516.00
3 Reliance IQ2000 PDM-30 $3,500.00
4 Emerson FX455 $1,600.00
5 Emerson FX455 $1,516.00
Model Number is the text field I want to use as a
Option is the primary key used to determine what will be replaced
Another table list the equipment that has been selected to be replaced
Primary Option
1 4
2 2

I was able to generate the following query with relationships:
Option Manufacturer Model Number Cost
2 Reliance IQ2000 PDM-30 $1,516.00
4 Emerson FX455 $1,600.00


I want to generate two more queries:
First - Euipment with model number as an exact match to any model number
in
the query I currently have AND not a Option already selected
Ex:
Option Manufacturer Model Number Cost
3 Reliance IQ2000 PDM-30 $3,500.00
5 Emerson FX455 $1,516.00

Second - Equipment that Model Number does not match Model Number of the
criteria I already have.
Ex:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00

I would greatly appreciate any help avaible and am more than happy to
give
more information if need. One thing to remember is my database contains
about 500 entries and will be used by multiple people to I don't want to
type
model in the criteria.

Dear Tyzon:

Evalue was just a column name I dreamed up, meant to mean values to
Eliminate. I believe that's what you were saying you mean to do.

Now, for problem 1, what is this:

Equipment.Model Number=QueryRelationship.Model Number
AND NOT Equipment.Option=Query Relationship.Option

Equipment.Model Number would be the column on which you want to filter.
QueryRelationship would seem to be some query, but I saw no mention of this
before. Now I'm completely lost. However, if you add QueryRelationship to
the query, JOINed as needed or as a cross product or whatever, then the
above criteria should be fine.

For your query2, again this QueryRelationship is something you didn't
mention before, and I have no concept of what it does or where it fits in.
Sorry, but without antecedents it just will not be possible to follow your
logic.

Perhaps the best help I can give is to try to help you gain perspective on
how little I know of the particulars of your situation. If you omit some
essential element of the problem initially, like QueryRelationship, and then
drop it in without explanation, well it's no wonder I'm suddenly
unenlightened.

May I suggest you give a bit of sample data from all your tables, post the
SQL to whatever queries you have, whether working or not, and tell me what
the results should look like. Then explain why this is what the results
should look like, and what logic produces these results. That should put me
completely in the picture and enable me to craft some kind of solution and
explain how it works.

OK?

Tom Ellison


"TyzonZ" wrote in message
...
Tom, thank you for posting so quickly. Primary in the Another Table only
means that field is used for the primary key. I do not need the field in
any
criteria. I'm a little confused about what Evalue means in your post for
the
solution. Looking at your post and considering I only need the option
selected and the drive model I believe I should use the Query Relationship
query in SQL code.

A refined definition of the problem:
1st Query returns entries where Equipment.Model Number=Query
Relationship.Model Number AND NOT Equipment.Option=Query
Relationship.Option

2nd Query returns entries where NOT Equipment.Model Number=Query
Relationship.Model Number AND NOT Equipment.Option=Query
Relationship.Option
(This what I was trying to say when I mentioned "the criteria I alread
have")

I think that should help clear up my question. Thank you again for your
continued help. I should be there with another suggestion of the code and
clarification of what Evalue means.

"Tom Ellison" wrote:

Dear Tyzon:

I failed to mention that you must change "AnotherTable" to the actual
name
of that table, which I did not see you specifiy.

Tom Ellison


"Tom Ellison" wrote in message
...
Dear Tyzon:

For someone new to Access you have a fairly advanced set of problems.

Question 1:

I believe you want to eliminate 1, 3, and 5 from the set 1, 2, 3, 4, 5,
based on 1, 3, and 5 being in "Another table". Now, "Another table"
has
two columns, Primary and Option. It would seem then that the set 1, 3,
and 5 generated from this includes all values in both columns Primary
and
Option. If that is not the case, then please explain why these 3 are
the
ones eliminated. If it is the case, then:

SELECT DISTINCT EValue
FROM (
SELECT Primary Evalue
FROM AnotherTable
UNION ALL
SELECT Option Evalue
FROM AnotherTable)

The above will give you the set 1, 3, 5 which are to be eliminate.
Then:

SELECT Option, Manufacturer, Model, Number, Cost
From Equipment
WHERE Option NOT IN (
SELECT DISTINCT EValue
FROM (
SELECT Primary Evalue
FROM AnotherTable
UNION ALL
SELECT Option Evalue
FROM AnotherTable)
ORDER BY Option

This incorporates the first query to eliminate those values.

I will post this and go to work on your further questions as time
allows.

Tom Ellison


"TyzonZ" wrote in message
...
I'm new to programming in Access so I apologize if I don't give all
the
needed information. I'm working with creating a plan to
eliminate/generate
spares for obsolete electronics in a factory. I have 1 table with the
equipment information
Equipment:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00
2 Reliance IQ2000 PDM-30 $1,516.00
3 Reliance IQ2000 PDM-30 $3,500.00
4 Emerson FX455 $1,600.00
5 Emerson FX455 $1,516.00
Model Number is the text field I want to use as a
Option is the primary key used to determine what will be replaced
Another table list the equipment that has been selected to be replaced
Primary Option
1 4
2 2

I was able to generate the following query with relationships:
Option Manufacturer Model Number Cost
2 Reliance IQ2000 PDM-30 $1,516.00
4 Emerson FX455 $1,600.00


I want to generate two more queries:
First - Euipment with model number as an exact match to any model
number
in
the query I currently have AND not a Option already selected
Ex:
Option Manufacturer Model Number Cost
3 Reliance IQ2000 PDM-30 $3,500.00
5 Emerson FX455 $1,516.00

Second - Equipment that Model Number does not match Model Number of
the
criteria I already have.
Ex:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00

I would greatly appreciate any help avaible and am more than happy to
give
more information if need. One thing to remember is my database
contains
about 500 entries and will be used by multiple people to I don't want
to
type
model in the criteria.

Tom, here is the information from my first and last post combined.
Descriptive labels have now been specified.

Table named "Equipment":
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00
2 Reliance IQ2000 PDM-30 $1,516.00
3 Reliance IQ2000 PDM-30 $3,500.00
4 Emerson FX455 $1,600.00
5 Emerson FX455 $1,516.00

Query named "Query Relationship":
Option Manufacturer Model Number Cost
2 Reliance IQ2000 PDM-30 $1,516.00
4 Emerson FX455 $1,600.00

Query1 - Returns entries from Equipment table where (Equipment.Model Number
EQUALS Query Relationship.Model Number) AND (Equipment.Option DOES NOT EQUAL
Query Relationship.Option)
Ex:
Option Manufacturer Model Number Cost
3 Reliance IQ2000 PDM-30 $3,500.00
5 Emerson FX455 $1,516.00

Query2 - Returns entries from Equipment table where (Equipment.Model Number
DOES NOT EQUAL Query Relationship.Model Number) AND (Equipment.Option DOES
NOT EQUAL Query Relationship.Option)
Ex:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00

Sorry about the confusion. Hope this clears things up. Again Query
Relationship (Query) and Equipment (Table) are working correctly. I just
need help with generating Query1 and Query2. Thank you again.

Tyzon


"Tom Ellison" wrote:

Dear Tyzon:

Evalue was just a column name I dreamed up, meant to mean values to
Eliminate. I believe that's what you were saying you mean to do.

Now, for problem 1, what is this:

Equipment.Model Number=QueryRelationship.Model Number
AND NOT Equipment.Option=Query Relationship.Option

Equipment.Model Number would be the column on which you want to filter.
QueryRelationship would seem to be some query, but I saw no mention of this
before. Now I'm completely lost. However, if you add QueryRelationship to
the query, JOINed as needed or as a cross product or whatever, then the
above criteria should be fine.

For your query2, again this QueryRelationship is something you didn't
mention before, and I have no concept of what it does or where it fits in.
Sorry, but without antecedents it just will not be possible to follow your
logic.

Perhaps the best help I can give is to try to help you gain perspective on
how little I know of the particulars of your situation. If you omit some
essential element of the problem initially, like QueryRelationship, and then
drop it in without explanation, well it's no wonder I'm suddenly
unenlightened.

May I suggest you give a bit of sample data from all your tables, post the
SQL to whatever queries you have, whether working or not, and tell me what
the results should look like. Then explain why this is what the results
should look like, and what logic produces these results. That should put me
completely in the picture and enable me to craft some kind of solution and
explain how it works.

OK?

Tom Ellison


"TyzonZ" wrote in message
...
Tom, thank you for posting so quickly. Primary in the Another Table only
means that field is used for the primary key. I do not need the field in
any
criteria. I'm a little confused about what Evalue means in your post for
the
solution. Looking at your post and considering I only need the option
selected and the drive model I believe I should use the Query Relationship
query in SQL code.

A refined definition of the problem:
1st Query returns entries where Equipment.Model Number=Query
Relationship.Model Number AND NOT Equipment.Option=Query
Relationship.Option

2nd Query returns entries where NOT Equipment.Model Number=Query
Relationship.Model Number AND NOT Equipment.Option=Query
Relationship.Option
(This what I was trying to say when I mentioned "the criteria I alread
have")

I think that should help clear up my question. Thank you again for your
continued help. I should be there with another suggestion of the code and
clarification of what Evalue means.

"Tom Ellison" wrote:

Dear Tyzon:

I failed to mention that you must change "AnotherTable" to the actual
name
of that table, which I did not see you specifiy.

Tom Ellison


"Tom Ellison" wrote in message
...
Dear Tyzon:

For someone new to Access you have a fairly advanced set of problems.

Question 1:

I believe you want to eliminate 1, 3, and 5 from the set 1, 2, 3, 4, 5,
based on 1, 3, and 5 being in "Another table". Now, "Another table"
has
two columns, Primary and Option. It would seem then that the set 1, 3,
and 5 generated from this includes all values in both columns Primary
and
Option. If that is not the case, then please explain why these 3 are
the
ones eliminated. If it is the case, then:

SELECT DISTINCT EValue
FROM (
SELECT Primary Evalue
FROM AnotherTable
UNION ALL
SELECT Option Evalue
FROM AnotherTable)

The above will give you the set 1, 3, 5 which are to be eliminate.
Then:

SELECT Option, Manufacturer, Model, Number, Cost
From Equipment
WHERE Option NOT IN (
SELECT DISTINCT EValue
FROM (
SELECT Primary Evalue
FROM AnotherTable
UNION ALL
SELECT Option Evalue
FROM AnotherTable)
ORDER BY Option

This incorporates the first query to eliminate those values.

I will post this and go to work on your further questions as time
allows.

Tom Ellison


"TyzonZ" wrote in message
...
I'm new to programming in Access so I apologize if I don't give all
the
needed information. I'm working with creating a plan to
eliminate/generate
spares for obsolete electronics in a factory. I have 1 table with the
equipment information
Equipment:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00
2 Reliance IQ2000 PDM-30 $1,516.00
3 Reliance IQ2000 PDM-30 $3,500.00
4 Emerson FX455 $1,600.00
5 Emerson FX455 $1,516.00
Model Number is the text field I want to use as a
Option is the primary key used to determine what will be replaced
Another table list the equipment that has been selected to be replaced
Primary Option
1 4
2 2

I was able to generate the following query with relationships:
Option Manufacturer Model Number Cost
2 Reliance IQ2000 PDM-30 $1,516.00
4 Emerson FX455 $1,600.00


I want to generate two more queries:
First - Euipment with model number as an exact match to any model
number
in
the query I currently have AND not a Option already selected
Ex:
Option Manufacturer Model Number Cost
3 Reliance IQ2000 PDM-30 $3,500.00
5 Emerson FX455 $1,516.00

Second - Equipment that Model Number does not match Model Number of
the
criteria I already have.
Ex:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00

I would greatly appreciate any help avaible and am more than happy to
give
more information if need. One thing to remember is my database
contains
about 500 entries and will be used by multiple people to I don't want
to
type
model in the criteria.

Dear Tyzon:

Thanks. It appears I may have missed some of what you provided. I'll
insert some responses within your last post below.

Tom Ellison


"TyzonZ" wrote in message
...
Tom, here is the information from my first and last post combined.
Descriptive labels have now been specified.

Table named "Equipment":
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00
2 Reliance IQ2000 PDM-30 $1,516.00
3 Reliance IQ2000 PDM-30 $3,500.00
4 Emerson FX455 $1,600.00
5 Emerson FX455 $1,516.00

Query named "Query Relationship":
Option Manufacturer Model Number Cost
2 Reliance IQ2000 PDM-30 $1,516.00
4 Emerson FX455 $1,600.00

Query1 - Returns entries from Equipment table where (Equipment.Model
Number
EQUALS Query Relationship.Model Number) AND (Equipment.Option DOES NOT
EQUAL
Query Relationship.Option)
Ex:
Option Manufacturer Model Number Cost
3 Reliance IQ2000 PDM-30 $3,500.00
5 Emerson FX455 $1,516.00

The thing confusing about your description is you use of EQUALS. I believe
you are referring to the existence of rows. That is, eliminate options 3
and 5 from the set {1, 2, 3, 4, 5} leaving {1, 2, 4} but only where Model
Number exists in Query 1. Here goes:

SELECT Option, Manufacturer, [Model Number], Cost
FROM Equipment E
WHERE NOT EXISTS (
SELECT * FROM Query1 Q
WHERE Q.Option = E.Option)
AND EXISTS (
SELECT * FROM Query1 Q
WHERE Q.[Model Number] = E.[Model Number])
ORDER BY Option

I hope I've understood you and coded that properly. If you find you need to
change anything in what I wrote, and it doesn't work, please post back just
how you changed it, and what didn't work (error message, wrong results,
etc.)

Query2 - Returns entries from Equipment table where (Equipment.Model
Number
DOES NOT EQUAL Query Relationship.Model Number) AND (Equipment.Option DOES
NOT EQUAL Query Relationship.Option)
Ex:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00

Almost the same thing:

SELECT Option, Manufacturer, [Model Number], Cost
FROM Equipment E
WHERE NOT EXISTS (
SELECT * FROM Query1 Q
WHERE Q.Option = E.Option)
AND NOT EXISTS (
SELECT * FROM Query1 Q
WHERE Q.[Model Number] = E.[Model Number])
ORDER BY Option

Just add a NOT there in the middle.

Am I doing any better?

Sorry about the confusion. Hope this clears things up. Again Query
Relationship (Query) and Equipment (Table) are working correctly. I just
need help with generating Query1 and Query2. Thank you again.

Tyzon


"Tom Ellison" wrote:

Dear Tyzon:

Evalue was just a column name I dreamed up, meant to mean values to
Eliminate. I believe that's what you were saying you mean to do.

Now, for problem 1, what is this:

Equipment.Model Number=QueryRelationship.Model Number
AND NOT Equipment.Option=Query Relationship.Option

Equipment.Model Number would be the column on which you want to filter.
QueryRelationship would seem to be some query, but I saw no mention of
this
before. Now I'm completely lost. However, if you add QueryRelationship
to
the query, JOINed as needed or as a cross product or whatever, then the
above criteria should be fine.

For your query2, again this QueryRelationship is something you didn't
mention before, and I have no concept of what it does or where it fits
in.
Sorry, but without antecedents it just will not be possible to follow
your
logic.

Perhaps the best help I can give is to try to help you gain perspective
on
how little I know of the particulars of your situation. If you omit some
essential element of the problem initially, like QueryRelationship, and
then
drop it in without explanation, well it's no wonder I'm suddenly
unenlightened.

May I suggest you give a bit of sample data from all your tables, post
the
SQL to whatever queries you have, whether working or not, and tell me
what
the results should look like. Then explain why this is what the results
should look like, and what logic produces these results. That should put
me
completely in the picture and enable me to craft some kind of solution
and
explain how it works.

OK?

Tom Ellison


"TyzonZ" wrote in message
...
Tom, thank you for posting so quickly. Primary in the Another Table
only
means that field is used for the primary key. I do not need the field
in
any
criteria. I'm a little confused about what Evalue means in your post
for
the
solution. Looking at your post and considering I only need the option
selected and the drive model I believe I should use the Query
Relationship
query in SQL code.

A refined definition of the problem:
1st Query returns entries where Equipment.Model Number=Query
Relationship.Model Number AND NOT Equipment.Option=Query
Relationship.Option

2nd Query returns entries where NOT Equipment.Model Number=Query
Relationship.Model Number AND NOT Equipment.Option=Query
Relationship.Option
(This what I was trying to say when I mentioned "the criteria I alread
have")

I think that should help clear up my question. Thank you again for
your
continued help. I should be there with another suggestion of the code
and
clarification of what Evalue means.

"Tom Ellison" wrote:

Dear Tyzon:

I failed to mention that you must change "AnotherTable" to the actual
name
of that table, which I did not see you specifiy.

Tom Ellison


"Tom Ellison" wrote in message
...
Dear Tyzon:

For someone new to Access you have a fairly advanced set of
problems.

Question 1:

I believe you want to eliminate 1, 3, and 5 from the set 1, 2, 3, 4,
5,
based on 1, 3, and 5 being in "Another table". Now, "Another table"
has
two columns, Primary and Option. It would seem then that the set 1,
3,
and 5 generated from this includes all values in both columns
Primary
and
Option. If that is not the case, then please explain why these 3 are
the
ones eliminated. If it is the case, then:

SELECT DISTINCT EValue
FROM (
SELECT Primary Evalue
FROM AnotherTable
UNION ALL
SELECT Option Evalue
FROM AnotherTable)

The above will give you the set 1, 3, 5 which are to be eliminate.
Then:

SELECT Option, Manufacturer, Model, Number, Cost
From Equipment
WHERE Option NOT IN (
SELECT DISTINCT EValue
FROM (
SELECT Primary Evalue
FROM AnotherTable
UNION ALL
SELECT Option Evalue
FROM AnotherTable)
ORDER BY Option

This incorporates the first query to eliminate those values.

I will post this and go to work on your further questions as time
allows.

Tom Ellison


"TyzonZ" wrote in message
...
I'm new to programming in Access so I apologize if I don't give all
the
needed information. I'm working with creating a plan to
eliminate/generate
spares for obsolete electronics in a factory. I have 1 table with
the
equipment information
Equipment:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00
2 Reliance IQ2000 PDM-30 $1,516.00
3 Reliance IQ2000 PDM-30 $3,500.00
4 Emerson FX455 $1,600.00
5 Emerson FX455 $1,516.00
Model Number is the text field I want to use as a
Option is the primary key used to determine what will be replaced
Another table list the equipment that has been selected to be
replaced
Primary Option
1 4
2 2

I was able to generate the following query with relationships:
Option Manufacturer Model Number Cost
2 Reliance IQ2000 PDM-30 $1,516.00
4 Emerson FX455 $1,600.00


I want to generate two more queries:
First - Euipment with model number as an exact match to any model
number
in
the query I currently have AND not a Option already selected
Ex:
Option Manufacturer Model Number Cost
3 Reliance IQ2000 PDM-30 $3,500.00
5 Emerson FX455 $1,516.00

Second - Equipment that Model Number does not match Model Number of
the
criteria I already have.
Ex:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00

I would greatly appreciate any help avaible and am more than happy
to
give
more information if need. One thing to remember is my database
contains
about 500 entries and will be used by multiple people to I don't
want
to
type
model in the criteria.

My examples were a little simplified as far as the data contained goes. I'll
give you the code I have and describe the error messages I'm receiving. I'd
preface this with Drives the table we referred to as Equipment previously.
DRC Drives Selected is the query we were referring to as Query Relationship
previously.

SELECT Drives.Option, Drives.[Product Line], Drives.Asset, Drives.Use,
Drives.[Year Installed], Drives.[Drive Mfg], Drives.[Drive Family],
Drives.[Model Number], Drives.[Drive Controller], Drives.[Voltage
(Magnitude)], Drives.[Voltage (Units)], Drives.[Software Title],
Drives.[Software operating system], Drives.[Drive Size/Rating (Magnitude)],
Drives.[Drive Size/Rating (Units)], Drives.[Old MTR KC], Drives.[Current
Spares], Drives.[Drive Life Cycle], Drives.[Replacement Option],
Drives.[Estimated Lead Time for Replacement], Drives.[Replacement Drive],
Drives.[New Mtr/Cbl], Drives.[Asset Engineer's Scope], Drives.[Drive KC],
Drives.Drive, Drives.[New MTR KC], Drives.[New MTR], Drives.[Processor KC],
Drives.Processor, Drives.[Eng/Hr], Drives.[Man/Hr], Drives.[Date Last Saved]
FROM Drives
WHERE NOT EXISTS (
SELECT * FROM Drives
WHERE [DRC Drives Selected].Option = Drives.Option)
AND EXISTS (
SELECT * FROM Drives
WHERE [DRC Drives Selected].[Model Number] = Drives.[Model Number])
ORDER BY Option;

When I try to view file I get a Enter Parameter Value prompt for
Drives.Option. It is as if SQL wants to look at a specific record. An
interesting note is that switching Drives.Option = [DRC Drives
Selected].Option gives Enter Parameter Value prompt for [DRC Drives
Selected].Option. Maybe this will give you some insight if you know how the
= is processed. Is there any way I can attach the file to give you a better
example of what I'm trying to do? I do have an excel file that performs
this, but formula length limits do not allow my "DRC Drives Selected" to
contain as many entries as need. I am willing to attach either of these
files if there is a method to do that?

I appreciate your persistance.

Tyzon

"Tom Ellison" wrote:

Dear Tyzon:

Thanks. It appears I may have missed some of what you provided. I'll
insert some responses within your last post below.

Tom Ellison


"TyzonZ" wrote in message
...
Tom, here is the information from my first and last post combined.
Descriptive labels have now been specified.

Table named "Equipment":
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00
2 Reliance IQ2000 PDM-30 $1,516.00
3 Reliance IQ2000 PDM-30 $3,500.00
4 Emerson FX455 $1,600.00
5 Emerson FX455 $1,516.00

Query named "Query Relationship":
Option Manufacturer Model Number Cost
2 Reliance IQ2000 PDM-30 $1,516.00
4 Emerson FX455 $1,600.00

Query1 - Returns entries from Equipment table where (Equipment.Model
Number
EQUALS Query Relationship.Model Number) AND (Equipment.Option DOES NOT
EQUAL
Query Relationship.Option)
Ex:
Option Manufacturer Model Number Cost
3 Reliance IQ2000 PDM-30 $3,500.00
5 Emerson FX455 $1,516.00

The thing confusing about your description is you use of EQUALS. I believe
you are referring to the existence of rows. That is, eliminate options 3
and 5 from the set {1, 2, 3, 4, 5} leaving {1, 2, 4} but only where Model
Number exists in Query 1. Here goes:

SELECT Option, Manufacturer, [Model Number], Cost
FROM Equipment E
WHERE NOT EXISTS (
SELECT * FROM Query1 Q
WHERE Q.Option = E.Option)
AND EXISTS (
SELECT * FROM Query1 Q
WHERE Q.[Model Number] = E.[Model Number])
ORDER BY Option

I hope I've understood you and coded that properly. If you find you need to
change anything in what I wrote, and it doesn't work, please post back just
how you changed it, and what didn't work (error message, wrong results,
etc.)

Query2 - Returns entries from Equipment table where (Equipment.Model
Number
DOES NOT EQUAL Query Relationship.Model Number) AND (Equipment.Option DOES
NOT EQUAL Query Relationship.Option)
Ex:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00

Almost the same thing:

SELECT Option, Manufacturer, [Model Number], Cost
FROM Equipment E
WHERE NOT EXISTS (
SELECT * FROM Query1 Q
WHERE Q.Option = E.Option)
AND NOT EXISTS (
SELECT * FROM Query1 Q
WHERE Q.[Model Number] = E.[Model Number])
ORDER BY Option

Just add a NOT there in the middle.

Am I doing any better?

Sorry about the confusion. Hope this clears things up. Again Query
Relationship (Query) and Equipment (Table) are working correctly. I just
need help with generating Query1 and Query2. Thank you again.

Tyzon


"Tom Ellison" wrote:

Dear Tyzon:

Evalue was just a column name I dreamed up, meant to mean values to
Eliminate. I believe that's what you were saying you mean to do.

Now, for problem 1, what is this:

Equipment.Model Number=QueryRelationship.Model Number
AND NOT Equipment.Option=Query Relationship.Option

Equipment.Model Number would be the column on which you want to filter.
QueryRelationship would seem to be some query, but I saw no mention of
this
before. Now I'm completely lost. However, if you add QueryRelationship
to
the query, JOINed as needed or as a cross product or whatever, then the
above criteria should be fine.

For your query2, again this QueryRelationship is something you didn't
mention before, and I have no concept of what it does or where it fits
in.
Sorry, but without antecedents it just will not be possible to follow
your
logic.

Perhaps the best help I can give is to try to help you gain perspective
on
how little I know of the particulars of your situation. If you omit some
essential element of the problem initially, like QueryRelationship, and
then
drop it in without explanation, well it's no wonder I'm suddenly
unenlightened.

May I suggest you give a bit of sample data from all your tables, post
the
SQL to whatever queries you have, whether working or not, and tell me
what
the results should look like. Then explain why this is what the results
should look like, and what logic produces these results. That should put
me
completely in the picture and enable me to craft some kind of solution
and
explain how it works.

OK?

Tom Ellison


"TyzonZ" wrote in message
...
Tom, thank you for posting so quickly. Primary in the Another Table
only
means that field is used for the primary key. I do not need the field
in
any
criteria. I'm a little confused about what Evalue means in your post
for
the
solution. Looking at your post and considering I only need the option
selected and the drive model I believe I should use the Query
Relationship
query in SQL code.

A refined definition of the problem:
1st Query returns entries where Equipment.Model Number=Query
Relationship.Model Number AND NOT Equipment.Option=Query
Relationship.Option

2nd Query returns entries where NOT Equipment.Model Number=Query
Relationship.Model Number AND NOT Equipment.Option=Query
Relationship.Option
(This what I was trying to say when I mentioned "the criteria I alread
have")

I think that should help clear up my question. Thank you again for
your
continued help. I should be there with another suggestion of the code
and
clarification of what Evalue means.

"Tom Ellison" wrote:

Dear Tyzon:

I failed to mention that you must change "AnotherTable" to the actual
name
of that table, which I did not see you specifiy.

Tom Ellison


"Tom Ellison" wrote in message
...
Dear Tyzon:

For someone new to Access you have a fairly advanced set of
problems.

Question 1:

I believe you want to eliminate 1, 3, and 5 from the set 1, 2, 3, 4,
5,
based on 1, 3, and 5 being in "Another table". Now, "Another table"
has
two columns, Primary and Option. It would seem then that the set 1,
3,
and 5 generated from this includes all values in both columns
Primary
and
Option. If that is not the case, then please explain why these 3 are
the
ones eliminated. If it is the case, then:

SELECT DISTINCT EValue
FROM (
SELECT Primary Evalue
FROM AnotherTable
UNION ALL
SELECT Option Evalue
FROM AnotherTable)

The above will give you the set 1, 3, 5 which are to be eliminate.
Then:

SELECT Option, Manufacturer, Model, Number, Cost
From Equipment
WHERE Option NOT IN (
SELECT DISTINCT EValue
FROM (
SELECT Primary Evalue
FROM AnotherTable
UNION ALL
SELECT Option Evalue
FROM AnotherTable)
ORDER BY Option

This incorporates the first query to eliminate those values.

I will post this and go to work on your further questions as time
allows.

Tom Ellison


"TyzonZ" wrote in message
...
I'm new to programming in Access so I apologize if I don't give all
the
needed information. I'm working with creating a plan to
eliminate/generate
spares for obsolete electronics in a factory. I have 1 table with
the
equipment information
Equipment:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00
2 Reliance IQ2000 PDM-30 $1,516.00
3 Reliance IQ2000 PDM-30 $3,500.00
4 Emerson FX455 $1,600.00
5 Emerson FX455 $1,516.00
Model Number is the text field I want to use as a
Option is the primary key used to determine what will be replaced
Another table list the equipment that has been selected to be
replaced
Primary Option
1 4
2 2

I was able to generate the following query with relationships:
Option Manufacturer Model Number Cost
2 Reliance IQ2000 PDM-30 $1,516.00
4 Emerson FX455 $1,600.00


I want to generate two more queries:
First - Euipment with model number as an exact match to any model
number
in
the query I currently have AND not a Option already selected
Ex:
Option Manufacturer Model Number Cost
3 Reliance IQ2000 PDM-30 $3,500.00
5 Emerson FX455 $1,516.00

Second - Equipment that Model Number does not match Model Number of
the
criteria I already have.
Ex:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00

I would greatly appreciate any help avaible and am more than happy
to
give
more information if need. One thing to remember is my database
contains
about 500 entries and will be used by multiple people to I don't
want
to
type
model in the criteria.

Dear Tyzon:

The point of the prompt for the value of Drives.Option would be that there
is no such column in that table. The slightest misspelling can cause this.
What do you get after you fix that (assuming I'm right about the column
name).

Tom Ellison


"TyzonZ" wrote in message
...
My examples were a little simplified as far as the data contained goes.
I'll
give you the code I have and describe the error messages I'm receiving.
I'd
preface this with Drives the table we referred to as Equipment previously.
DRC Drives Selected is the query we were referring to as Query
Relationship
previously.

SELECT Drives.Option, Drives.[Product Line], Drives.Asset, Drives.Use,
Drives.[Year Installed], Drives.[Drive Mfg], Drives.[Drive Family],
Drives.[Model Number], Drives.[Drive Controller], Drives.[Voltage
(Magnitude)], Drives.[Voltage (Units)], Drives.[Software Title],
Drives.[Software operating system], Drives.[Drive Size/Rating
(Magnitude)],
Drives.[Drive Size/Rating (Units)], Drives.[Old MTR KC], Drives.[Current
Spares], Drives.[Drive Life Cycle], Drives.[Replacement Option],
Drives.[Estimated Lead Time for Replacement], Drives.[Replacement Drive],
Drives.[New Mtr/Cbl], Drives.[Asset Engineer's Scope], Drives.[Drive KC],
Drives.Drive, Drives.[New MTR KC], Drives.[New MTR], Drives.[Processor
KC],
Drives.Processor, Drives.[Eng/Hr], Drives.[Man/Hr], Drives.[Date Last
Saved]
FROM Drives
WHERE NOT EXISTS (
SELECT * FROM Drives
WHERE [DRC Drives Selected].Option = Drives.Option)
AND EXISTS (
SELECT * FROM Drives
WHERE [DRC Drives Selected].[Model Number] = Drives.[Model Number])
ORDER BY Option;

When I try to view file I get a Enter Parameter Value prompt for
Drives.Option. It is as if SQL wants to look at a specific record. An
interesting note is that switching Drives.Option = [DRC Drives
Selected].Option gives Enter Parameter Value prompt for [DRC Drives
Selected].Option. Maybe this will give you some insight if you know how
the
= is processed. Is there any way I can attach the file to give you a
better
example of what I'm trying to do? I do have an excel file that performs
this, but formula length limits do not allow my "DRC Drives Selected" to
contain as many entries as need. I am willing to attach either of these
files if there is a method to do that?

I appreciate your persistance.

Tyzon

"Tom Ellison" wrote:

Dear Tyzon:

Thanks. It appears I may have missed some of what you provided. I'll
insert some responses within your last post below.

Tom Ellison


"TyzonZ" wrote in message
...
Tom, here is the information from my first and last post combined.
Descriptive labels have now been specified.

Table named "Equipment":
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00
2 Reliance IQ2000 PDM-30 $1,516.00
3 Reliance IQ2000 PDM-30 $3,500.00
4 Emerson FX455 $1,600.00
5 Emerson FX455 $1,516.00

Query named "Query Relationship":
Option Manufacturer Model Number Cost
2 Reliance IQ2000 PDM-30 $1,516.00
4 Emerson FX455 $1,600.00

Query1 - Returns entries from Equipment table where (Equipment.Model
Number
EQUALS Query Relationship.Model Number) AND (Equipment.Option DOES NOT
EQUAL
Query Relationship.Option)
Ex:
Option Manufacturer Model Number Cost
3 Reliance IQ2000 PDM-30 $3,500.00
5 Emerson FX455 $1,516.00

The thing confusing about your description is you use of EQUALS. I
believe
you are referring to the existence of rows. That is, eliminate options 3
and 5 from the set {1, 2, 3, 4, 5} leaving {1, 2, 4} but only where Model
Number exists in Query 1. Here goes:

SELECT Option, Manufacturer, [Model Number], Cost
FROM Equipment E
WHERE NOT EXISTS (
SELECT * FROM Query1 Q
WHERE Q.Option = E.Option)
AND EXISTS (
SELECT * FROM Query1 Q
WHERE Q.[Model Number] = E.[Model Number])
ORDER BY Option

I hope I've understood you and coded that properly. If you find you need
to
change anything in what I wrote, and it doesn't work, please post back
just
how you changed it, and what didn't work (error message, wrong results,
etc.)

Query2 - Returns entries from Equipment table where (Equipment.Model
Number
DOES NOT EQUAL Query Relationship.Model Number) AND (Equipment.Option
DOES
NOT EQUAL Query Relationship.Option)
Ex:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00

Almost the same thing:

SELECT Option, Manufacturer, [Model Number], Cost
FROM Equipment E
WHERE NOT EXISTS (
SELECT * FROM Query1 Q
WHERE Q.Option = E.Option)
AND NOT EXISTS (
SELECT * FROM Query1 Q
WHERE Q.[Model Number] = E.[Model Number])
ORDER BY Option

Just add a NOT there in the middle.

Am I doing any better?

Sorry about the confusion. Hope this clears things up. Again Query
Relationship (Query) and Equipment (Table) are working correctly. I
just
need help with generating Query1 and Query2. Thank you again.

Tyzon


"Tom Ellison" wrote:

Dear Tyzon:

Evalue was just a column name I dreamed up, meant to mean values to
Eliminate. I believe that's what you were saying you mean to do.

Now, for problem 1, what is this:

Equipment.Model Number=QueryRelationship.Model Number
AND NOT Equipment.Option=Query Relationship.Option

Equipment.Model Number would be the column on which you want to
filter.
QueryRelationship would seem to be some query, but I saw no mention of
this
before. Now I'm completely lost. However, if you add
QueryRelationship
to
the query, JOINed as needed or as a cross product or whatever, then
the
above criteria should be fine.

For your query2, again this QueryRelationship is something you didn't
mention before, and I have no concept of what it does or where it fits
in.
Sorry, but without antecedents it just will not be possible to follow
your
logic.

Perhaps the best help I can give is to try to help you gain
perspective
on
how little I know of the particulars of your situation. If you omit
some
essential element of the problem initially, like QueryRelationship,
and
then
drop it in without explanation, well it's no wonder I'm suddenly
unenlightened.

May I suggest you give a bit of sample data from all your tables, post
the
SQL to whatever queries you have, whether working or not, and tell me
what
the results should look like. Then explain why this is what the
results
should look like, and what logic produces these results. That should
put
me
completely in the picture and enable me to craft some kind of solution
and
explain how it works.

OK?

Tom Ellison


"TyzonZ" wrote in message
...
Tom, thank you for posting so quickly. Primary in the Another
Table
only
means that field is used for the primary key. I do not need the
field
in
any
criteria. I'm a little confused about what Evalue means in your
post
for
the
solution. Looking at your post and considering I only need the
option
selected and the drive model I believe I should use the Query
Relationship
query in SQL code.

A refined definition of the problem:
1st Query returns entries where Equipment.Model Number=Query
Relationship.Model Number AND NOT Equipment.Option=Query
Relationship.Option

2nd Query returns entries where NOT Equipment.Model Number=Query
Relationship.Model Number AND NOT Equipment.Option=Query
Relationship.Option
(This what I was trying to say when I mentioned "the criteria I
alread
have")

I think that should help clear up my question. Thank you again for
your
continued help. I should be there with another suggestion of the
code
and
clarification of what Evalue means.

"Tom Ellison" wrote:

Dear Tyzon:

I failed to mention that you must change "AnotherTable" to the
actual
name
of that table, which I did not see you specifiy.

Tom Ellison


"Tom Ellison" wrote in message
...
Dear Tyzon:

For someone new to Access you have a fairly advanced set of
problems.

Question 1:

I believe you want to eliminate 1, 3, and 5 from the set 1, 2, 3,
4,
5,
based on 1, 3, and 5 being in "Another table". Now, "Another
table"
has
two columns, Primary and Option. It would seem then that the set
1,
3,
and 5 generated from this includes all values in both columns
Primary
and
Option. If that is not the case, then please explain why these 3
are
the
ones eliminated. If it is the case, then:

SELECT DISTINCT EValue
FROM (
SELECT Primary Evalue
FROM AnotherTable
UNION ALL
SELECT Option Evalue
FROM AnotherTable)

The above will give you the set 1, 3, 5 which are to be
eliminate.
Then:

SELECT Option, Manufacturer, Model, Number, Cost
From Equipment
WHERE Option NOT IN (
SELECT DISTINCT EValue
FROM (
SELECT Primary Evalue
FROM AnotherTable
UNION ALL
SELECT Option Evalue
FROM AnotherTable)
ORDER BY Option

This incorporates the first query to eliminate those values.

I will post this and go to work on your further questions as time
allows.

Tom Ellison


"TyzonZ" wrote in message
...
I'm new to programming in Access so I apologize if I don't give
all
the
needed information. I'm working with creating a plan to
eliminate/generate
spares for obsolete electronics in a factory. I have 1 table
with
the
equipment information
Equipment:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00
2 Reliance IQ2000 PDM-30 $1,516.00
3 Reliance IQ2000 PDM-30 $3,500.00
4 Emerson FX455 $1,600.00
5 Emerson FX455 $1,516.00
Model Number is the text field I want to use as a
Option is the primary key used to determine what will be
replaced
Another table list the equipment that has been selected to be
replaced
Primary Option
1 4
2 2

I was able to generate the following query with relationships:
Option Manufacturer Model Number Cost
2 Reliance IQ2000 PDM-30 $1,516.00
4 Emerson FX455 $1,600.00


I want to generate two more queries:
First - Euipment with model number as an exact match to any
model
number
in
the query I currently have AND not a Option already selected
Ex:
Option Manufacturer Model Number Cost
3 Reliance IQ2000 PDM-30 $3,500.00
5 Emerson FX455 $1,516.00

Second - Equipment that Model Number does not match Model Number
of
the
criteria I already have.
Ex:
Option Manufacturer Model Number Cost
1 Reliance 1336S-BRF30-L5 $400.00

I would greatly appreciate any help avaible and am more than
happy
to
give
more information if need. One thing to remember is my database
contains
about 500 entries and will be used by multiple people to I don't
want
to
type
model in the criteria.