Line numbers in a transaction

[Guest]

Hi

I have a database where I have a number of transactions (numbered from 1 up)
with a number of transaction lines in each. For example, transaction 1 might
have 2 lines of info, transaction 2 may have 5 lines. I have a table with
the transaction lines in and I need to create a query that numbers the lines
of each transaction.
In my example, I might have:
trans item
1 apple
1 orange
2 apple
2 pear
2 peach
2 orange
2 strawberry

What I need to get to is this:
trans line item
1 1 apple
1 2 orange
2 1 apple
2 2 pear
2 3 peach
2 4 orange
2 5 strawberry

So each line in each transaction needs its own line number (within that
transaction). I don't see how to do it with count - or anything else,
actually!

Thanks in advance!
Andy.

 

[KARL DEWEY]

Try this, putting your tablename in place of Product (two places) ---
SELECT Q.trans, Q.Item, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[trans] = Q.[trans]
AND Q1.Item< Q.Item)+1 AS Line
FROM Product AS Q
ORDER BY Q.trans, Q.Item;

 

[Jackie L]

Do you need to see the values in a query or do you need the values added to
the tables?

 

[KARL DEWEY]

Yes you can use an IIF statement in criteria.

 

[KARL DEWEY]

Posted to wrong thread.

 

[Guest]

Hi

Thanks for your response. I would like the values squirting into the table.
I've surfed the groups but not found anything like it!

Andy.

 

[Guest]

Hi Karl

Thanks for your reply! I'm only a beginner - so be gentle with me!

Your query worked - but I'd like to update the Line field in the table with
the line number produced by your query.
I've tried a few ways of changing your query to make it update but I can't
work it out. Please help me again!
Thanks.
Andy.

Try this, putting your tablename in place of Product (two places) ---
SELECT Q.trans, Q.Item, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[trans] = Q.[trans]
AND Q1.Item< Q.Item)+1 AS Line
FROM Product AS Q
ORDER BY Q.trans, Q.Item;

--
KARL DEWEY
Build a little - Test a little

Hi

I have a database where I have a number of transactions (numbered from 1
up)
with a number of transaction lines in each. For example, transaction 1
might
have 2 lines of info, transaction 2 may have 5 lines. I have a table with
the transaction lines in and I need to create a query that numbers the
lines
of each transaction.
In my example, I might have:
trans item
1 apple
1 orange
2 apple
2 pear
2 peach
2 orange
2 strawberry

What I need to get to is this:
trans line item
1 1 apple
1 2 orange
2 1 apple
2 2 pear
2 3 peach
2 4 orange
2 5 strawberry

So each line in each transaction needs its own line number (within that
transaction). I don't see how to do it with count - or anything else,
actually!

Thanks in advance!
Andy.

 

[KARL DEWEY]

You should not store the line numbers in a table as data may be added,
changed, or deleted. When that happens the stored line number will be in
error. Just run the query when the information is needed.
--
KARL DEWEY
Build a little - Test a little

Hi Karl

Thanks for your reply! I'm only a beginner - so be gentle with me!

Your query worked - but I'd like to update the Line field in the table with
the line number produced by your query.
I've tried a few ways of changing your query to make it update but I can't
work it out. Please help me again!
Thanks.
Andy.

Try this, putting your tablename in place of Product (two places) ---
SELECT Q.trans, Q.Item, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[trans] = Q.[trans]
AND Q1.Item< Q.Item)+1 AS Line
FROM Product AS Q
ORDER BY Q.trans, Q.Item;

--
KARL DEWEY
Build a little - Test a little

Hi

I have a database where I have a number of transactions (numbered from 1
up)
with a number of transaction lines in each. For example, transaction 1
might
have 2 lines of info, transaction 2 may have 5 lines. I have a table with
the transaction lines in and I need to create a query that numbers the
lines
of each transaction.
In my example, I might have:
trans item
1 apple
1 orange
2 apple
2 pear
2 peach
2 orange
2 strawberry

What I need to get to is this:
trans line item
1 1 apple
1 2 orange
2 1 apple
2 2 pear
2 3 peach
2 4 orange
2 5 strawberry

So each line in each transaction needs its own line number (within that
transaction). I don't see how to do it with count - or anything else,
actually!

Thanks in advance!
Andy.

 

[Guest]

Hi Karl

I need to output to a csv file with the trans number and the line number, so
I am going to run the update immediately before exporting the table.

Cheers.
Andy.

You should not store the line numbers in a table as data may be added,
changed, or deleted. When that happens the stored line number will be in
error. Just run the query when the information is needed.
--
KARL DEWEY
Build a little - Test a little

Hi Karl

Thanks for your reply! I'm only a beginner - so be gentle with me!

Your query worked - but I'd like to update the Line field in the table
with
the line number produced by your query.
I've tried a few ways of changing your query to make it update but I
can't
work it out. Please help me again!
Thanks.
Andy.

Try this, putting your tablename in place of Product (two places) ---
SELECT Q.trans, Q.Item, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[trans] = Q.[trans]
AND Q1.Item< Q.Item)+1 AS Line
FROM Product AS Q
ORDER BY Q.trans, Q.Item;

--
KARL DEWEY
Build a little - Test a little


:

Hi

I have a database where I have a number of transactions (numbered from
1
up)
with a number of transaction lines in each. For example, transaction 1
might
have 2 lines of info, transaction 2 may have 5 lines. I have a table
with
the transaction lines in and I need to create a query that numbers the
lines
of each transaction.
In my example, I might have:
trans item
1 apple
1 orange
2 apple
2 pear
2 peach
2 orange
2 strawberry

What I need to get to is this:
trans line item
1 1 apple
1 2 orange
2 1 apple
2 2 pear
2 3 peach
2 4 orange
2 5 strawberry

So each line in each transaction needs its own line number (within
that
transaction). I don't see how to do it with count - or anything else,
actually!

Thanks in advance!
Andy.

 

[KARL DEWEY]

I recommend using a Make Table query to create a temporary table.
--
KARL DEWEY
Build a little - Test a little

Hi Karl

I need to output to a csv file with the trans number and the line number, so
I am going to run the update immediately before exporting the table.

Cheers.
Andy.

You should not store the line numbers in a table as data may be added,
changed, or deleted. When that happens the stored line number will be in
error. Just run the query when the information is needed.
--
KARL DEWEY
Build a little - Test a little

Hi Karl

Thanks for your reply! I'm only a beginner - so be gentle with me!

Your query worked - but I'd like to update the Line field in the table
with
the line number produced by your query.
I've tried a few ways of changing your query to make it update but I
can't
work it out. Please help me again!
Thanks.
Andy.

Try this, putting your tablename in place of Product (two places) ---
SELECT Q.trans, Q.Item, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[trans] = Q.[trans]
AND Q1.Item< Q.Item)+1 AS Line
FROM Product AS Q
ORDER BY Q.trans, Q.Item;

--
KARL DEWEY
Build a little - Test a little


:

Hi

I have a database where I have a number of transactions (numbered from
1
up)
with a number of transaction lines in each. For example, transaction 1
might
have 2 lines of info, transaction 2 may have 5 lines. I have a table
with
the transaction lines in and I need to create a query that numbers the
lines
of each transaction.
In my example, I might have:
trans item
1 apple
1 orange
2 apple
2 pear
2 peach
2 orange
2 strawberry

What I need to get to is this:
trans line item
1 1 apple
1 2 orange
2 1 apple
2 2 pear
2 3 peach
2 4 orange
2 5 strawberry

So each line in each transaction needs its own line number (within
that
transaction). I don't see how to do it with count - or anything else,
actually!

Thanks in advance!
Andy.