SQL and Access...

  • Thread starter Thread starter Heath
  • Start date Start date
H

Heath

Hi all,

I have an existing access database with several tables and relationships
already setup. What I am trying to do it using SQL to select information
from a table based on certain information. The problem I get is the
fields that have relationships do not display the correct data but
rather the primary key for that data.

So for instant I want to select everything from Table1 and instead of
getting the first name from table1 I get a value of 15 which is the
primary key(id) of the actually first name.

I am not sure if there is a setting in access to pass the actual name,
or I do I need to query the names table for the name of the primary key
value.

Maybe this is an issue with my SQL or how I am implementing it...

any input would be helpfully!
 
Hi all,

I have an existing access database with several tables and relationships
already setup. What I am trying to do it using SQL to select information
from a table based on certain information. The problem I get is the
fields that have relationships do not display the correct data but
rather the primary key for that data.

So for instant I want to select everything from Table1 and instead of
getting the first name from table1 I get a value of 15 which is the
primary key(id) of the actually first name.

I am not sure if there is a setting in access to pass the actual name,
or I do I need to query the names table for the name of the primary key
value.

Maybe this is an issue with my SQL or how I am implementing it...

any input would be helpfully!
Click to expand...

Almost certainly, you're yet another victim of the misleading, confusing,
obnoxious "Lookup Wizard". See http://www.mvps.org/access/lookupfields.htm for
a critique.

If so your table *APPEARS* to contain a name. It doesn't; it contains a
number, the foreign key to the name table. This fact is concealed from your
view by the lookup combo box.

What you will need to do is to create a Query joining your main table to the
lookup table (or tables), and select the name field from the lookup table, and
the other (non lookup fields) from the main table.
 
To add some to John's answer, you can spot a lookup field easily by opening a
table in normal view, and click into a field. If you see a dropdown with an
arrow when you set focus to the field, then you have a lookup field. It may
be based on another table or query, or it may be a Value List. Depending on
the implementation, the bound column may be numeric or it may be text. In
addition, the Display Control property is generally set to Combo Box. If you
reset it to Text Box, save the table, and then do a Compact and repair, you
will have permanently exorcised the evil Lookup field.

Value List Examples

In this example, the actual value selected will be the value stored in the
table:
Row Source: "Red";"Yellow";"Green"
Bound Column: 1
Column Count: 1
Column Widths: 1"

However, in this example, a numeric value will be the value stored in the
table:

Row Source: 1;"Red";2;"Yellow";3;"Green"
Bound Column: 1
Column Count: 2
Column Widths: 0";1"

If the Row Source Type is a Value List, be careful about removing this if
the Bound Column is not the same as the first non-zero width column
(otherwise, you may end up with numbers that you don't know what they mean).
The best practice is to make a backup of your database first, before doing
any design changes or running any action queries (update, delete, append,
etc.) that change data.

In the second example, above, it would be dangerous to get change the
Display Control to a text box, without first creating a lookup table with the
same values. Otherwise, you could end up with a column of meaningless numbers
with no way to correlate them to the original text values.

Access MVP Alumni Jeff Conrad has a free utility available known as CSD
Tools, which you can download and install as an add-in. Once installed, this
tool is available to use against any database that you have open. One feature
of this tool is to quickly and easily scan your database for the evil Lookup
fields, instead of you having to manually discover them.

CSD Tools
http://www.accessmvp.com/JConrad/accessjunkie/csdtools.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
Back
Top