mchapman286
asked on
Problem with Query Export to Excel
I am trying to create Excel Spreadsheets from Queries so the end user can manipulate the data easier.
I am able to Export the Query to Excel using TransferSpreadsheet. My problem is when I open the spreadsheet the columns that pull information from lookup tables show the number instead of the name.
For example I have a table of sales people names that feed the field SalesPerson in another table. The field SalesPerson it shows the names you can choose from and hides the corresponding record number. When I run the query the name will show in the query results but when export the query to excel using transfer spreadsheet, only the corresponding number shows and not the name.
Any help would be greatly appreciated! Thanks!
I am able to Export the Query to Excel using TransferSpreadsheet. My problem is when I open the spreadsheet the columns that pull information from lookup tables show the number instead of the name.
For example I have a table of sales people names that feed the field SalesPerson in another table. The field SalesPerson it shows the names you can choose from and hides the corresponding record number. When I run the query the name will show in the query results but when export the query to excel using transfer spreadsheet, only the corresponding number shows and not the name.
Any help would be greatly appreciated! Thanks!
rockiroads
Might need to look at your query as whatever is outputted by the query should be dumped in excel.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have used the lookup wizard when designing my tables and have done so for quite a few fields. I realize that the number is stored and not the name, but whenever I created queries/reports the corresponding name has always appeared. Because that was what I was after I never thought too much about what was going on behind the sences.
Does anyone know of a way that I can take my query results which show the names that I want from the lookup fields and automatically move these results to Excel. What I would like is to have one button that runs a report and another button which simply runs the query, moves the results to excel and opens the corresponding spreadsheet. Is this possible or do I have to stick to the transfer spreadsheet option?
What is the proper way to design a table that looks up values from another table? In future should I not have an autonumber and just put the names as the primary key so the reference table only has one field in it?
Thanks for your help everyone!
Does anyone know of a way that I can take my query results which show the names that I want from the lookup fields and automatically move these results to Excel. What I would like is to have one button that runs a report and another button which simply runs the query, moves the results to excel and opens the corresponding spreadsheet. Is this possible or do I have to stick to the transfer spreadsheet option?
What is the proper way to design a table that looks up values from another table? In future should I not have an autonumber and just put the names as the primary key so the reference table only has one field in it?
Thanks for your help everyone!
ASKER
Thanks Capricorn1:
I added the lookup tables to my query and then made sure the joins were correct and added the fields with the names but now when I click the button that runs the transferspreadhsheet command I receive a Too Many Fields Defined error. The query itself only has 22 fields in it. However the query itself will run with no errors.
I added the lookup tables to my query and then made sure the joins were correct and added the fields with the names but now when I click the button that runs the transferspreadhsheet command I receive a Too Many Fields Defined error. The query itself only has 22 fields in it. However the query itself will run with no errors.
post the codes you are using for the transferspreadsheet...
are you getting correct information when opening the query by itself?
are you getting correct information when opening the query by itself?
ASKER
Thanks Everyone:
I redid the Query and Code and it worked as you suggested. My queries look a mess because of how many tables the information is pulling from but it worked. I'll try to modify my table design going forward.
I redid the Query and Code and it worked as you suggested. My queries look a mess because of how many tables the information is pulling from but it worked. I'll try to modify my table design going forward.