467,146 Members | 1,268 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,146 developers. It's quick & easy.

dmax problem to create an autonumber + 1

100+
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("ordernumber", "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
Jul 7 '07 #1
  • viewed: 4144
Share:
4 Replies
puppydogbuddy
Expert 1GB
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("ordernumber", "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("ordernumber", "tbl_orders","ordernumber <=" & [ordernumber]) AS Sequence, ordernumber
FROM tbl_orders
ORDER BY tbl_orders.ordernumber;
Jul 7 '07 #2
100+
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
Jul 7 '07 #3
puppydogbuddy
Expert 1GB
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("customerName ", "tbl_orders"," customerName <= & [customerName] & "'") AS Sequence, ordernumber, customerName
FROM tbl_orders
ORDER BY tbl_orders. customerName;
Jul 8 '07 #4
100+
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
Jul 8 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Apple | last post: by
2 posts views Thread by Apple | last post: by
2 posts views Thread by Apple | last post: by
4 posts views Thread by dskillingstad@gmail.com | last post: by
2 posts views Thread by junkaccount@derrickcompany.com | last post: by
8 posts views Thread by luanhoxung | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.