sequence numbers

[ksam14714]

I need to automatically generate a 5-character value for my Business Key. Without any user interaction.

2 character -> from 01,02,03,04 to 05
+
3 character -> Sequential Number (001,002,003).

The Sequential Number must reset on each new day.

thanks

 

[dhookom]

Is this the same question as you asked in your previous thread? If so, please don't start a new thread. You might want to look at the sample database on Roger Carlson's site.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Your requirements are not very clear, so I took a lot of assumptions here:

[pre]
MyTable
PK MyDate First Second
1 4/6/2015 1 1
2 4/6/2015 1 2
3 4/6/2015 1 3
4 4/6/2015 2 1
5 4/7/2015 1 1
6 4/7/2015 2 2
7 4/7/2015 3 3
8 4/7/2015 4 4[/pre]

So for today's date, you may just ask:
[tt]
Select Format(First, '00') & Format(Second, '000') As BusKey
From MyTable
Where MyDate = #Date#
Order By PK

BusKey
01001
02002
03003
04004[/tt]

This is just a wild guess...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[ksam14714]

This is my requirements:
ComboBox= 01 or 02 or 03 or 04 or 05

if ComboBox= [highlight #FCE94F]01[/highlight] and MyDate = [highlight #FCE94F]4/6/2015[/highlight]
MyTable
PK Castumer_Number Seq_Number
1 1 01-001
2 2 01-002
3 3 01-003

if ComboBox= [highlight #FCE94F]02[/highlight] and MyDate = [highlight #FCE94F]4/6/2015[/highlight]
MyTable
PK Castumer_Number Seq_Number
1 4 02-001
2 5 02-002
3 6 02-003

I hope it's clear now
thanks

 

[dhookom]

Apparently you want to create you own autonumber over the selected value from the combo box restarting each date with 001. Did you look at the sample in the link I posted?

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Without any user interaction" - so that's not true, user will have a combo box to (interact) choose 01, 02, 03, 04, or 05 from...

it looks like you can still use a table like this:

[pre]
PK Castumer_Number Seq_Number Seq_N2 MyDate
1 1 1 1 4/6/2015
2 2 1 2 4/6/2015
3 3 1 3 4/6/2015
4 4 2 1 4/6/2015
5 5 2 2 4/6/2015
6 6 2 3 4/6/2015
[/pre]
And concantinate and format Seq_Number (like I have in my previous post) and Seq_N2 for any given day to give you the 01003 sequence number. With any new Customer it is easy to add to this table, just use [tt]MAX(Seq_N2) + 1[/tt] to get the next record/sequence.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[ksam14714]

This is My Database:

 

[ksam14714]

This is What I Need:

http://www.up-00.com/"][/URL]

 

[dhookom]

If the ComboBox is bound, you don't "need" the 01 - 05.

Again "Did you look at the sample in the link I posted?"

Do you understand how to use VBA?

Will you ever have multiple simultaneous users in the application?

Are you familiar with using DMax()?

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Looks to me you are braking your own rules: "3 character -> Sequential Number (001,002,003)."
But you show you want:
01-[red]0001[/red] <- 4 digits

And please answer all Duane's questions in full.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.