How do I reference other rows in a query?

[Guest]

I have a table full of individual transactions, and I'm trying to put
together running totals (not absolute totals). Only way I can think of is to
reference the row above or below me in a query to check for variables and
then use that to formulate my data. Is there any way to do this? Thanks in
advance!

 

['69 Camaro]

Hi.

For an example, please see the tip, "How to calculate a running total," on
the following Web page:

http://www.Access.QBuilt.com/html/queries.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

Thx for your response. However, I'm not at all familiar with SQL. How would
I do this in design view?

 

['69 Camaro]

Hi, Leo.

How would
I do this in design view?

You don't. Switch to SQL View to see the actual query. (Select the View ->
SQL View menu when in the query's Design View or Datasheet View.) Replace
everything you see with the sample syntax on that Web page. Replace the
table name with your table's name, the primary key with your table's primary
key, and Pymt with the name of the column in your table that you want to see
the running total of. Then run the query.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

The problem is, I'm trying to go further than that in developing my model.
But I don't know SQL at all, so I'm not sure what to do next without learning
it entirely.

Here's an example of the table I'm working off of:

Part Number Transaction Qty
ABC 2
ABC 1
ABC 4
BYN 5
BYN 18
XYZ 1
XYZ 1

And I want to get a running balance of each part number:

Part Number Transaction Qty Running Balance
ABC 2 2
ABC 1 3
ABC 4 7
BYN 5 5
BYN 18 23
XYZ 1 1
XYZ 1 2

How would I accomplish something like that? And is SQL the only way to do
it? Thank you.

 

['69 Camaro]

Hi, Leo.

I'm trying to go further than that in developing my model.
But I don't know SQL at all, so I'm not sure what to do next without
learning
it entirely.

SQL is to a relational database what the steering wheel is to your car. If
you don't want to control the direction beyond forward and reverse, you
don't need a steering wheel. If you don't want more than simple queries,
you don't need to learn any SQL.

Here's an example of the table I'm working off of:

Part Number Transaction Qty

You should remove the spaces within the column names. Only use alphanumeric
characters and the underscore for names to avoid bugs in your code. Open
the table in Design View and type Part Number and Transaction Qty as the
Caption Properties of these columns if you want to read those names in the
column headers of queries, instead of the real names.

How would I accomplish something like that?

Add an AutoNumber named ID to the table and save the table. Try this syntax
for a running total of each part number:

SELECT P1.PartNum, P1.TransQty,
SUM(P2.TransQty) AS RunningTotal
FROM tblTransactions AS P1 INNER JOIN tblTransactions AS P2
ON (P1.PartNum = P2.PartNum) AND (P2.ID <= P1.ID)
GROUP BY P1.ID, P1.PartNum, P1.TransQty;

And is SQL the only way to do
it?

No. One could also write a VBA procedure that uses a recordset and an
array, then iterate through the recordset record by record, summing each
transaction per part number, and writing each record's column's values and
resulting sum to a table. I don't recommend that method.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.