Format of a linked excel file

Peterso

Board Regular
Joined
Nov 28, 2012
Messages
88
I have linked to an excel file with Invoice No. in Column A but the Invoice No. doesn't appear in every row (it just print in the first row). How should I do to in query to make it appear in each row Thanks
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,703
Office Version
  1. 365
Platform
  1. Windows
In Access, each record (row) is independent of each other, and the order of the records in and Access table (or any other relational database table) really has no meaning (someone once described it as think of a table in a relational database as a bag of marbles). As such, if you are missing data from certain records, it can really be a pain to work with.

The preference is to clean up the data before it gets into Access. If you can clean up the data in Excel, that would be preferable. I often time will create Excel macros for data cleanup purposes like this.

If the data is imported (and not linked) into an Access table, you could also clean it up using VBA and looping through recordsets. That is a bit more involved, especially if you are not proficient in Access VBA or working with recordsets. If the data is linked, you do not have the ability to easily update the data in Access (Access will not update Excel files without the use of VBA affecting the Excel file directly).

If there is some logic as to what each missing field should have as its value, you may be able to use a calculated field in Access to get the value you need. However note that will NOT actually update the blank field. It is simply a calculated field returning a value. See here: Microsoft Access tips: Subquery basics
 

Watch MrExcel Video

Forum statistics

Threads
1,133,525
Messages
5,659,321
Members
418,496
Latest member
WHYCHumphrey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top