Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Access a row/column in DATABASE
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 |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Access a row/column in DATABASE
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 wrote in message oups.com... 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 |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Access a row/column in DATABASE
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 wrote in message oups.com... 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 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I access a database in Outlook Express for mail merge | Mailmerge | |||
Mailmerge picture from an Access Database | Microsoft Word Help | |||
Merging from secured Access database | Mailmerge | |||
Trying to establish a dde link with an access database | Mailmerge | |||
How to insert only selected fields from Access database in Word? | Microsoft Word Help |