 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Mailmerge and Fax / November 2003Data returned as Id instead of the value within
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. 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 > I am using Access as my data source. I have 2 fields that > are lookup columns in the database. When I select those [quoted text clipped - 18 lines] > > Thank you in advance. 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? 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 ----------------------------------------------------- -- Peter Jamieson MS Word MVP > 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.) [quoted text clipped - 6 lines] > ~~ Message posted from http://www.WordForums.com/ > ~~ View and post usenet messages directly from http://www.WordForums.com/ 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... > 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? -- Peter Jamieson MS Word MVP > But I dont have a lookup table. > [quoted text clipped - 16 lines] > ~~ Message posted from http://www.WordForums.com/ > ~~ View and post usenet messages directly from http://www.WordForums.com/ 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. 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. -- Peter Jamieson MS Word MVP > 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. > > ------------------------------------------------ > ~~ Message posted from http://www.WordForums.com/ > ~~ View and post usenet messages directly from http://www.WordForums.com/ > *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" " " } 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 [quoted text clipped - 9 lines] > ~~ Message posted from http://www.WordForums.com/ > ~~ View and post usenet messages directly from http://www.WordForums.com/ > * 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 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. > > * is the field blank if you get the data using e.g. Excel or MS Query > > instead > > of Word? > > * > > 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. -- Peter Jamieson MS Word MVP > > * Does { MERGEFIELD PrimaryContactPrx } come up as blank however you > > connect to the database from Word? (DDE, ODBC)* [quoted text clipped - 17 lines] > ~~ Message posted from http://www.WordForums.com/ > ~~ View and post usenet messages directly from http://www.WordForums.com/ |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.