Query results different when exporting to Excel

So I have this query that is enclosed. When  i run the query the results for state are the two digit abbreviation from the Link to the Tblstates table, however after the module i wrote creates the excel spreadsheet the two digit ID is rendered in the excel?

*******************************Query************************************************************************

SELECT First(Provider.Reviewer) AS FirstOfReviewer, Provider.DoctorOffice, Provider.Street, Provider.Address2, Provider.City, Provider.State, Provider.Zip, Provider.County, Provider.Phone, Provider.Fax, Provider.[E-mail Address], Provider.Fee, Provider.ProviderType, Provider.Type, Provider.Speciality, Provider.Salutation, Provider.FirstName, Provider.LastName, Provider.Comments, Provider.ShippingAddress, Provider.ShippingAddress2, Provider.ShippingCity, Provider.ShippingState, Provider.ShippingZIP, Provider.ShippingCompany
FROM Provider LEFT JOIN tblStates ON (Provider.ShippingState = tblStates.ID) AND (Provider.State = tblStates.ID)
GROUP BY Provider.DoctorOffice, Provider.Street, Provider.Address2, Provider.City, Provider.State, Provider.Zip, Provider.County, Provider.Phone, Provider.Fax, Provider.[E-mail Address], Provider.Fee, Provider.ProviderType, Provider.Type, Provider.Speciality, Provider.Salutation, Provider.FirstName, Provider.LastName, Provider.Comments, Provider.ShippingAddress, Provider.ShippingAddress2, Provider.ShippingCity, Provider.ShippingState, Provider.ShippingZIP, Provider.ShippingCompany;

************************************Procedure ******************************************************

Private Sub Command71_Click()

 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Qryproviderrpt_all", "C:\EBILteST\Provider_" & Format(Now(), "yyyymmddhhnnss") & ".xlsx", True
 
 FollowHyperlink "C:\EBILteST"

End Sub
seamus9909Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertFabrice LambertCommented:
There is Provider.State and Provider.ShippingState in your select statement, according to your join, these values are also equal to tblStates.ID.
This is why it is present in the output file.
0
seamus9909Author Commented:
But the query return the abbreviation. (NY, PA).  The Excel returns the ID
0
Fabrice LambertFabrice LambertCommented:
Check the fields configuration, are there list of choices associated with it ? (you can see that with table creation mode).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
But the query return the abbreviation. (NY, PA).  The Excel returns the ID

Yet another problem caused by using table level lookups.  

Lookups DO NOT belong on tables.  They belong on forms.  Remove the lookup from the table.  Then rather than exporting the table, create a query that joins the table to the state table and select the abbreviation from there.  Table level lookups simply hide the true value of the stored data. Thye abbreviation is not what is stored in the table.  It is the numeric ID value and every time you use a query or write code, you have to be aware of that and know what value you actually need.  Once you realize that you need to create a query with the join to get the lookup value, you realize that this abomination hasn't actually saved you any work but it has caused confusion.
3
seamus9909Author Commented:
im so confused.  The query is returning the State Abbreviation.  So why when i create the excel does it revert back to the Id value?
0
hnasrCommented:
im so confused.  The query is returning the State Abbreviation.  So why when i create the excel does it revert back to the Id value?
I am surprised.
Try:
  • Copy records from the query in access and paste in excel.
  • Create a table from the query, then export the created access table to excel.

If it copies/exports as you expect, then one should investigate the query. To do that, try to recreate the issue with a demo database and upload.
I am not aware of any special restrictions on exporting queries.
0
Fabrice LambertFabrice LambertCommented:
im so confused.  The query is returning the State Abbreviation.  So why when i create the excel does it revert back to the Id value?
PatHartman gave you the reason:
MS Access give priority to table level lookup when returning query results, but not when exporting.
Either:
- Check your tables design, and remove lookup values.
- Adjust your query so it also pull the values associated with ID.

Optionally, give us or explain your data model so we can help with the query.
0
seamus9909Author Commented:
hi here is a sample of the table (tblprovider) and the previusly stated query.  You will see the results are the state abbr. and not the ID.  

This may help

1.   Run the Query
2.   click on "export to excel" on toolbar
3.   Select "export with formatting and layout" on popup
Excel worksheet shows the state Abbreviaton

if  run steps 1 and 2 above not step 3 then the excel worksheet shows the Lookup IDs of the state.

Is there  a way in the procedure I wrote to also export with formatting and layout"

THank you everyone!  I know i am a novice here
0
hnasrCommented:
Sample is missing from comment.
0
PatHartmanCommented:
im so confused.  The query is returning the State Abbreviation.  So why when i create the excel does it revert back to the Id value?
I told you.  You have a table level lookup.  The export is exporting the ACTUAL table contents NOT what Access is showing you.  This happens in many situations when you use table level lookups and is one of the major reasons that experts recommend against them.  

Remove the table level lookup.  View the data.  You will see that that is what is being exported to Excel.  Read my original suggestion again to see the solution.

I included an example.  tblCompany has two state codes.  One with a lookup and one without.  There are two error queries that show typical problems encountered with table level lookups.  Export qLookup1 and see that the spreadsheet results are different from what you see when you view the query.
TableLevelLookups.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hnasrCommented:
What you get in Excel is the right thing. In query you display the required values to be displayed, say field_name instead of field_id..
View the Lookup properties for the ID  field and check the column widths. The ID field width might be set to 0. Setting the column widths to non-zero value, displays the ID values.

To output the field_name, you need to include it in the query, by joining the relevant code table, or by displaying the description instead of the code in the query.
So what access is outputting is right, but you are confused by what is displayed in the query,
0
seamus9909Author Commented:
Thank you Pat.
0
PatHartmanCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.