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
|
|||
|
|||
Cumulative Change
I am trying to create a query that will give me a cumulative change in
contract amounts for each change order that is issued. Each Contract can be identified by a Project number and Trade; Change order numbers for each trade are sequential 1-Whatever. For each specific Change order I need to know the cumulative change of this and all previous change orders. I want this as a field in a query, not just on a report, because I am going to have to use this answer to calculate other fields in my query. The results should look something like this: FVF# Trade Change# AmtChOrder CumChange 1234 GC 1 (2.00) (2.00) 1234 GC 2 5.00 3.00 1234 GC 3 4.00 7.00 1235 GC 1 3.00 3.00 My best attempt, using code posted on this board for something that looked similar to me yielded the following SQL SELECT tblChgeOrders.[FVF#], tblChgeOrders.Trade, tblChgeOrders.[Change#], tblChgeOrders.AmtChOrder, (SELECT Sum ([AmtChOrder]![VT]) FROM [tblChgeOrders] As [VT] WHERE [FVF#]![VT]=[FVF#]![tblChgeOrders] AND [Trade]![VT]=[Trade]![tblChgeOrders] AND [AmtChOrder]![VT]=[AmtChOrder]![tblChgeOrders] AND [Change#]![VT]=[Change#]![tblChgeOrders]) AS CumChange FROM tblChgeOrders; This didn't give me syntax errors, but when I tried to run it, the program wanted me to define all of the VT fields. One of my other attempts, using a syntax closer to what someone else had given is: SELECT tblChgeOrders.[FVF#], tblChgeOrders.Trade, tblChgeOrders.[Change#], tblChgeOrders.AmtChOrder, (SELECT Sum (VT.AmtChOrder) FROM [tblChgeOrders] As [VT] WHERE VT.FVF# = tblChgeOrders.[FVF#] AND VT.Trade = tblChgeOrders.Trade AND VT.AmtChOrde r= tblChgeOrders.AmtChOrder AND VT.[Change#] = tbl.ChgeOrders.[Change#]) AS CumChange FROM tblChgeOrders; It says my syntax is wrong,and I don't have a clue where. Any help would be gratefully appreciated, including a change in technique. HELP (and many thanks) |
#2
|
|||
|
|||
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1 Perhaps this query: SELECT [FVF#], Trade, [Change#], AmtChOrder, (SELECT Sum(AmtChOrder) FROM [tblChgeOrders] WHERE FVF# = CO.[FVF#] AND Trade = CO.Trade AND AmtChOrder = CO.AmtChOrder AND [Change#] = CO.[Change#]) AS CumChange FROM tblChgeOrders As CO -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQSZ5WoechKqOuFEgEQLe+ACdGJLBzYeMm9q8PTUc8skiRh 76sM0AnjiX wAdG/xaXo4t5+c5Epeb8OKuS =csLz -----END PGP SIGNATURE----- r. howell wrote: I am trying to create a query that will give me a cumulative change in contract amounts for each change order that is issued. Each Contract can be identified by a Project number and Trade; Change order numbers for each trade are sequential 1-Whatever. For each specific Change order I need to know the cumulative change of this and all previous change orders. I want this as a field in a query, not just on a report, because I am going to have to use this answer to calculate other fields in my query. The results should look something like this: FVF# Trade Change# AmtChOrder CumChange 1234 GC 1 (2.00) (2.00) 1234 GC 2 5.00 3.00 1234 GC 3 4.00 7.00 1235 GC 1 3.00 3.00 My best attempt, using code posted on this board for something that looked similar to me yielded the following SQL SELECT tblChgeOrders.[FVF#], tblChgeOrders.Trade, tblChgeOrders.[Change#], tblChgeOrders.AmtChOrder, (SELECT Sum ([AmtChOrder]![VT]) FROM [tblChgeOrders] As [VT] WHERE [FVF#]![VT]=[FVF#]![tblChgeOrders] AND [Trade]![VT]=[Trade]![tblChgeOrders] AND [AmtChOrder]![VT]=[AmtChOrder]![tblChgeOrders] AND [Change#]![VT]=[Change#]![tblChgeOrders]) AS CumChange FROM tblChgeOrders; This didn't give me syntax errors, but when I tried to run it, the program wanted me to define all of the VT fields. One of my other attempts, using a syntax closer to what someone else had given is: SELECT tblChgeOrders.[FVF#], tblChgeOrders.Trade, tblChgeOrders.[Change#], tblChgeOrders.AmtChOrder, (SELECT Sum (VT.AmtChOrder) FROM [tblChgeOrders] As [VT] WHERE VT.FVF# = tblChgeOrders.[FVF#] AND VT.Trade = tblChgeOrders.Trade AND VT.AmtChOrde r= tblChgeOrders.AmtChOrder AND VT.[Change#] = tbl.ChgeOrders.[Change#]) AS CumChange FROM tblChgeOrders; It says my syntax is wrong,and I don't have a clue where. Any help would be gratefully appreciated, including a change in technique. HELP (and many thanks) |
#3
|
|||
|
|||
I tested this and it didn't work, but by removing this line:
AND AmtChOrder = CO.AmtChOrder it did. SELECT [FVF#], Trade, [Change#], AmtChOrder, (SELECT Sum(AmtChOrder) FROM [tblChgeOrders] WHERE FVF# = CO.[FVF#] AND Trade = CO.Trade AND [Change#] = CO.[Change#]) AS CumChange FROM tblChgeOrders As CO -- --Roger Carlson www.rogersaccesslibrary.com Reply to: Roger dot Carlson at Spectrum-Health dot Org "MGFoster" wrote in message hlink.net... -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Perhaps this query: SELECT [FVF#], Trade, [Change#], AmtChOrder, (SELECT Sum(AmtChOrder) FROM [tblChgeOrders] WHERE FVF# = CO.[FVF#] AND Trade = CO.Trade AND AmtChOrder = CO.AmtChOrder AND [Change#] = CO.[Change#]) AS CumChange FROM tblChgeOrders As CO -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQSZ5WoechKqOuFEgEQLe+ACdGJLBzYeMm9q8PTUc8skiRh 76sM0AnjiX wAdG/xaXo4t5+c5Epeb8OKuS =csLz -----END PGP SIGNATURE----- r. howell wrote: I am trying to create a query that will give me a cumulative change in contract amounts for each change order that is issued. Each Contract can be identified by a Project number and Trade; Change order numbers for each trade are sequential 1-Whatever. For each specific Change order I need to know the cumulative change of this and all previous change orders. I want this as a field in a query, not just on a report, because I am going to have to use this answer to calculate other fields in my query. The results should look something like this: FVF# Trade Change# AmtChOrder CumChange 1234 GC 1 (2.00) (2.00) 1234 GC 2 5.00 3.00 1234 GC 3 4.00 7.00 1235 GC 1 3.00 3.00 My best attempt, using code posted on this board for something that looked similar to me yielded the following SQL SELECT tblChgeOrders.[FVF#], tblChgeOrders.Trade, tblChgeOrders.[Change#], tblChgeOrders.AmtChOrder, (SELECT Sum ([AmtChOrder]![VT]) FROM [tblChgeOrders] As [VT] WHERE [FVF#]![VT]=[FVF#]![tblChgeOrders] AND [Trade]![VT]=[Trade]![tblChgeOrders] AND [AmtChOrder]![VT]=[AmtChOrder]![tblChgeOrders] AND [Change#]![VT]=[Change#]![tblChgeOrders]) AS CumChange FROM tblChgeOrders; This didn't give me syntax errors, but when I tried to run it, the program wanted me to define all of the VT fields. One of my other attempts, using a syntax closer to what someone else had given is: SELECT tblChgeOrders.[FVF#], tblChgeOrders.Trade, tblChgeOrders.[Change#], tblChgeOrders.AmtChOrder, (SELECT Sum (VT.AmtChOrder) FROM [tblChgeOrders] As [VT] WHERE VT.FVF# = tblChgeOrders.[FVF#] AND VT.Trade = tblChgeOrders.Trade AND VT.AmtChOrde r= tblChgeOrders.AmtChOrder AND VT.[Change#] = tbl.ChgeOrders.[Change#]) AS CumChange FROM tblChgeOrders; It says my syntax is wrong,and I don't have a clue where. Any help would be gratefully appreciated, including a change in technique. HELP (and many thanks) |
#4
|
|||
|
|||
Thank You so very much.
That did the trick, except that I needed to enclose the FVF# after WHERE in brackets before it would accept the syntax. (Which I mention just in case anyone else is trying to copy this and adapt it to their situation). I guess I am learning that it would have been a good idea to keep the # symbol out of my field names. "MGFoster" wrote: -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Perhaps this query: SELECT [FVF#], Trade, [Change#], AmtChOrder, (SELECT Sum(AmtChOrder) FROM [tblChgeOrders] WHERE FVF# = CO.[FVF#] AND Trade = CO.Trade AND AmtChOrder = CO.AmtChOrder AND [Change#] = CO.[Change#]) AS CumChange FROM tblChgeOrders As CO -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQSZ5WoechKqOuFEgEQLe+ACdGJLBzYeMm9q8PTUc8skiRh 76sM0AnjiX wAdG/xaXo4t5+c5Epeb8OKuS =csLz -----END PGP SIGNATURE----- r. howell wrote: I am trying to create a query that will give me a cumulative change in contract amounts for each change order that is issued. Each Contract can be identified by a Project number and Trade; Change order numbers for each trade are sequential 1-Whatever. For each specific Change order I need to know the cumulative change of this and all previous change orders. I want this as a field in a query, not just on a report, because I am going to have to use this answer to calculate other fields in my query. The results should look something like this: FVF# Trade Change# AmtChOrder CumChange 1234 GC 1 (2.00) (2.00) 1234 GC 2 5.00 3.00 1234 GC 3 4.00 7.00 1235 GC 1 3.00 3.00 My best attempt, using code posted on this board for something that looked similar to me yielded the following SQL SELECT tblChgeOrders.[FVF#], tblChgeOrders.Trade, tblChgeOrders.[Change#], tblChgeOrders.AmtChOrder, (SELECT Sum ([AmtChOrder]![VT]) FROM [tblChgeOrders] As [VT] WHERE [FVF#]![VT]=[FVF#]![tblChgeOrders] AND [Trade]![VT]=[Trade]![tblChgeOrders] AND [AmtChOrder]![VT]=[AmtChOrder]![tblChgeOrders] AND [Change#]![VT]=[Change#]![tblChgeOrders]) AS CumChange FROM tblChgeOrders; This didn't give me syntax errors, but when I tried to run it, the program wanted me to define all of the VT fields. One of my other attempts, using a syntax closer to what someone else had given is: SELECT tblChgeOrders.[FVF#], tblChgeOrders.Trade, tblChgeOrders.[Change#], tblChgeOrders.AmtChOrder, (SELECT Sum (VT.AmtChOrder) FROM [tblChgeOrders] As [VT] WHERE VT.FVF# = tblChgeOrders.[FVF#] AND VT.Trade = tblChgeOrders.Trade AND VT.AmtChOrde r= tblChgeOrders.AmtChOrder AND VT.[Change#] = tbl.ChgeOrders.[Change#]) AS CumChange FROM tblChgeOrders; It says my syntax is wrong,and I don't have a clue where. Any help would be gratefully appreciated, including a change in technique. HELP (and many thanks) |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Look to change font color within a cell formula | Steve Kaitz | General Discussion | 2 | June 22nd, 2004 11:00 PM |
Change Font Color when I type the letter | Anne Fu | Worksheet Functions | 3 | May 19th, 2004 06:06 AM |
One change affects several other cellrange references | JMay | Worksheet Functions | 6 | April 30th, 2004 09:20 PM |
Writing a macro to change external links to manual updating in Excel 2000 | John Wirt | Links and Linking | 5 | February 16th, 2004 09:03 AM |
Change "Company" field in File Properties | Trixie | Worksheet Functions | 1 | October 28th, 2003 01:43 AM |