Data returned as Id instead of the value within

[darryl]

I am using Access as my data source. I have 2 fields that
are lookup columns in the database. When I select those
fields I get the value in the Id field instead of the
value in the field selected ie:

I run a query on the table of SELECT City FROM "table";
The values for City are say NY and LA. What is returned is
the primary key field which is labeled "Id" with
respective values of 1 and 2 instead of the "City" value.
In Access the query returns the correct value, but when
exported in a Merge returns the Id field. Doesnt make a
difference whether I Merge with the query or the table the
query came from. Same happens if I export to an .xls file.
The Id field is returned instead of the proper value to
the file.

All other values queried are returned correctly. It only
happens where the fields being requested are coming from a
Lookup field in the table that obviously link to another
table for the Lookup. Solutions?

Thank you in advance.

 

[Peter Jamieson]

I am pretty sure that what is happening is as follows:
a. when you create a "lookup column", you are adding a column into your
table which stores the ID needed to look up the value.
b. Access understands this construct and displays an appropriate lookup
instead of the ID. So, for example, if you define a QUERY saying SELECT *
FROM mytable and mytable contains a lookup field, you will see a dropdown in
the query results.
c. however, when external programs get the data from the query or table,
they do not "understand" that the item is a lookup item, and just display
the ID, which is all they see.

When the connection method used is DDE, and Word is getting the data via
Access, not directly from the underlying Jet database, it's possible that
Access would be smart enough to return the data you want. But it doesn't.

So what you have to do is define a query that does the necessary join
explicitly rather than relying on this special feature of Access. (Purely
from a personal point of view I would try to avoid this particular Access
feature precisely because you end up having to do things the ""hard way"
anyway, but then I'm not an Access expert). You can either do that in the
query design pane by dragging both tables, defining the relationship, and
dragging the necessary fields into the grid, or just by specifying the SQL,
which might be something roughly like

SELECT base.f1,base.f2,base.f3, lookup.city FROM base INNER JOIN lookup ON
base.city = lookup.id

assuming ID is the primary key of the lookup table and you have given your
"lookup field" the name "city" in your "base" table

Peter Jamieson
MS Word MVP

 

[Jigsatics]

I have the same situation. The only difference is that my merged field
does not show up. The field is a prefix (e.g. Mr., Miss, Mrs., etc.)
and it was set up as a list box in Access.

When I mail merge it to a Word document, the prefix does not show up.
It's just blank. What seems to be the problem?

 

[Peter Jamieson]

I don't know if you saw my response to the original question, but here it is
again. I think you are seeing the same problem:


-----------------------------------------------------
I am pretty sure that what is happening is as follows:
a. when you create a "lookup column", you are adding a column into your
table which stores the ID needed to look up the value.
b. Access understands this construct and displays an appropriate lookup
instead of the ID. So, for example, if you define a QUERY saying SELECT *
FROM mytable and mytable contains a lookup field, you will see a dropdown in
the query results.
c. however, when external programs get the data from the query or table,
they do not "understand" that the item is a lookup item, and just display
the ID, which is all they see.

When the connection method used is DDE, and Word is getting the data via
Access, not directly from the underlying Jet database, it's possible that
Access would be smart enough to return the data you want. But it doesn't.

So what you have to do is define a query that does the necessary join
explicitly rather than relying on this special feature of Access. (Purely
from a personal point of view I would try to avoid this particular Access
feature precisely because you end up having to do things the ""hard way"
anyway, but then I'm not an Access expert). You can either do that in the
query design pane by dragging both tables, defining the relationship, and
dragging the necessary fields into the grid, or just by specifying the SQL,
which might be something roughly like

SELECT base.f1,base.f2,base.f3, lookup.city FROM base INNER JOIN lookup ON
base.city = lookup.id

assuming ID is the primary key of the lookup table and you have given your
"lookup field" the name "city" in your "base" table

 

[Jigsatics]

But I dont have a lookup table.

And since I don't have a lookup table, the statement below won't work
for me.

SELECT base.f1,base.f2,base.f3, lookup.city FROM base INNER JOIN
lookup ON

Other fields like City has a lookup table and since I have'nt gone that
far in my merge document I can use your solution to that particular
problem. But in this instance, nothing shows up when I merge the field.
You mentioned that it will show an ID instead of the actual text but I
can't even see an ID.

There must be something else. I could probably figure it out if it at
least shows a value. Help...

 

[Peter Jamieson]

But in this instance, nothing shows up when I merge the field.

You mentioned that it will show an ID instead of the actual text but I
can't even see an ID.

Can you confirm that it is Access 2003?

How is the field defined in the Table Design View? e.g. where are the
possible values (Mr. Mrs. etc.) set up?

 

[Jigsatics]

I'm using Access XP using Word97 to mail merge. The list was set up in
Design Mode, under the Lookup tab, in Row Source.

 

[Peter Jamieson]

Is the Row Source Type a Value List and does the Row Source just have a list
such as "Mr";"Mrs" etc. (or whatever)?

If so, I haven't been able to replicate the problem (with Word 97 and Access
2002 on Win98) here. As far as I can tell the column is identical to a
normal text column.

If not, let me know exactly how the item is set up and I'll try again.

 

[Jigsatics]

*Is the Row Source Type a Value List and does the Row Source just have

a list
such as "Mr";"Mrs" etc. (or whatever)?*

Yeah.. It was set up exactly as you described. The way my mail merge
document handles this is to put an X on [ ] Mr. [ ] Mrs. etc. This is
the statement I used in my Word document:

{ IF { MERGEFIELD PrimaryContactPrx } = "Miss" "X" " " }

 

[Peter Jamieson]

Not sure what to suggest at this point, but things I suppose I would look at
to see include...

Does { MERGEFIELD PrimaryContactPrx } come up as blank however you connect
to the database from Word? (DDE, ODBC)

is the field blank if you try to include data usng the DATABASE toolbar?

is the field blank if you get the data using e.g. Excel or MS Query instead
of Word?

--
Peter Jamieson
MS Word MVP

*Is the Row Source Type a Value List and does the Row Source just have
a list
such as "Mr";"Mrs" etc. (or whatever)?*

Yeah.. It was set up exactly as you described. The way my mail merge
document handles this is to put an X on [ ] Mr. [ ] Mrs. etc. This is
the statement I used in my Word document:

{ IF { MERGEFIELD PrimaryContactPrx } = "Miss" "X" " " }

 

[Jigsatics]

* Does { MERGEFIELD PrimaryContactPrx } come up as blank however yo

connect to the database from Word? (DDE, ODBC)*

Yes. It's blank.

*
is the field blank if you try to include data usng the DATABAS
toolbar?*

Do you mean "INSERT MERGE FIELD" in the Word toolbar? Yes, it's blank.

* is the field blank if you get the data using e.g. Excel or MS Quer
instead
of Word?
*

No. When I exported the Access data to Excel I got the prefixes

 

[Peter Jamieson]

I don't hold out much hope of seeing anything useful here, but...

Do you mean "INSERT MERGE FIELD" in the Word toolbar? Yes, it's blank.

No, I mean using the Insert Database icon on the Database toolbar (findable
via Tools|Customize). Also, you can either just insert the results, or a {
DATABASE } field that you can re-execute later.

No. When I exported the Access data to Excel I got the prefixes.

I was thinking more along the lines of using the Excel Data menu (which
probably uses MS Query) to get the data from Access without using an
intermediate file.