dmax problem to create an autonumber + 1

[jacc14]

Hi
This is the second query I have had with Dmax. The first one I resolved as it was in a form and by using DMAX everytime I click for new record it gave a new order number ie dmax("ordernumb er", "tbl_orders")+1 .

This problem is quite different and I am hoping someone out there can advice.

I have a group of orders in a table. I tick a box to select which records I want to batch for that particular month. An update query then copies these records to a new table ready for batching.

My query is that each row needs to have a unique reference number. I cant use autonumber as I must specify the first number at the beginning. After that each record will increment by one.

Thanks for your help. Hope all this makes sense

jacc14

[puppydogbuddy]

Hi
This is the second query I have had with Dmax. The first one I resolved as it was in a form and by using DMAX everytime I click for new record it gave a new order number ie dmax("ordernumb er", "tbl_orders")+1 .

This problem is quite different and I am hoping someone out there can advice.

I have a group of orders in a table. I tick a box to select which records I want to batch for that particular month. An update query then copies these records to a new table ready for batching.

My query is that each row needs to have a unique reference number. I cant use autonumber as I must specify the first number at the beginning. After that each record will increment by one.

Thanks for your help. Hope all this makes sense

jacc14

As long as ordernumber is unique, you can use DCount to generate number sequence in your query as shown below: Don't forget to add the other fields besides Sequence and ordernumber to your query

SELECT DCount("ordernu mber", "tbl_orders","o rdernumber <=" & [ordernumber]) AS Sequence, ordernumber
FROM tbl_orders
ORDER BY tbl_orders.orde rnumber;

[jacc14]

Hi there
Just had a go and understand how it works. Problem is that I am only selecting random records from the tbl_orders. As I dont want the first record the sequence is starting at number 2 . If I dont select the 4th record then the sequence is 1 , 2 , 3 , 5 and so on.

Hope this makes sense. Its as if I need to assign a unique number once the selected data has been transfered to the result table rather than before.

Thanks for your help

[puppydogbuddy]

Hi there
Just had a go and understand how it works. Problem is that I am only selecting random records from the tbl_orders. As I dont want the first record the sequence is starting at number 2 . If I dont select the 4th record then the sequence is 1 , 2 , 3 , 5 and so on.

Hope this makes sense. Its as if I need to assign a unique number once the selected data has been transfered to the result table rather than before.

Thanks for your help

Ok, you are correct. I did not realize you were picking random records. But, you can do the same thing with a text field as long as it is unique within the query. For example, if customerName is unique within the query.

SELECT DCount("custome rName ", "tbl_orders "," customerName <=’” & [customerName] & "'") AS Sequence, ordernumber, customerName
FROM tbl_orders
ORDER BY tbl_orders. customerName;

[jacc14]

Thanks for your help

There was no unique record as there are sometimes more orders for one customer.

I have now solved this by running an additional select query from the result table and putting in your original suggest Dcount and sequence method.

Finally got there in the end.

Many thanks
jacc14