table programming dlookup & insert into vs query

[Guest]

Table1 (currently populated data)

ID Ref Quant

1 A 500
2 A 350
3 A 400
4 B 550
5 B 600
6 C 200


Column Ref changes randomly. Values in Quant are random. This is the end
result desired:

ID Ref Quant SeriesStart SeriesEnd

1 A 500 1 500
2 A 350 501 850
3 A 400 851 1250
4 B 550 1 550
5 B 600 551 1150
6 C 200 1 200

The SeriesStart resets to 1 when Ref changes mode. SeriesStart othewise is
+1 of the prior SeriesEnd. SeriesEnd value relative to Quant is just a
running sum, until Ref change mode causes a reset.

I had not thought a query calc would be viable so was approaching the table
like an array and was going to vb code the solution - which isn't that much
of a biggie I don't think. It was the LookUp and InsertInto commands to get
values in/out of the table that I'm not so sure about......

would welcome advice on this

 

[Duane Hookom]

Try this SQL
SELECT Table1.ID, Table1.Ref, Table1.Quant,
Nz((SELECT Sum(Quant)
FROM table1 t
WHERE t.Ref = table1.Ref AND T.ID<Table1.ID),0)+1 AS SeriesStart,
(SELECT Sum(Quant)
FROM table1 t
WHERE t.Ref = table1.Ref AND T.ID<=Table1.ID) AS SerieEnd
FROM Table1;

 

[Guest]

a couple confusion points:


FROM table1 t

is there really a space between 1 and t? not quite sure I follow this
part....
T is the name of the second table?

 

[Guest]

VB throws a compile error - it highlights the first Table1 appearance and
state 'expected Case'


I do have the table named Table1

 

[Guest]

copied in as written - it highlights first line SELECT TAble1 etc....and
states syntax error...

copied in as written but eliminated line breaks so it is one long statement
and it highlights the entire statement and states syntax error....

current table is named Table1 and destination table is called table T

 

[Duane Hookom]

The syntax that I sent was the SQL view of a query. There was no code or
modules involved.

 

[Guest]

ok - to where do I send the bottle of wine in thanks for your help !!

and I would like to learn more about structuring SQL statements as it is
definitely different than VB.... (I still don't get your t. stuff...) if
you could point to a reference book or site that you recommend that would be
very welcomed....thanks again.

 

[Duane Hookom]

Jeff Conrad has some Access book links at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Books. I
would recommend the Hernandez/Viescas book on SQL queries. I also like
"Access Database Design & Programming" by Steven Roman published by O
Reilly.