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 said:
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
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
:
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
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.
:
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
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.
