Query results in Numbers, not my entries

[Guest]

Hello all. in my table I have a lookup, which looks up a Boat Number. When I
created a query on that table, the results only show me the autonumber for
that boat, not the boat number. As a matter of fact when I go into datasheet
view of the table it shows me the same.

Any help greatly appreciated.
Steph

 

[Guest]

Have you tried adding the boat number field to your query, and unchecking the
'show' button for the autonumber field ?

 

[Guest]

Thanks for the reply. I did try that but all it gave me was a list of all the
boats and the stations they are associated with.

What I have is a table for each type of boat (4 total) and I want to query
each table to show me which boats have had entries made on them. In the
results of that query, I get all in the info I need, however the
lkupBoatNumber field is just the autonumber, not the boat number.

thanks again!

 

[John W. Vinson]

Hello all. in my table I have a lookup, which looks up a Boat Number. When I
created a query on that table, the results only show me the autonumber for
that boat, not the boat number. As a matter of fact when I go into datasheet
view of the table it shows me the same.

That's because the number is *what is actually in your table*.

The Lookup Field is, in the opinion of a lot of developers, a dreadfully bad
design decision by Microsoft. See
http://www.mvps.org/access/lookupfields.htm
for a critique.

What's happening is that your table does NOT contain the boat number. It
appears to, because the Lookup Wizard is concealing the actual contents of the
table (a numeric foriegn key) from view. When you create a query based on the
table, you get the actual contents of the field (the autonumber) rather than
what you *see* in the datasheet.

The solution is to include both your main table *and* the lookup table in your
query; pull the Boat Number from the lookup table, and the other fields from
your main table.

John W. Vinson [MVP]

 

[Guest]

Thanks John. I have another database where I use the same method, a lookup
in a field in my table and my queries work fine. Thats why I was a little
puzzled. I only have about 20 boats in my other table (that the lookup
references) is there a way I an tell my qery that if say, the number 2 is
returned, that it display the boat number?

Thanks again.

 

[John W. Vinson]

Thanks John. I have another database where I use the same method, a lookup
in a field in my table and my queries work fine. Thats why I was a little
puzzled. I only have about 20 boats in my other table (that the lookup
references) is there a way I an tell my qery that if say, the number 2 is
returned, that it display the boat number?

Just JOIN THE BOATS TABLE.

That's what a lookup field does, behind the scenes, unreliably and
unpredictably (in my limited experience, I got disgusted with them real
quick).

A Lookup field is *NEVER* necessary, under *ANY* circumstances. Lookup fields
have one good feature: they make it a couple of mouseclicks easier to put a
combo box on a form. This minor advantage is vastly outweighed by their
disadvantages.

If you create a query joining your other table to your boats table by the
BoatID, then you are in control; you can see which fields you are selecting,
what they contain, and so on... just what you *can't* see when the actual
contents of your table are concealed from view.

John W. Vinson [MVP]