unique sales order and line number

K

kcirino

I am working on bringing in data from another division. The program that I
use to bring this into our ERP system requires a line number. Is there a way
I can next number by sales order number and restart the number to 1 when the
sales order number changes? If I can do this through tables (autonumber) or
queries I will need help understanding how to use Visual Basic coding in
Access since I am fairly new to using Access for data scrubbing.

Example:
Sales Order # Line #
1 1
1 2
1 3
2 1
3 1
3 2
3 3
 
K

KARL DEWEY

Try this (You may need to remove the +1) --
SELECT Q.[Sales Order #], (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Sales Order #] = Q.[Sales Order #]
AND Q1.[PrimaryKey] < Q.[PrimaryKey])+1 AS [Line #]
FROM Product AS Q
ORDER BY Q.[Sales Order #], Q.[PrimaryKey];
 
K

kcirino

Thanks for the quick response. My SQL is pasted below. I tried with and
without the 1 and the line number was 1 for all records or 0 without the 1.
I think the issue might be that I currently don't have a primary key in the
table so that is why I want to add line number so that the sales order number
and line number make up the primary key. The uniqueid (which is sales order
number) can have multiple records. I did create a separate table that has
the unique id and the number of rows each sales order has. Is there a way I
can link to this and subtract 1 from the max row until I get to 0 and
restart? Thanks again for your help. I am crunched for time to try to get
this data into our system by the end of July.
SELECT Q.uniqueid, (SELECT COUNT(*) FROM [tb_F47012 All Records Including
Line ID] Q1
WHERE Q1.[uniqueid] = Q.[uniqueid]
AND Q1.[uniqueid] < Q.[uniqueid])+1 AS [Line #]
FROM [tb_F47012 All Records Including Line ID] AS Q
ORDER BY Q.[uniqueid];

KARL DEWEY said:
Try this (You may need to remove the +1) --
SELECT Q.[Sales Order #], (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Sales Order #] = Q.[Sales Order #]
AND Q1.[PrimaryKey] < Q.[PrimaryKey])+1 AS [Line #]
FROM Product AS Q
ORDER BY Q.[Sales Order #], Q.[PrimaryKey];

--
Build a little, test a little.


kcirino said:
I am working on bringing in data from another division. The program that I
use to bring this into our ERP system requires a line number. Is there a way
I can next number by sales order number and restart the number to 1 when the
sales order number changes? If I can do this through tables (autonumber) or
queries I will need help understanding how to use Visual Basic coding in
Access since I am fairly new to using Access for data scrubbing.

Example:
Sales Order # Line #
1 1
1 2
1 3
2 1
3 1
3 2
3 3
 
K

KARL DEWEY

You have to have a second field in the
WHERE Q1.[uniqueid] = Q.[uniqueid]
AND Q1.[uniqueid] < Q.[uniqueid])+1 AS [Line #]
otherwise the line # will not step.
You can add an Autonumber field to your table and use that.

--
Build a little, test a little.


kcirino said:
Thanks for the quick response. My SQL is pasted below. I tried with and
without the 1 and the line number was 1 for all records or 0 without the 1.
I think the issue might be that I currently don't have a primary key in the
table so that is why I want to add line number so that the sales order number
and line number make up the primary key. The uniqueid (which is sales order
number) can have multiple records. I did create a separate table that has
the unique id and the number of rows each sales order has. Is there a way I
can link to this and subtract 1 from the max row until I get to 0 and
restart? Thanks again for your help. I am crunched for time to try to get
this data into our system by the end of July.
SELECT Q.uniqueid, (SELECT COUNT(*) FROM [tb_F47012 All Records Including
Line ID] Q1
WHERE Q1.[uniqueid] = Q.[uniqueid]
AND Q1.[uniqueid] < Q.[uniqueid])+1 AS [Line #]
FROM [tb_F47012 All Records Including Line ID] AS Q
ORDER BY Q.[uniqueid];

KARL DEWEY said:
Try this (You may need to remove the +1) --
SELECT Q.[Sales Order #], (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Sales Order #] = Q.[Sales Order #]
AND Q1.[PrimaryKey] < Q.[PrimaryKey])+1 AS [Line #]
FROM Product AS Q
ORDER BY Q.[Sales Order #], Q.[PrimaryKey];

--
Build a little, test a little.


kcirino said:
I am working on bringing in data from another division. The program that I
use to bring this into our ERP system requires a line number. Is there a way
I can next number by sales order number and restart the number to 1 when the
sales order number changes? If I can do this through tables (autonumber) or
queries I will need help understanding how to use Visual Basic coding in
Access since I am fairly new to using Access for data scrubbing.

Example:
Sales Order # Line #
1 1
1 2
1 3
2 1
3 1
3 2
3 3
 
K

kcirino

It worked when I created another autonumber field as a key - You are my hero!
Thanks so much!

KARL DEWEY said:
You have to have a second field in the
WHERE Q1.[uniqueid] = Q.[uniqueid]
AND Q1.[uniqueid] < Q.[uniqueid])+1 AS [Line #]
otherwise the line # will not step.
You can add an Autonumber field to your table and use that.

--
Build a little, test a little.


kcirino said:
Thanks for the quick response. My SQL is pasted below. I tried with and
without the 1 and the line number was 1 for all records or 0 without the 1.
I think the issue might be that I currently don't have a primary key in the
table so that is why I want to add line number so that the sales order number
and line number make up the primary key. The uniqueid (which is sales order
number) can have multiple records. I did create a separate table that has
the unique id and the number of rows each sales order has. Is there a way I
can link to this and subtract 1 from the max row until I get to 0 and
restart? Thanks again for your help. I am crunched for time to try to get
this data into our system by the end of July.
SELECT Q.uniqueid, (SELECT COUNT(*) FROM [tb_F47012 All Records Including
Line ID] Q1
WHERE Q1.[uniqueid] = Q.[uniqueid]
AND Q1.[uniqueid] < Q.[uniqueid])+1 AS [Line #]
FROM [tb_F47012 All Records Including Line ID] AS Q
ORDER BY Q.[uniqueid];

KARL DEWEY said:
Try this (You may need to remove the +1) --
SELECT Q.[Sales Order #], (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Sales Order #] = Q.[Sales Order #]
AND Q1.[PrimaryKey] < Q.[PrimaryKey])+1 AS [Line #]
FROM Product AS Q
ORDER BY Q.[Sales Order #], Q.[PrimaryKey];

--
Build a little, test a little.


:

I am working on bringing in data from another division. The program that I
use to bring this into our ERP system requires a line number. Is there a way
I can next number by sales order number and restart the number to 1 when the
sales order number changes? If I can do this through tables (autonumber) or
queries I will need help understanding how to use Visual Basic coding in
Access since I am fairly new to using Access for data scrubbing.

Example:
Sales Order # Line #
1 1
1 2
1 3
2 1
3 1
3 2
3 3
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top