If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|