Format of a linked excel file

[Peterso]

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

 

[Joe4]

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