Export query to HTML returns numbers rather than names for lookup field

[maroberge]

My query calls up two tables ("T_Names", "T_Scores"). In "T_Scores", field "Editor" is filled using lookup values from "T_Names"; there is thus a "1 to ∞" going from "T_Names.No" to "T_Scores.Editor" when I create the query.



These fields are added in the query page’s lower part, where I list "Editor" and "TitleOriginal", both from "T_Scores". In SQL view, the query reads:

SELECT T_SCORES.Editor, T_SCORES.TitleOriginal
FROM T_NAMES INNER JOIN T_SCORES ON T_NAMES.N° = T_SCORES.Editor
ORDER BY T_SCORES.Editor;

The query executes correctly, but when I export it to HTML and display it my browser, column "Editor" shows record numbers rather than the names of the editors. I stumble on this problem in many exported queries and have great difficulty pinpointing the source of the problem. Thanks in advance for any help.
query,

[orange]

Are you using lookup fields in your table?

[maroberge]

Yes, as I mentioned in my post, the names of editors come from a table, and I choose the names from a dropdown list.

[Micron]

Easy answer is don't use lookup fields in tables.
http://access.mvps.org/access/lookupfields.htm

I guess Access isn't passing along the underlying connection it makes between your lookup field and the hidden system table where the actual values are kept. The reason I'm guessing is because I, like so many others, do not use lookup fields in tables. You could try using Editor.Value in the query and see if that helps, but I've only ever seen that as a solution when a query refers to a joined field and that field is a lookup field.

[maroberge]

I had read the page at the link you quote, as well as other pages that do not see anything wrong with lookup tables. In my case, they are quite essential: I have a list of some 2,000 names from which I need to choose in several tables. With regard to your suggestion of using "Editor.Value", I must say that this is what I would do if I were dealing with multivalued fields, which is not the case here. It is simply a lookup table, and no *.Value entry is available.

[Welshgasman]

The lookup fields merely hide what you would do manually. In doing so, they have confused you as they confuse many others who use them? I have lost count of how many times this question gets asked. Just wish I had a pound for every time it has been asked.

So if you do not want to get rid of the lookup fields, then bring in the tables/queries that hold the lookup data and use those fields instead.

great difficulty pinpointing the source of the problem

lookup fields, pure and simple.

[Micron]

I'm afraid your comment about lookup fields in tables being essential is lost on me. The proper method is to have a lookup table, not a lookup field. Sure, you will find many pages that write positively about them; probably because they're catering to an audience that hasn't the interest or ability to do things properly.

Perhaps your post title threw me off, because an index number is what I'd expect to get instead of a value where mv fields are involved. So you are correct in relating .Value to being a mv field issue. I guess I wasn't of much help.

[isladogs]

Like everyone else here, I would strongly advise against using lookup fields in tables.
I suggest you read my article on this topic to understand the reasons for your current problem: http://www.mendipdatasystems.co.uk/t...lds/4594445135

You also mentioned that you use multivalued fields. These are also a bad idea for similar reasons: See http://www.mendipdatasystems.co.uk/m...lds/4594468763

There are better alternatives to both of these features

[maroberge]

Thank you for the many replies. I guess that I should have called what I am using a "Lookup Table", created by using the Lookup Wizard. I have read (and will reread) Colin's pages warning against the two "easy" methods offered by Access and I also tried exporting my query with formatting. The names I expected indeed appeared in the resulting page, but this caused Microsoft to add even more "bloat" than what it does when choosing to export without format. For instance, a table line will look as follows because Access is adding whatever HTML code is needed to reproduce the look of the original query, which is absolutely irrelevant when you need to insert such code into a web page:

<TR VALIGN=TOP><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>Creation, The</FONT></TD>
<TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>Haydn, Joseph</FONT></TD>


</TR>

All this code has to be deleted using a custom macro before inserting the table code into an HTML page. When dealing with an unformatted page, this is rather quick, but awfully long in the case of a formatted page.

The fact remains that I have used lookup tables with success many times, but in the case described in my original post, I am only running in circles. Is there a full-scale tutorial for beginners in the art of avoiding lookup tables and multivalued fields and thus work like the pros?

[orange]

Is there a full-scale tutorial for beginners in the art of avoiding lookup tables and multivalued fields and thus work like the pros?

General MS Access:

MS Access video series by Steve Bishop.

Several articles in the Database Planning and Design link in my signature.
Colin's materials are great.
My guess is you'll have to change your table structure and re-design without using field level lookup and/or multivalued fields.

Here is one veteran programmer's view to lookups/multivalued fields.

[isladogs]

You are getting confused.
Lookup tables are absolutely fine
However lookup fields in tables are what you need to avoid - that's what my article explains.
And, if you didn't do so before, also read the referenced link - the evils of lookup fields in tables: http://access.mvps.org/Access/lookupfields.htm

[maroberge]

Thanks to all for your comments. I have experimented a lot with the export possibilities offered by Access and concluded that my best bet, short of restructuring much of my database, is to export to Excel (formatted). This gives the expected result, as mentioned by Colin in his article Issues with Table Level Lookup Fields. The amount of editing with macros before and after pasting into Dreamweaver is minimal and answers my needs, at least for now. I have started watching Steve Bishop's videos and will surely benefit from them.

[Welshgasman]

I would have thought no restructuring is required?
You just remove the lookup fields in the table. That reveals the true value in the field. Then you use a query to bring the data together, including that in your lookup tables.

No restructuring as I understand it.?