Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
NTC
Starting Member
21 Posts |
 Posted - 2007-09-10 : 19:33:22
|
| Am humble...need help w/ basic sql since I don't write it often enoughSequence Rev SumPast41 100 n/a2 110 n/a3 105 n/a4 103 4185 55 3736 90 353etc etc (hope that column alignment is lost too badly...)Table has first 2 columns...need the SumPast4 column created in query showing these three columns... don't need to worry about the n/a example just trying to show that the sum is of the current and past three records...thanks in advance |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2007-09-18 : 00:58:36
|
| Hi NTC - not really sure if I understand...So you want to update each SumPast4 field with the sum of all Revs where sequence is between the current sequence number and the current sequence number - 3?Problem with access (assuming you're using JET) is that you can't have a join on an update statement.Is that your problem?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
NTC
Starting Member
21 Posts |
Posted - 2007-09-18 : 11:05:42
|
| thanks for reply. I crafted the correct sql statement that works. (Works fine with Access too)need to find an sql forum location however where there is more activity - an 8 day lag for a basic sql question means there isn't any significant action here I don't think.....am writing more and more sql statements and enjoy it - but now & then need sanity checks on the syntax and structure with others that write sql..... |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2007-09-18 : 18:31:20
|
| Yes, true, this is an SQL Server forum - so Access questions may get missed for a while.It would be great if you could post your solution - in case anyone else out there gets into the same situation as you- we wouldn't want them waiting for 8 days to find an answer...--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
NTC
Starting Member
21 Posts |
Posted - 2007-09-19 : 09:26:54
|
| Well I don't understand the implications that Access and SQL are mutually exclusive. One chooses the db by the need of the user. You wouldn't use a big 18 wheel tractor trailer truck for a job that can be done by a small pick up truck. But just because a small pick up truck is being used it doesn't mean that the technology of the pick up truck is less good - it is just a smaller container. In the same way Access is just a smaller container than SQLServer, Oracle, DB2...but it is still driven by sql.This works:SELECT Sequence, Rev, (SELECT Sum(TableName.Rev) AS SumPast4 FROM TableName WHERE TableName.Sequence <= T1.Sequence AND TableName.Sequence>=(T1.Sequence-3)) AS SumPast4FROM TableName AS T1; |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-09-19 : 09:43:45
|
Access and SQL Server don't use the same query language. It's related, but not the same.
elsasoft.org |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2007-09-19 : 18:40:10
|
| Hi NTCI think you misunderstand. I wasn't making any value judgement about whether to use Access or not. Actually I think Access is fantastic. Nonetheless this is an SQL Server forum, not an Access forum. SQL Server is a completely different software package from Microsoft Access - so there's no inherent accusation in simply saying that most of the people here are focused on SQL Server and the T-SQL language, as opposed to VBA or JET.No need to get upset. We're all nice people really - (well except maybe for [name withheld]).--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
|
|
basic sql math - need help with statement