Reset Inceremental Number Every Moth (1 Viewer)

[sameh.abdelkawy]

Hi there,

I need to create a query to solve this problem:
When entering a new product in the month it has a new serial number starting from 1. This number increments with every new product till the end of the month. A new product in the new month will get a new serial beginning from 1 again.
I need to apply it to the year to.

A serial number resets in the start of every month and every year as well.
Find the attached file.

Thank you in advance for your help.

 

[Gasman]

Have you seen the first thread in Similar threads below, marked Solved?

 

[sameh.abdelkawy]

Yes. Didn't work for me.

 

[Gasman]

Need a little more than that to help.?
What did not work?, show your attempt.

 

[oleronesoftwares]

Need a little more than that to help.?
What did not work?, show your attempt

@sameh.abdelkawy waiting for your reply to @Gasman 's question

 

[The_Doc_Man]

In general, custom numbering works if you are careful in how you specify the "rule" you are applying. You will need to design your tables carefully to make this work, however. If you have this number that you describe, you ALSO have to have something in the table to show the date on which it applies. If there is no date-related field in the table, you can't do this.

If you have year, month, and this sequence number, you can write a DCount query that will tell you exactly what you want. If you have a date-stamp, you can compute what you want. If you don't have a date in the table, you are going nowhere. So.... what ELSE is in this table besides what you showed us earlier?

 

[sameh.abdelkawy]

Need a little more than that to help.?
What did not work?, show your attempt.

I need to create it in a simple steps to the example I attached and not by modules as other examples.

 

[GinaWhipp]

Hmm, so a little confused. Your sample is an Excel spreadsheet. You posted this the Access (database) area.

Are you wanting to do this in Access?

 

[sameh.abdelkawy]

Hmm, so a little confused. Your sample is an Excel spreadsheet. You posted this the Access (database) area.

Are you wanting to do this in Access?

Yes. I need to create the conditions in Access query.

 

[GinaWhipp]

Well, I'm backing out because I do this in module and store the values in a table.

 

[Pat Hartman]

This isn't possible in a query. WHY? because records are not actually committed until the entire recordset is process. Therefore, the Max() value will not change so each new row will be assigned the same number.

You need to use code. I tried to build a function with an internal counter defined as STATIC but I couldn't make it work. If there are multiple year/month values in the append query, you will need a two dimensional array. Play around with it if you want.

If your BE is SQL Server, you might be able to create a query that uses Row-Number to solve the problem. I don't know if other RDBMS' support this concept but Access does not.

I have always done conversions using a DAO recordset so I know I am correctly controlling the sequence number. For new records, the sequence numbers are assigned in a form, one at a time so the simple DMax() method works fine.

 

[sameh.abdelkawy]

Hmm, so a little confused. Your sample is an Excel spreadsheet. You posted this the Access (database) area.

Are you wanting to do this in Access?

Hello,

I need to create these conditions using Access query.
I tried many solutions in this forum but didn't work with me.
I'll be very grateful for any help.

Thanks!

 

[Pat Hartman]

1. WHY do you think you need to do this in a query?
2. Are you doing a conversion or is this an ongoing process?
3. Did you read post #11?
4. Is your BE SQL Server?

 

[sameh.abdelkawy]

1. WHY do you think you need to do this in a query?
2. Are you doing a conversion or is this an ongoing process?
3. Did you read post #11?
4. Is your BE SQL Server?

1- I need to do this with any part of the program. I know that Query can do it easily but don't know how.
2- It's an on going process when adding new product every day, I'll got Automatic number for year and month. When adding product in the first day of the next month, the program resets to the beginning from number 1 again, and so on.
3- Read many posts but not sure about #11, kindly send me the link.
4- Just Windows 10 running Office 2019.
Thanks!

 

[sameh.abdelkawy]

This isn't possible in a query. WHY? because records are not actually committed until the entire recordset is process. Therefore, the Max() value will not change so each new row will be assigned the same number.

You need to use code. I tried to build a function with an internal counter defined as STATIC but I couldn't make it work. If there are multiple year/month values in the append query, you will need a two dimensional array. Play around with it if you want.

If your BE is SQL Server, you might be able to create a query that uses Row-Number to solve the problem. I don't know if other RDBMS' support this concept but Access does not.

I have always done conversions using a DAO recordset so I know I am correctly controlling the sequence number. For new records, the sequence numbers are assigned in a form, one at a time so the simple DMax() method works fine.

I just use Windows 10 and Office 2019. Can I do it?
Many thanks for your time and help.

 

[GinaWhipp]

1- I need to do this with any part of the program. I know that Query can do it easily but don't know how.

It cannot be done in a query. Not sure why you think this. Here's an example of how it would work...

Another post on Sequential Numbering… « Gina's Coffee Shop

regina-whipp.com

 

[Uncle Gizmo]

I know that Query can do it easily but don't know how.

Is this what you mean?

NumberedQuery.mdb (beginner)

Please consider donating $1 per sample downloaded, find out why here. NumberedQuerySQL is a very...

www.rogersaccesslibrary.com

 

[Pat Hartman]

When entering a new product i

It is important to understand the problem. This is a sequence number that needs to be generated in an append query which is different from the examples you all posted which work for select queries or in the BeforeUpdate event of a form. Please Sameth, correct me if I am wrong. Are you looking for a way to do this in an append query??????? Because if you don't care if the sequence number changes when records are deleted, then just generate the sequence number on the fly in the select query.

The entire problem with generating the sequence number revolves around the request to do it in an append query. Doing it in a select query or in a form where you add a new record are both trivial. I asked if the BE was SQL Server because T-SQL has a feature - Row-Number which can probably do this but that doesn't help if the BE is Jet or ACE since they do NOT have that functionality.

I know that Query can do it easily but don't know how.

It cannot be done in a query. Not sure why you think this. Here's an example of how it would work...

You can create a sequence number in a query for EXISTING records if there is a UNIQUEID on which to base it. You can do it with a join that counts the number of rows in the table with an ID less than the current one. It is more complicated with a three column uniqueID but you can still do it.
The problem with "new" records is you can't use the join technique, you need to use the dMax() technique which is what we use in forms when we are adding new records. That doesn't work because the "previous" records are not actually saved until the end of the query so the dMax() always returns the same value. Remember Action queries are encapsulated in a transaction and the transaction is committed in its entirety or not as the last action.

So, if you use in an APPEND query:

Nz(dmax("SeqNum", "YourTable", "YourYear = YourYear and YourMonth = YourMonth"), 0) +1 As Updated Seq

You will generate the same sequence number for all rows that match year and month. So if the max existing seq is 395 and you add 10 new records to that group, all will get the number 396 10 times because the dMax() operates against the SAVED data, not the pending data. Using a function may solve the problem if you can keep a static counter but I wasn't successful.


Here is a sample which you can use to generate exactly the sequence number you want EXCEPT, it works in a form, one record at a time.

I asked if you were converting existing data because that you would do in a DAO code loop using the same technique as the form except that the sequence number is based on an internal counter rather than a dMax()