Pages

▼

Friday, March 27, 2020

Switching to Access Probably a Bad Idea

Word cannot link to Access like it does to Excel.  Excel can get data from Access, but in a not very useful way.  My thought was to import into Excel, and link to Word.

The Access query produces:

category dead
'dead' 'Category'
671 FAM
357 FAMNONRES
15 PRIV
5970 PUB
169 RES
124 SCHOOL
18 UNKNOWN
165 WORK
12 WORSHIP

but imported into Excel all the Category names turn into numbers:

SumOfdead cause
6 4
3 5
2 8
3 10
11 18
3 20
3 24



even though I imported them as text.  I think Access normalized all the category names.  It is almost like Access, Word, and Excel were written by separate corporations.

And no, I cannot write a book in SQL queries.

Figured it out.  I imported from the old database, not the new one.

It is clumsy.  Link from Excel to Access query results.  Create graph from data in Excel.  Link from Word to Excel objects.
at

4 comments:

  1. Dean in AzMarch 27, 2020 at 7:38 PM

    Make a report of your table, and export that. Exporting directly tables and queries in any dbs is not a great plan.If I was staying at home, I'd offer to assist, but I am in an Essential Occupation.

    ReplyDelete
  2. Paul SandMarch 28, 2020 at 2:57 AM

    I've never used Access (and barely use Word or Excel) but have you tried exporting the data as a CSV file? It's my dim recollection that CSV is a useful lingua franca for tasks like yours.

    I think (from Googling) that you can import CSV files into Word tables without using Excel as an intermediate step, but I haven't tried that myself.

    ReplyDelete
  3. Clayton CramerMarch 28, 2020 at 9:10 AM

    Export/import is clumsy compared to how Word automatically updates from a spreadsheet.

    ReplyDelete
  4. Paul SandMarch 28, 2020 at 11:14 AM

    Okay. How about mail merge? I *really* haven't tried that, but ... "you can use the mail merge feature to merge any kind of data with Word, such as inventory records, tasks, or whatever you store in Access."

    https://support.microsoft.com/en-us/office/use-mail-merge-to-send-access-data-to-word-053cc639-fe30-4d3a-943d-0bee0892f16a?ui=en-us&rs=en-us&ad=us

    ReplyDelete