Exporting the results of a query to Excel

[Joe B.]

All,

I have created a query in Access 2007 which pulls data from the table for a certain time period. The query contains lookups so the results of the query display table values rather than ID's. However, when I export the results of the query to Excel, the ID's not the values are displayed. How can I get access to export the exact results of the query.

Thanks in advance for the assistance.

Joe B.

 

[pbaldy]

One of the many reasons most of us recommend against lookup fields. You need to join the lookup tables in the query so you can return the display value.

 

[Joe B.]

Not sure what you mean. I am not very experienced with Access. My query displays the table values but why do the values not get exported to Excel?

 

[pbaldy]

Because the table is actually storing the ID, it just shows you the other value:

http://access.mvps.org/access/lookupfields.htm

 

[Joe B.]

If I do no use lookup fields, how do I get the queries and reports to show the values rather than ID's

 

[pbaldy]

Like I said, you join the two tables together. Here's what it looks like in design view.

 

[Joe B.]

I got it now. I created a new query which contains the two tables rather than using the lookup. This works great for displaying the value but for some reason the query does not return all the values in the table. It seems strange but the number of records returned by the query is limited to the number of records in the second table on which the join was created. In the original table there are 38 records but the query only returns 5 records which is the number of records in the second table.

Thanks for all you help.

 

[pbaldy]

Click on the join line to edit it. Choose the appropriate "Return all records from...".

 

[Joe B.]

Thanks. I assume I can add multiple tables and joins to eliminate other table look ups.

 

[pbaldy]

Happy to help, and yes.

 

[Joe B.]

There are three options when I click on the join and select Join Properties:
1. Only include rows where the joined fields from both tables are equal
2. Include all rows from table "A" and only those records from table "B" where the joined fields are equal.
3. Include all records from table "B" and only those records from table "A" where the joined fields are equal.

I have tried to each option and am still not having all the records returned from Table A correctly. Either the records are limited to the number of rows in Table B or all the records in Table A are returned but not each row is filled with a value from Table B. In Table A there is more than one occurance from Table B.

Example: Table A Has fields Date, Asset Type, and value. Table B has the different asset types: so for different dates the same Asset Type would have different values:

January, Equities, $100
January, Bonds, $200
January, ADR, $300
February, Equities, $150 ....

Only one instance of Equities is returned. How do I get all the records returned?

Thanks again.
Joe B.

 

[pbaldy]

Can you post the db here?

 

[Joe B.]

Will post tomorrow...Thanks for the help so far

 

[Joe B.]

Here is the db. Thanks so much for your help.

 

[pbaldy]

Two problems. First, the query designer automatically set a join on the field named "ID" in all tables, since it assumes fields of the same name are the appropriate ones to join on, which isn't the case for you. Second, your join fields are of different data types (Autonumber in the lookup tables, text in the Average Spread table).

If I fix those two this query appears to work.

SELECT [Average Spread].ID, [Average Spread].Year1, [Average Spread].Quarter1, [Asset Class].[Asset Class], [Global Regions].[Global Regions], [Average Spread].Spread
FROM ([Average Spread] INNER JOIN [Asset Class] ON [Average Spread].[Asset Class] = [Asset Class].ID) INNER JOIN [Global Regions] ON [Average Spread].[Global Region] = [Global Regions].ID
ORDER BY [Average Spread].ID;

 

[Joe B.]

Is there a way to select the fields to join manually? Also Not sure what you mean about the fields are of different data types. The only autonumber field is the primary key. The join field Asset Class and Global Region are test fields in all the tables. Sorry for being such a dolt about this but I am pretty new to Access.

 

[pbaldy]

You can right-click on the existing joins to delete them, then click and drag between the correct fields to create new ones. The Global Region and Asset Class fields in Average Spread are text. They should be Number/Long Integer to match up to the autonumber fields in the lookup tables.

 

[Joe B.]

That worked....Thanks so much for you help

 

[pbaldy]

No problemo!