Linecount in lineitems of invoice

  • Thread starter Thread starter SAC
  • Start date Start date
S

SAC

I'd like a linecount in the lineitems subform of an invoice.

Also, if a line is added later, renumber....for example, if I want to add a
line after line three, I would like the added line to be line four and the
lines after it to be renumbered.

I tried have a line count control on the main form and using before insert
and after insert in the subform, but it doesn't seem to be reliable. Is
there a better way?

Thanks for your help.
 
Use Ranking to give line numbers --
SELECT Column1, Column2, (SELECT COUNT(*)
FROM [YourTable-4] T1
WHERE T1.Column2 <= T.Column2) AS Rank
FROM [YourTable-4] AS T
ORDER BY Column1, Column2;

Or use Ranking in a Group to give line numbers --
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points < Q.Points)+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;
 
Thanks, Karl. I don't think I made it clear...

They want to have each line item printed in the order in which they enter
it.....however, if they forget a line item after line three, they want to
edit it, "insert" the line item between line three and four and have
everything print the new order.


1 - A
2 - B
3 - D
4 - E
5 - F

Edited:

1 - A
2 - B
3 - C
4 - D
5 - E
6 - F

Idealy they want to press a key and have a blank line inserted between, for
example, line three and line four, insert the info, and then have all the
line numbers renumbered.

KARL DEWEY said:
Use Ranking to give line numbers --
SELECT Column1, Column2, (SELECT COUNT(*)
FROM [YourTable-4] T1
WHERE T1.Column2 <= T.Column2) AS Rank
FROM [YourTable-4] AS T
ORDER BY Column1, Column2;

Or use Ranking in a Group to give line numbers --
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points < Q.Points)+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

--
KARL DEWEY
Build a little - Test a little


SAC said:
I'd like a linecount in the lineitems subform of an invoice.

Also, if a line is added later, renumber....for example, if I want to add
a
line after line three, I would like the added line to be line four and
the
lines after it to be renumbered.

I tried have a line count control on the main form and using before
insert
and after insert in the subform, but it doesn't seem to be reliable. Is
there a better way?

Thanks for your help.
Click to expand...
Click to expand...
 
One way I see is to include the line numbers as a field in your table.
Then in your subform after adding a new record (duplicate of existing line
number) have update to increment all line number >= by 1 except for record
with same primary key ID as new record.
Event sequence store line number and primary key ID number. Use these as
criteria in the update query. Then requery.

I would need to test what the best way to trigger the event would be. Maybe
someone else can point you in the right direction.
--
KARL DEWEY
Build a little - Test a little


SAC said:
Thanks, Karl. I don't think I made it clear...

They want to have each line item printed in the order in which they enter
it.....however, if they forget a line item after line three, they want to
edit it, "insert" the line item between line three and four and have
everything print the new order.


1 - A
2 - B
3 - D
4 - E
5 - F

Edited:

1 - A
2 - B
3 - C
4 - D
5 - E
6 - F

Idealy they want to press a key and have a blank line inserted between, for
example, line three and line four, insert the info, and then have all the
line numbers renumbered.

KARL DEWEY said:
Use Ranking to give line numbers --
SELECT Column1, Column2, (SELECT COUNT(*)
FROM [YourTable-4] T1
WHERE T1.Column2 <= T.Column2) AS Rank
FROM [YourTable-4] AS T
ORDER BY Column1, Column2;

Or use Ranking in a Group to give line numbers --
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points < Q.Points)+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

--
KARL DEWEY
Build a little - Test a little


SAC said:
I'd like a linecount in the lineitems subform of an invoice.

Also, if a line is added later, renumber....for example, if I want to add
a
line after line three, I would like the added line to be line four and
the
lines after it to be renumbered.

I tried have a line count control on the main form and using before
insert
and after insert in the subform, but it doesn't seem to be reliable. Is
there a better way?

Thanks for your help.
Click to expand...
Click to expand...
Click to expand...
 
Good idea! Thanks.
KARL DEWEY said:
One way I see is to include the line numbers as a field in your table.
Then in your subform after adding a new record (duplicate of existing line
number) have update to increment all line number >= by 1 except for record
with same primary key ID as new record.
Event sequence store line number and primary key ID number. Use these as
criteria in the update query. Then requery.

I would need to test what the best way to trigger the event would be.
Maybe
someone else can point you in the right direction.
--
KARL DEWEY
Build a little - Test a little


SAC said:
Thanks, Karl. I don't think I made it clear...

They want to have each line item printed in the order in which they enter
it.....however, if they forget a line item after line three, they want to
edit it, "insert" the line item between line three and four and have
everything print the new order.


1 - A
2 - B
3 - D
4 - E
5 - F

Edited:

1 - A
2 - B
3 - C
4 - D
5 - E
6 - F

Idealy they want to press a key and have a blank line inserted between,
for
example, line three and line four, insert the info, and then have all the
line numbers renumbered.

KARL DEWEY said:
Use Ranking to give line numbers --
SELECT Column1, Column2, (SELECT COUNT(*)
FROM [YourTable-4] T1
WHERE T1.Column2 <= T.Column2) AS Rank
FROM [YourTable-4] AS T
ORDER BY Column1, Column2;

Or use Ranking in a Group to give line numbers --
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points < Q.Points)+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

--
KARL DEWEY
Build a little - Test a little


:

I'd like a linecount in the lineitems subform of an invoice.

Also, if a line is added later, renumber....for example, if I want to
add
a
line after line three, I would like the added line to be line four and
the
lines after it to be renumbered.

I tried have a line count control on the main form and using before
insert
and after insert in the subform, but it doesn't seem to be reliable.
Is
there a better way?

Thanks for your help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top