Sub select to get name from id in another table

  • Thread starter Thread starter jason
  • Start date Start date
J

jason

How do I do a subselect on audListings].[Status_ID] to obtain its consequent
NAME value in the table tblMarketStatus based on the AudListings.Status_ID
which is the primary key in tblMarketStatus as I wish to display the name as
well as the ID. Here is how the query looks at the moment - it is a
omparison query put together with the help of Douglas Steele (Thanks Man! -
works great!)


SELECT [tblListings].[ListingsID], "Market Status has changed" AS Flag,
[tblListings].[Name], [tblListings].[Status_ID],
[tblMarketStatus].[Market_Status], [audListings].[Status_ID],
[audListings].[AudCreationDate]
FROM tblMarketStatus INNER JOIN (tblListings INNER JOIN audListings ON
[tblListings].[ListingsID]=[audListings].[ListingsID]) ON
[tblMarketStatus].[MarketStatusID]=[tblListings].[Status_ID]
WHERE ((([tblListings].[Status_ID])<>[audListings].[Status_ID]));

Cheers
Jason
 
Dear Jason:

I take it you have a table in which there is a [NAME] value to be
located using [Status_ID]. Is it the case that this column is not in
tblMarketStatus, tblListings, or audListings? It would appear so.

If so, you will need to add a fourth table to the query and create the
JOIN to it using the Status_ID value so you can retrieve the NAME for
it.

If not, then please help me understand what I am missing here.

How do I do a subselect on audListings].[Status_ID] to obtain its consequent
NAME value in the table tblMarketStatus based on the AudListings.Status_ID
which is the primary key in tblMarketStatus as I wish to display the name as
well as the ID. Here is how the query looks at the moment - it is a
omparison query put together with the help of Douglas Steele (Thanks Man! -
works great!)


SELECT [tblListings].[ListingsID], "Market Status has changed" AS Flag,
[tblListings].[Name], [tblListings].[Status_ID],
[tblMarketStatus].[Market_Status], [audListings].[Status_ID],
[audListings].[AudCreationDate]
FROM tblMarketStatus INNER JOIN (tblListings INNER JOIN audListings ON
[tblListings].[ListingsID]=[audListings].[ListingsID]) ON
[tblMarketStatus].[MarketStatusID]=[tblListings].[Status_ID]
WHERE ((([tblListings].[Status_ID])<>[audListings].[Status_ID]));

Cheers
Jason
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi Tom, the problem is that the qry is tracking status-id in two tables:

tblListings
audListings

Thus, to obtain the name Market Status based on .status_id I join the table
[tblMarketStatus] to the query. If I bring the .NAME field into the query
from tblMarketStatus it will show the value "Under Offer" for the primary
table but will do the same thing for the Name value for the audListings
table which is incorrect. In other words, I cannot just bring in the name
field as it will only work for the primary field flag and not for the shadow
field flag as the will both look the same when they are not....

does this make sense:

look here to see what I mean:

http://69.2.200.70/catamaranco/images/screenshot/qrytrackStatus_id.jpg

You can see that tblListings.status_id and audListings.status_id are two
different values. but If I join the table [tblMarketStatus] to the query it
will show the correct name for tblListings.status_id but not for
audListings.status_id as it only picks up the name for the primary table and
not the audit/shadow table.

- Jason


Tom Ellison said:
Dear Jason:

I take it you have a table in which there is a [NAME] value to be
located using [Status_ID]. Is it the case that this column is not in
tblMarketStatus, tblListings, or audListings? It would appear so.

If so, you will need to add a fourth table to the query and create the
JOIN to it using the Status_ID value so you can retrieve the NAME for
it.

If not, then please help me understand what I am missing here.

How do I do a subselect on audListings].[Status_ID] to obtain its consequent
NAME value in the table tblMarketStatus based on the AudListings.Status_ID
which is the primary key in tblMarketStatus as I wish to display the name as
well as the ID. Here is how the query looks at the moment - it is a
omparison query put together with the help of Douglas Steele (Thanks Man! -
works great!)


SELECT [tblListings].[ListingsID], "Market Status has changed" AS Flag,
[tblListings].[Name], [tblListings].[Status_ID],
[tblMarketStatus].[Market_Status], [audListings].[Status_ID],
[audListings].[AudCreationDate]
FROM tblMarketStatus INNER JOIN (tblListings INNER JOIN audListings ON
[tblListings].[ListingsID]=[audListings].[ListingsID]) ON
[tblMarketStatus].[MarketStatusID]=[tblListings].[Status_ID]
WHERE ((([tblListings].[Status_ID])<>[audListings].[Status_ID]));

Cheers
Jason
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
 
Dear Jason:

Then you just need to alias one or both of them so they are distinct.

In the design grid, in the Field row, you can add the desired name to
the left of the actual column name, followed by a colon. That will
rename the column in the resulting query.

Hi Tom, the problem is that the qry is tracking status-id in two tables:

tblListings
audListings

Thus, to obtain the name Market Status based on .status_id I join the table
[tblMarketStatus] to the query. If I bring the .NAME field into the query
from tblMarketStatus it will show the value "Under Offer" for the primary
table but will do the same thing for the Name value for the audListings
table which is incorrect. In other words, I cannot just bring in the name
field as it will only work for the primary field flag and not for the shadow
field flag as the will both look the same when they are not....

does this make sense:

look here to see what I mean:

http://69.2.200.70/catamaranco/images/screenshot/qrytrackStatus_id.jpg

You can see that tblListings.status_id and audListings.status_id are two
different values. but If I join the table [tblMarketStatus] to the query it
will show the correct name for tblListings.status_id but not for
audListings.status_id as it only picks up the name for the primary table and
not the audit/shadow table.

- Jason


Tom Ellison said:
Dear Jason:

I take it you have a table in which there is a [NAME] value to be
located using [Status_ID]. Is it the case that this column is not in
tblMarketStatus, tblListings, or audListings? It would appear so.

If so, you will need to add a fourth table to the query and create the
JOIN to it using the Status_ID value so you can retrieve the NAME for
it.

If not, then please help me understand what I am missing here.

How do I do a subselect on audListings].[Status_ID] to obtain its consequent
NAME value in the table tblMarketStatus based on the AudListings.Status_ID
which is the primary key in tblMarketStatus as I wish to display the name as
well as the ID. Here is how the query looks at the moment - it is a
omparison query put together with the help of Douglas Steele (Thanks Man! -
works great!)


SELECT [tblListings].[ListingsID], "Market Status has changed" AS Flag,
[tblListings].[Name], [tblListings].[Status_ID],
[tblMarketStatus].[Market_Status], [audListings].[Status_ID],
[audListings].[AudCreationDate]
FROM tblMarketStatus INNER JOIN (tblListings INNER JOIN audListings ON
[tblListings].[ListingsID]=[audListings].[ListingsID]) ON
[tblMarketStatus].[MarketStatusID]=[tblListings].[Status_ID]
WHERE ((([tblListings].[Status_ID])<>[audListings].[Status_ID]));

Cheers
Jason
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Great - thanks Tom - you could not possibly help with my other thread:
Comparison query not returning more than one fld which is related to this
exercise...

Many thanks
Jason


Tom Ellison said:
Dear Jason:

Then you just need to alias one or both of them so they are distinct.

In the design grid, in the Field row, you can add the desired name to
the left of the actual column name, followed by a colon. That will
rename the column in the resulting query.

Hi Tom, the problem is that the qry is tracking status-id in two tables:

tblListings
audListings

Thus, to obtain the name Market Status based on .status_id I join the table
[tblMarketStatus] to the query. If I bring the .NAME field into the query
from tblMarketStatus it will show the value "Under Offer" for the primary
table but will do the same thing for the Name value for the audListings
table which is incorrect. In other words, I cannot just bring in the name
field as it will only work for the primary field flag and not for the shadow
field flag as the will both look the same when they are not....

does this make sense:

look here to see what I mean:

http://69.2.200.70/catamaranco/images/screenshot/qrytrackStatus_id.jpg

You can see that tblListings.status_id and audListings.status_id are two
different values. but If I join the table [tblMarketStatus] to the query it
will show the correct name for tblListings.status_id but not for
audListings.status_id as it only picks up the name for the primary table and
not the audit/shadow table.

- Jason


Tom Ellison said:
Dear Jason:

I take it you have a table in which there is a [NAME] value to be
located using [Status_ID]. Is it the case that this column is not in
tblMarketStatus, tblListings, or audListings? It would appear so.

If so, you will need to add a fourth table to the query and create the
JOIN to it using the Status_ID value so you can retrieve the NAME for
it.

If not, then please help me understand what I am missing here.

How do I do a subselect on audListings].[Status_ID] to obtain its consequent
NAME value in the table tblMarketStatus based on the AudListings.Status_ID
which is the primary key in tblMarketStatus as I wish to display the
Click to expand...
name
as
well as the ID. Here is how the query looks at the moment - it is a
omparison query put together with the help of Douglas Steele (Thanks Man! -
works great!)


SELECT [tblListings].[ListingsID], "Market Status has changed" AS Flag,
[tblListings].[Name], [tblListings].[Status_ID],
[tblMarketStatus].[Market_Status], [audListings].[Status_ID],
[audListings].[AudCreationDate]
FROM tblMarketStatus INNER JOIN (tblListings INNER JOIN audListings ON
[tblListings].[ListingsID]=[audListings].[ListingsID]) ON
[tblMarketStatus].[MarketStatusID]=[tblListings].[Status_ID]
WHERE ((([tblListings].[Status_ID])<>[audListings].[Status_ID]));

Cheers
Jason



Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
 
Back
Top