sequence numbers

[MyDear]

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

 

[jdraw]

Why must??
Relational databases wor very well with 1 fact in 1 field.
Don't over complicate your situation unnecessarily.

Use 2 separate fields; you can concatenate when needed for display/presentation.

 

[MyDear]

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.


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

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

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

if ComboBox= 01 and MyDate = 4/6/2015
MyTable
PK Castumer_Number Seq_Number
1 7 01-004
2 8 01-005
3 9 01-006

and The Sequential Number must reset on each new day.

I hope it's clear now
thanks

 

[jdraw]

Hmm. I don't think your rationale makes this a must.
Also, I would like you to tell us in simple terms:
-- what exactly you are trying to do and
--how do the fields PK, Castumer_Number and Seq_number relate?

PK is often used to represent primary key, and by definition a primary key uniquely identifies a record. You show 3 scenarios, each with similar PK, sequential Castumer_numbers, and a some unidentified routine to create your "sequential_number"
(using PK 3 for example)

PK Castumer_Number Seq_Number
3 3 01-003
3 6 02-003 and
3 9 01-006

Why does Castumer_Number change in your sample?
What exactly are you trying to show readers?

Good luck with your project.

 

[MyDear]

This is My Database:

 

[jdraw]

What exactly is the "business" underlying your database?
Do you have some business facts or a description to help readers with what you are trying to do?

 

[MyDear]

I work at money exchange

 

[jdraw]

The only data model I could find and the supporting facts/ requirements was at
http://www.databaseanswers.org/data_models/money_exchange_for_foreign_students/facts.htm
and
http://www.databaseanswers.org/data_models/money_exchange_for_foreign_students/index.htm

Seems quite specific but it might have some relevance for you.

Good luck.

 

[MyDear]

This is What I Need:

http://www.up-00.com/