A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Cumulative Change



 
 
Thread Tools Display Modes
  #1  
Old August 20th, 2004, 07:39 PM
r. howell
external usenet poster
 
Posts: n/a
Default 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  
Old August 20th, 2004, 11:21 PM
MGFoster
external usenet poster
 
Posts: n/a
Default

-----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  
Old August 23rd, 2004, 01:32 PM
Roger Carlson
external usenet poster
 
Posts: n/a
Default

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  
Old August 23rd, 2004, 01:41 PM
r. howell
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 11:49 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.