Query returns linked table IDs, not Data!

  • Thread starter Thread starter Nunnsby
  • Start date Start date
N

Nunnsby

Hi There

I have a really simple request:

I need to return the linked table DATA from a query, and NOT the link
ID.

I have a form that shows fields based on the results of a query. The
query is from a table (tblmailinglist), and is a straight forward query
of just normal fields, with one sorted.

The problem is that the table field (titleLookUp) itself is linked to
another table (tbltitles), which contains various titles: Mr, Mrs, Mr &
Mrs, etc. The query from tblMailingList returns the correct data if I
double-click on the query, but when I use the fieldname in the form, it
only displays the table link ID, like 14, or 15, or 19, etc, but not
the actual data contained in the query. Like Mr, Mrs, etc.

Any help here would be appreciated.

Thanks

Richard
 
A field defined as a lookup field in a table design is not "just normal
fields". You should consider never using lookup fields in tables as
suggested at http://www.mvps.org/access/lookupfields.htm. If you want the
appropriate value to display in your query or form, add the table containing
the title to the query or record source.
 
Hi There

I have a really simple request:

I need to return the linked table DATA from a query, and NOT the link
ID.
Click to expand...

What's in the table IS the link ID, so that's what your query is
returning.
I have a form that shows fields based on the results of a query. The
query is from a table (tblmailinglist), and is a straight forward query
of just normal fields, with one sorted.

The problem is that the table field (titleLookUp) itself is linked to
another table (tbltitles), which contains various titles: Mr, Mrs, Mr &
Mrs, etc. The query from tblMailingList returns the correct data if I
double-click on the query, but when I use the fieldname in the form, it
only displays the table link ID, like 14, or 15, or 19, etc, but not
the actual data contained in the query. Like Mr, Mrs, etc.
Click to expand...

Create a new Query. Add tblMailingList; add tblTitles. Join the two
tables on TitleID (or whatever the name of the field is). Select the
name from tblMailinglist, and the title from tblTitles. That's how
relational databases work.

Microsoft's misleading, infuriating Lookup Wizard has tricked you.
What you SEE in the table datasheet is the title - but *IT'S NOT
THERE*. The table does not contain titles; it contains links. That's
fine, that's normal - you just need to use a Query to go beyond the
extremely limited and confusing limitations of the Lookup field type.

John W. Vinson[MVP]
 
John said:
What's in the table IS the link ID, so that's what your query is
returning.


Create a new Query. Add tblMailingList; add tblTitles. Join the two
tables on TitleID (or whatever the name of the field is). Select the
name from tblMailinglist, and the title from tblTitles. That's how
relational databases work.

Microsoft's misleading, infuriating Lookup Wizard has tricked you.
What you SEE in the table datasheet is the title - but *IT'S NOT
THERE*. The table does not contain titles; it contains links. That's
fine, that's normal - you just need to use a Query to go beyond the
extremely limited and confusing limitations of the Lookup field type.

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

Hi All

Thanks for the reply, much appreciated. I have corrected the mistake,
but now the results are different. Anything that was originilly a null
value, whereby there was no title, has disappeared from the results.

Any ideas?

Thanks
 
Thanks for the reply, much appreciated. I have corrected the mistake,
but now the results are different. Anything that was originilly a null
value, whereby there was no title, has disappeared from the results.

Any ideas?
Click to expand...

That would be a result of the way you joined the tables in the query.
You've made an inner join, which includes only the records that match on
both sides of the join. You want an outer join (left join or right
join, depending) that includes all the records from one of the tables,
and only the matching records from the other. If you have the query
open in design view, and you right-click on the join line between the
two tables, you can choose the appropriate option for the join.
 
Dirk said:
That would be a result of the way you joined the tables in the query.
You've made an inner join, which includes only the records that match on
both sides of the join. You want an outer join (left join or right
join, depending) that includes all the records from one of the tables,
and only the matching records from the other. If you have the query
open in design view, and you right-click on the join line between the
two tables, you can choose the appropriate option for the join.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...

Dirk, You are a LEGEND! :)

Thanks alot, you don't know how much time you have just saved me!
It's 23h37 here, South African Time, and I can now go home! :)

Thanks to all who have helped. I love the Techy Community. Hopefully
one day I'll be able to help out too.

Much appreciated.

Good Night All!

Richard
 
Dirk, You are a LEGEND! :)
Click to expand...

LOL Seems to me Duane and John are much more legendary.
Thanks alot, you don't know how much time you have just saved me!
It's 23h37 here, South African Time, and I can now go home! :)

Thanks to all who have helped. I love the Techy Community. Hopefully
one day I'll be able to help out too.
Click to expand...

You're welcome. Now get some sleep!
 
Back
Top