Access a row/column in DATABASE

[jamiechen168]

Hello,

I used Ctrl+F9 to insert a database successfully. This is mainly
because Mail Merge takes only one data source and I need to use
Database command to insert another set of data.

{ DATABASE \d "C:\\Mytemp\\Office\\OrderData2-Data.csv" \c "" \s
"SELECT Invoice_ID, Invoice_Description, Amount FROM
C:\\Mytemp\\Office\\OrderData-Data.csv" \l "1" \b "191" \h \*
MERGEFORMAT }

"OrderData2-Data.csv" is like this

Order ID Amount
1 10
2 99

What I need to to is to use SUM formula to calculate the total amount
of the above database. I tried to set a Bookmark and use formula and I
cannot get it work, can someone give me a direction on how to get it
working?

This is what I tried so far
- Use SET command to create a bookmark
{ SET TABLE1 { DATABASE \d "C:\\Mytemp\\Office\\OrderData2-Data.csv"
\c "" \s "SELECT Invoice_ID, Invoice_Description, Amount FROM
C:\\Mytemp\\Office\\OrderData-Data.csv" \l "1" \b "191" \h \*
MERGEFORMAT } }
- Then use SUM formula
{ =SUM(TABLE1 B2:B4) }

And I still cannot get it work. Please help

Many thanks

 

[Doug Robbins - Word MVP]

You will probably need to select the data and then use Ctrl+Shift+F9 to
unlink it from its source, which will then convert it into ordinary text.
You should then be able to use a formula to add the items.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Peter Jamieson]

You /may/ be able to do something along the following lines. However,
Microsoft seems to have done something in Word 2003 which adds an extra
paragraph to the result so it may not work for you.

This may also work in Word 2002. Another similar approach should work
(probably better in Word 2000.

Let's suppose you also have a folder called c:\a. Create a text file in it
called t.udl with the following contents:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Mytemp\Office;Extended
Properties="HDR=YES;";Persist Security Info=False;Jet OLEDB:Engine Type=96

Then insert the following database field in addition to the one you already
have:

{ DATABASE \d "C:\\a\\t.udl" \c "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\\a\\;Extended Properties=\"HDR=YES;\";Jet OLEDB:Engine Type=96;"
\s "SELECT sum(Amount) FROM `C:\\Mytemp\\Office\\OrderData-Data.csv`" }

Test the field. If it doesn't work you probably won't be able to use this
general approach in Word 2003. Otherwise, you should notice that the result
is not in a table as DATBASE field results usually are - this is because
when the result only has a single row (notice that there is no \h parameter)
and column, Word does not surround it with a table. Unfortuanately, MS has
done something in more recent versions of Word that /sometimes/ inserts an
additional paragraph mark. However, you may be able to get around for
queries that return a numeric result by nesting the DATABASE field inside an
{ = } field, e.g.

{ ={ DATABASE \d "C:\\a\\t.udl" \c "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\\a\\;Extended Properties=\"HDR=YES;\";Jet OLEDB:Engine Type=96;"
\s "SELECT sum(Amount) FROM `C:\\Mytemp\\Office\\OrderData-Data.csv`" } }

(Both sets of {} need to be the special field braces that you can insert
using ctrl-F9)

Alternatively, you can use an ODBC connection, e.g. instead of t.udl, create
a file called t.dsn containing:

[ODBC]
DRIVER=Microsoft Text Driver (*.txt; *.csv)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=text
DriverId=27
DefaultDir=C:\Mytemp\Office
DBQ=C:\Mytemp\Office

Then insert the following database field:

{ DATABASE \d "c:\\a\\t.dsn" \c "FILEDSN=c:\\a\\t.dsn;" \s "SELECT
sum(Amount) FROM C:\\Mytemp\\Office\\OrderData-Data.csv" }

Peter Jamieson