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!
mchapman286Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
in your query, you have to make a join to the Table that is used as the lookup for the field concerned. Then select rom that Table the column that contains the SalesPerson name
0
 
rockiroadsCommented:
Might need to look at your query as whatever is outputted by the query should be dumped in excel.
0
 
GRayLConnect With a Mentor Commented:
I'll bet you are using the Lookup feature in the Table Design of the table to 'import' the name instead of the number.  While you see the Name, what is actually there is the number, and that is what get's exported.  One of the main reasons you should not use that 'design' feature.  If you have the number in the table, you need a query to get the name from the appropriate table and export the query, not the table.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
mchapman286Author Commented:
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!
0
 
Rey Obrero (Capricorn1)Commented:
mchapman286,

have you seen my comment at http#a24273573

0
 
Rey Obrero (Capricorn1)Commented:
sorry,

mchapman286,

have you seen my comment at http:#a24273573

0
 
mchapman286Author Commented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
post the codes you are using for the transferspreadsheet...

are you getting correct information when opening the query by itself?

0
 
mchapman286Author Commented:
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.
0
All Courses

From novice to tech pro — start learning today.