Reading Access Database in Excel (1 Viewer)

[Mr_Si]

Hi all,

I want to link my access database to a new Excel spreadsheet so that the data is stored in Access but Excel can then read information from a table or query and then calculations can be done based on its values.

At the moment, I've got it working but some of the fields have multiple columns as they are a lookup to another table or query.

In access, the data shows as, for example, Manufacturer Name instead of the ID of the Manufacturer name.

When viewed in Excel, it shows the ID column and not the name column, so it's not very helpful.

Is there a way to make Excel show the 2nd column of the field instead of the ID column?

Thanks,
Simon

 

[Mihail]

I think that you need to link Excel to a query from Access.

 

[Mr_Si]

Hi, yes it's looking at a query, not a table. In Access, the query shows the data from the 2nd column, but when Excel reads it, it shows the data from the first 1st column. Which I don't want.

 

[Rx_]

In the Query design, there is a checkbox just below the table, Field...
It determines if the output is presented.
Now, if it is a query, why do you have the ID field anyway? If you used
Select * From... table name
Replace that by dragging the field names that you want, in the order that you want.
When you run the query in Access, Excel should look the same

 

[Mr_Si]

My apologies, I haven't explained properly maybe. I have a query of a table. Some of the table's fields are lookups to other queries including the id so that it can be referenced in vba during using the database. This is how I have always done it.

I have made it so that the column containing the id is set to 0cm so that the next column is the one that displays in access.

Now, if I make an excel file based on the query of this main table, it doesn't pay attention to the 0cm size setting I made in access, so it displays the id column. The access field has 3 columns: idsManufacturerID, chrManufacturer, memDescription.

Is there a way to get excel to display the 2nd column instead of the first column, or do I have to connect more than 1 table/query at the beginning?

 

[Mihail]

If you have no need why you include in the query that column. Remove it.

 

[Mr_Si]

Thanks both for your help.
You made me realise that I don't need to use ID fields in every lookup field that I do, so I edited each lookup query in the main table so that it just contained the named value and not the ID. Now it just displays the manufacturer name by default.

Then Refreshing the data in Excel, it updates showing the info I want.

Thanks.