Imported data as numeric key?

  • Thread starter Thread starter steve c
  • Start date Start date
S

steve c

Thanks for Reading. I had a problem and John Nurick was
kind enough to help me, but I am an access moron :D.

I checked the "Joins" in the query (I'm not a database
expert ny ANY means, just an access amateur) and they
seemed to be there and working as they should? I don't
know any SQL. I don't know how to "modify a query so it
explicitly joins related tables." Can someone help me
out here? Thanks a lot, and sorry for making everyone go
into an incredible amount of detail in order to help me.

Below is the messages exchanged between John and I.

Thanks,

Steve C


My original message,
I am exporting the results of a select query to excel.
When i run the query, the results appear as I want them
to, for instance, "Passenger Name" shows "John Doe."
However, when i select the query and run a File - Export
to excel, the information in certain fields is
represented by what appears to be a primary key rather
than the field with the actual text information I want to
look at. Does anyone know what is causing this to happen
or how I can fix it?
Click to expand...


John's Response >>


Hi Steve,

This sounds like one of the many gotchas of Access's
misbegotten "lookup
fields". See http://www.mvps.org/access/lookupfields.htm
for other
reasons why these sooner or later cause more problems
than they solve.

Modify your query so it explicitly joins the related
tables and it shoul
export OK.
 
Steve:

First check to verify that your Autonumber keys are part of you query, if they are, do they need it to be there? I assume that in an export to Excel you would not need the Access autonumber key. Anyway, if you want to get rid of them, simply at the query level, uncheck the checkbox in the "SHOW" criteria line (4th line down in the query grid) of the numeric field you want left out of the export process.

That's it! If it's not visible, it does not export. I hope this helps you. I had this problem early in my carreer as an Access developer too.
 
Steve,

If the query is returning key values from one table instead of the
related values from another table there *must* be something wrong with
the joins. Probably this is that you are relying on the join that's
"built into" the lookup field (remember, a lookup field *stores* the key
value but *displays* the related value from the lookup table), but this
is failing to work as you export.

Suppose the Lookup field is called LookupCity, and it stores values from
the CityID field in the Cities table and displays values from the
CityName field.

If you create a query
SELECT Field1, Field2, LookupCity FROM MyTable;
the lookup field will usually display the city name.

To do the same thing with an explicit join it's something like
SELECT MyTable.Field1, MyTable.Field2, Cities.CityName
FROM MyTable INNER JOIN Cities
ON MyTable.LookupCity=Cities.CityID;
 
Back
Top