incremental ID number in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to have an incremental ID Number created by a query EG.:

ID product
R1000-1 xyz
R1000-2 fre
R1000-3 abc

Thanks
 
Dear Fred:

From your example, it looks like you want to assign numbers according
to the values in the [product] column in descending order. Please
confirm that.

Next, it appears that all numbers start with "R1000-" followed by a
one digit sequence number that starts from 1. Is that correct?

Do you want these numbers generated dynamically at the moment the
query is run? Or do you want them assigned permanently once they've
been generated?

Is the product column unique?

If you'll answer these questions and give a bit of narrative about
what is going on behind this requirement, perhaps I can help.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I have two tables.

one is a reference header (unique number like an invoice header)
ID
eg.: R1000
R1001
R1002
and the other one is a shipped product table with the reference to the header
HDRID Product
EG.: R1000 XYZ
R1000 FRE
R1000 ABC
R1001 GTR
R1001 GTX
ETC ....

I'm doing a query inner join [Shippedproduct] on [REFHDR].ID =
[Shippedproduct].ID and I would like the reference (R1000) be dynamically
incremented with a -1,-2,-3 .... ETC. and then drop all the record into a
table

Thanks

FRED



Tom Ellison said:
Dear Fred:

From your example, it looks like you want to assign numbers according
to the values in the [product] column in descending order. Please
confirm that.

Next, it appears that all numbers start with "R1000-" followed by a
one digit sequence number that starts from 1. Is that correct?

Do you want these numbers generated dynamically at the moment the
query is run? Or do you want them assigned permanently once they've
been generated?

Is the product column unique?

If you'll answer these questions and give a bit of narrative about
what is going on behind this requirement, perhaps I can help.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I would like to have an incremental ID Number created by a query EG.:

ID product
R1000-1 xyz
R1000-2 fre
R1000-3 abc

Thanks
Click to expand...
Click to expand...
 
Dear Fred:

I'd like to help. You need to read my post and respond to all the
questions I ask or I cannot.

Do you "want to assign numbers according to the values in the
[product] column in descending order?" as I asked.

I see the HDRID column is in the table called Shippedproduct.

My best guess based on possibly incomplete information is to start
with a query:

SELECT HDRID, Product,
(SELECT COUNT(*) + 1 FROM Shippedproduct T1
WHERE T1.HDRID = T.HDRID
AND T1.Product > T.Product) AS Rank
FROM Shippedproduct T
ORDER BY HDRID, Product

Does this product the numbering you want?

When you update the sequence numbering, do you want the result to
sort? The values like R1000-1 could not be a numeric column, but
would be text. Text does not sort numerically. The order could well
look like:

R1000-1
R1000-10
R1000-11
R1000-2

This is the way text sorts - alphabetically not numerically.

I suggest two alternatives. Use leading zeroes, so all the sequence
numbers have the same number of digits. Allowing for numbering only
up to a maximum of 99, it could look like:

R1000-01
R1000-02
R1000-03
R1000-04
..
..
..
R1000-09
R1000-10

That somewhat of an improvement.

If you change the HDRID column to look like this, then you destroy the
relationship with the REFHDR table. It would be better to put the
sequence numbers in a new column. You can transform the SELECT query
to perform the update.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have two tables.

one is a reference header (unique number like an invoice header)
ID
eg.: R1000
R1001
R1002
and the other one is a shipped product table with the reference to the header
HDRID Product
EG.: R1000 XYZ
R1000 FRE
R1000 ABC
R1001 GTR
R1001 GTX
ETC ....

I'm doing a query inner join [Shippedproduct] on [REFHDR].ID =
[Shippedproduct].ID and I would like the reference (R1000) be dynamically
incremented with a -1,-2,-3 .... ETC. and then drop all the record into a
table

Thanks

FRED



Tom Ellison said:
Dear Fred:

From your example, it looks like you want to assign numbers according
to the values in the [product] column in descending order. Please
confirm that.

Next, it appears that all numbers start with "R1000-" followed by a
one digit sequence number that starts from 1. Is that correct?

Do you want these numbers generated dynamically at the moment the
query is run? Or do you want them assigned permanently once they've
been generated?

Is the product column unique?

If you'll answer these questions and give a bit of narrative about
what is going on behind this requirement, perhaps I can help.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I would like to have an incremental ID Number created by a query EG.:

ID product
R1000-1 xyz
R1000-2 fre
R1000-3 abc

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Tom I will answer your question like you said in your last E-Mail I,m sorry
it creates more confusion as I tried to make It simpler.

Q1- Yes, I want to assign numbers according to the value in the [product]
column in descending order.
Q2- Yes, it start from a "R1000-" followed by a one digit sequence number
that starts from 1.
Q3- I want these numbers generated dynamically at the moment the query is run.
Q4- The product column is not unique if unique mean that you cannot have the
same product more than once. The product column is unique in my query because
I'm doing a group by, but record source could have more than one same product
under the same invoice header let's say.

don't worry with the sorting when the auto number generated, I will catch up
with the sorting


Thanks,

I'm sorry for the confusion I create

Fred

Tom Ellison said:
Dear Fred:

From your example, it looks like you want to assign numbers according
to the values in the [product] column in descending order. Please
confirm that.

Next, it appears that all numbers start with "R1000-" followed by a
one digit sequence number that starts from 1. Is that correct?

Do you want these numbers generated dynamically at the moment the
query is run? Or do you want them assigned permanently once they've
been generated?

Is the product column unique?

If you'll answer these questions and give a bit of narrative about
what is going on behind this requirement, perhaps I can help.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I would like to have an incremental ID Number created by a query EG.:

ID product
R1000-1 xyz
R1000-2 fre
R1000-3 abc

Thanks
Click to expand...
Click to expand...
 
Back
Top