Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / August 2006

Tip: Looking for answers? Try searching our database.

Access a row/column in DATABASE

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
jamiechen168@gmail.com - 24 Aug 2006 03:38 GMT
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 - 24 Aug 2006 04:31 GMT
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.

Signature

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

> Hello,
>
[quoted text clipped - 30 lines]
>
> Many thanks
Peter Jamieson - 24 Aug 2006 09:35 GMT
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

> Hello,
>
[quoted text clipped - 30 lines]
>
> Many thanks

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.