How to export a table with a field that is normally not present [Access] ? RRS feed

  • Question

  • I have a table names Person which contains for example




    I would like to save this in an excel document. I do this this way:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Person", outputFileName, True

    I would like to also export the brother name associated with the ID read from Relative Table.

    How can this be achieved?

    Tuesday, January 23, 2018 3:43 AM


  • I'm not sure about your table design, because it seems to me that the brother is also a Person.   Currently, as I understand it, you have:

        Name                             Relative
        Brother_ID -------------> ID

    But you could also have Brother_ID point to a different record in the Person table, not needing the Relative table at all.

    That said, given your current table structure, you could create a query with SQL like this:

        SELECT Person.*, Relative.Name As BrotherName
        FROM Person LEFT JOIN Relative ON Person.Brother_ID = Relative.ID

    Save that query with a name of your choice -- qryPersonAndRelatives, for example -- and then export that query using DoCmd.TransferSpreadsheet.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Okol-Gui Tuesday, January 23, 2018 5:02 AM
    Tuesday, January 23, 2018 4:29 AM