Reset Inceremental Number Every Moth

[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.

 

[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!

 

[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