query to get all rows where id is in two different fields in diffe

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi - I need to write a query which will get all rows where an id is in either
of two different columns in any row in the table. I hope this makes sense.
Here is what I wrote. I get an error when I try this...

SELECT [PMK2005UNIQUE Query].id, [PMK2005UNIQUE Query].[principal id]
FROM [PMK2005UNIQUE Query] where [PMK2005UNIQUE Query].id in ((select
[PMK2005UNIQUE Query].id from [PMK2005UNIQUE Query]) AND (select
[PMK2005UNIQUE Query].[principal id] from [PMK2005UNIQUE Query]))
 
SELECT [PMK2005UNIQUE Query].id,
[PMK2005UNIQUE Query].[principal id]
FROM [PMK2005UNIQUE Query]
WHERE [PMK2005UNIQUE Query].id in
(SELECT [Principal ID]
FROM [PMK2005 Unique Query)
OR
[PMK2005UNIQUE Query].id in
(SELECT [ID]
FROM [PMK2005 Unique Query])
 
Hi - I need to write a query which will get all rows where an id is in either
of two different columns in any row in the table. I hope this makes sense.
Here is what I wrote. I get an error when I try this...

SELECT [PMK2005UNIQUE Query].id, [PMK2005UNIQUE Query].[principal id]
FROM [PMK2005UNIQUE Query] where [PMK2005UNIQUE Query].id in ((select
[PMK2005UNIQUE Query].id from [PMK2005UNIQUE Query]) AND (select
[PMK2005UNIQUE Query].[principal id] from [PMK2005UNIQUE Query]))
Click to expand...

This makes no sense. You're apparently trying to select those records
where the ID is in the same query. You're using [PMK2005UNIQUE Query]
in both the main query and both subqueries. Nowhere does this query
prompt for an ID.

I'm GUESSING that what you want is

SELECT <whatever>
FROM [PMK2005UNIQUE Query]
WHERE [ID] = [Enter ID:]
OR [Principal ID] = [Enter ID:]

This will prompt you for an ID and return whichever fields you choose
if that ID occurs in either field (matching what you say in the text,
though not the same as your subject line).

John W. Vinson[MVP]
 
Thanks John. That works great if I have an id I want to check. However, I
want to select only those ids where id = id and principal id = id without
entering an id. I did this with vba code and inserted the records into a new
table. I never got a simple query to do it. Any ideas there?
--
MorningStarFan


John Vinson said:
Hi - I need to write a query which will get all rows where an id is in either
of two different columns in any row in the table. I hope this makes sense.
Here is what I wrote. I get an error when I try this...

SELECT [PMK2005UNIQUE Query].id, [PMK2005UNIQUE Query].[principal id]
FROM [PMK2005UNIQUE Query] where [PMK2005UNIQUE Query].id in ((select
[PMK2005UNIQUE Query].id from [PMK2005UNIQUE Query]) AND (select
[PMK2005UNIQUE Query].[principal id] from [PMK2005UNIQUE Query]))
Click to expand...

This makes no sense. You're apparently trying to select those records
where the ID is in the same query. You're using [PMK2005UNIQUE Query]
in both the main query and both subqueries. Nowhere does this query
prompt for an ID.

I'm GUESSING that what you want is

SELECT <whatever>
FROM [PMK2005UNIQUE Query]
WHERE [ID] = [Enter ID:]
OR [Principal ID] = [Enter ID:]

This will prompt you for an ID and return whichever fields you choose
if that ID occurs in either field (matching what you say in the text,
though not the same as your subject line).

John W. Vinson[MVP]
Click to expand...
 
Restating your question.
You want to select records where the ID and the Principal ID are the same
within one record. Is that correct?

I read your original post, as saying you wanted to select records where the
ID existed in any record (which would be all records except those where ID
is null) or the ID was equal to any Principal ID in any record.

Another interpretation I see is
The ID in a record is equal to the Principal ID in ANY record in the table.

The last should be
SELECT [PMK2005UNIQUE Query].id,
[PMK2005UNIQUE Query].[principal id]
FROM [PMK2005UNIQUE Query]
WHERE [PMK2005UNIQUE Query].id in
(SELECT [Principal ID]
FROM [PMK2005 Unique Query])

There are some other possible interpretations of your request.
--ID in at least one other record and ID = Principal ID in at least one
record in the table (current or other)
--ID in at least one other record and ID = Principal Id in the current
record
--ID in at least one other record and ID = Principal ID in at least one
other record in the table

MorningStarFan said:
Thanks John. That works great if I have an id I want to check. However, I
want to select only those ids where id = id and principal id = id without
entering an id. I did this with vba code and inserted the records into a
new
table. I never got a simple query to do it. Any ideas there?
--
MorningStarFan


John Vinson said:
Hi - I need to write a query which will get all rows where an id is in
either
of two different columns in any row in the table. I hope this makes
sense.
Here is what I wrote. I get an error when I try this...

SELECT [PMK2005UNIQUE Query].id, [PMK2005UNIQUE Query].[principal id]
FROM [PMK2005UNIQUE Query] where [PMK2005UNIQUE Query].id in ((select
[PMK2005UNIQUE Query].id from [PMK2005UNIQUE Query]) AND (select
[PMK2005UNIQUE Query].[principal id] from [PMK2005UNIQUE Query]))
Click to expand...

This makes no sense. You're apparently trying to select those records
where the ID is in the same query. You're using [PMK2005UNIQUE Query]
in both the main query and both subqueries. Nowhere does this query
prompt for an ID.

I'm GUESSING that what you want is

SELECT <whatever>
FROM [PMK2005UNIQUE Query]
WHERE [ID] = [Enter ID:]
OR [Principal ID] = [Enter ID:]

This will prompt you for an ID and return whichever fields you choose
if that ID occurs in either field (matching what you say in the text,
though not the same as your subject line).

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Correction here - the *and* should be an *or* in the following sentence--

those ids where id = id and principal id = id

--
MorningStarFan


MorningStarFan said:
Thanks John. That works great if I have an id I want to check. However, I
want to select only those ids where id = id and principal id = id without
entering an id. I did this with vba code and inserted the records into a new
table. I never got a simple query to do it. Any ideas there?
--
MorningStarFan


John Vinson said:
Hi - I need to write a query which will get all rows where an id is in either
of two different columns in any row in the table. I hope this makes sense.
Here is what I wrote. I get an error when I try this...

SELECT [PMK2005UNIQUE Query].id, [PMK2005UNIQUE Query].[principal id]
FROM [PMK2005UNIQUE Query] where [PMK2005UNIQUE Query].id in ((select
[PMK2005UNIQUE Query].id from [PMK2005UNIQUE Query]) AND (select
[PMK2005UNIQUE Query].[principal id] from [PMK2005UNIQUE Query]))
Click to expand...

This makes no sense. You're apparently trying to select those records
where the ID is in the same query. You're using [PMK2005UNIQUE Query]
in both the main query and both subqueries. Nowhere does this query
prompt for an ID.

I'm GUESSING that what you want is

SELECT <whatever>
FROM [PMK2005UNIQUE Query]
WHERE [ID] = [Enter ID:]
OR [Principal ID] = [Enter ID:]

This will prompt you for an ID and return whichever fields you choose
if that ID occurs in either field (matching what you say in the text,
though not the same as your subject line).

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
No to question one.

It turns this request had several parts. For some records I needed to select
both records if a particular id appeared in the id field of one record and
then also in the principal id field of another record so that I could get the
highest value in another field for both records. There were other records
that I needed to select as well so I did this in vba code instead of an
access query.

The code for your other interpretation is the logic I needed for one part of
this request though so thanks! It was still helpful.

--
MorningStarFan


John Spencer said:
Restating your question.
You want to select records where the ID and the Principal ID are the same
within one record. Is that correct?

I read your original post, as saying you wanted to select records where the
ID existed in any record (which would be all records except those where ID
is null) or the ID was equal to any Principal ID in any record.

Another interpretation I see is
The ID in a record is equal to the Principal ID in ANY record in the table.

The last should be
SELECT [PMK2005UNIQUE Query].id,
[PMK2005UNIQUE Query].[principal id]
FROM [PMK2005UNIQUE Query]
WHERE [PMK2005UNIQUE Query].id in
(SELECT [Principal ID]
FROM [PMK2005 Unique Query])

There are some other possible interpretations of your request.
--ID in at least one other record and ID = Principal ID in at least one
record in the table (current or other)
--ID in at least one other record and ID = Principal Id in the current
record
--ID in at least one other record and ID = Principal ID in at least one
other record in the table

MorningStarFan said:
Thanks John. That works great if I have an id I want to check. However, I
want to select only those ids where id = id and principal id = id without
entering an id. I did this with vba code and inserted the records into a
new
table. I never got a simple query to do it. Any ideas there?
--
MorningStarFan


John Vinson said:
On Wed, 1 Feb 2006 14:58:14 -0800, "MorningStarFan"

Hi - I need to write a query which will get all rows where an id is in
either
of two different columns in any row in the table. I hope this makes
sense.
Here is what I wrote. I get an error when I try this...

SELECT [PMK2005UNIQUE Query].id, [PMK2005UNIQUE Query].[principal id]
FROM [PMK2005UNIQUE Query] where [PMK2005UNIQUE Query].id in ((select
[PMK2005UNIQUE Query].id from [PMK2005UNIQUE Query]) AND (select
[PMK2005UNIQUE Query].[principal id] from [PMK2005UNIQUE Query]))

This makes no sense. You're apparently trying to select those records
where the ID is in the same query. You're using [PMK2005UNIQUE Query]
in both the main query and both subqueries. Nowhere does this query
prompt for an ID.

I'm GUESSING that what you want is

SELECT <whatever>
FROM [PMK2005UNIQUE Query]
WHERE [ID] = [Enter ID:]
OR [Principal ID] = [Enter ID:]

This will prompt you for an ID and return whichever fields you choose
if that ID occurs in either field (matching what you say in the text,
though not the same as your subject line).

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top