Autoincrementing part of a composite key

[SGT68]

My table key looks like this. Its a table that keeps a master record of conferences that occur during a calendar year

ConfYear (Date/Time) primary key
ConfNo (Byte) primary key


As you see its a composite key. Now each time a new record is created i want the ConfNo to autoincrement within its ConfYear. I used a byte as its not usually more than 4. Autonumber does not work as part of a composite key i found out.

2014 1
2014 2
2014 3 etc
and revert to 1 when ConfYear input changes to 2015

Is there a simple way to do this with say, a macro, or is the only way with
VBA and DAO?

 

[namliam]

VBA or a query or a combination

Select max(confno) +1 newKey from yourtable where confyear = year(date())

 

[SGT68]

VBA or a query or a combination

Select max(confno) +1 newKey from yourtable where confyear = year(date())

Thanks, i'll work on that

 

[smshaaban]

I have a similar issue. I want to make the primary key format something like "101-A, 101-B". It would be nice if the key value auto-increments. I'm not familiar with VBA, so could you please explain the other methods for making such primary key format possible?

 

[GinaWhipp]

Have a look at...

http://regina-whipp.com/blog/?p=704 (Sample code available)

 

[SGT68]

I have a similar issue. I want to make the primary key format something like "101-A, 101-B". It would be nice if the key value auto-increments. I'm not familiar with VBA, so could you please explain the other methods for making such primary key format possible?

I think you will get the help you need in your question if you create a separate thread rather than post here as your question in not the same as mine. And I will not get the help i need as helpers will be diverted from my question as the helper GinaWhipp has been. Thanks.

 

[GinaWhipp]

Actually, my link provides solutions to both question but you are right... a new separate post would be best.

 

[SGT68]

Actually, my link provides solutions to both question but you are right... a new separate post would be best.

Thank you Gina for your help to both of us.

 

[smshaaban]

I'm new to the forum, so sorry for breaking any rules. Thanks for the help anyway.

 

[SGT68]

I'm new to the forum, so sorry for breaking any rules. Thanks for the help anyway.

No problem. You did not break any rules, it was just a suggestion, but welcome anyway, I'm quite new here myself, but its a great forum, with lots of really experienced people, and you usually get an answer to your problem quickly.

After i posted i realised you had only be here for two posts, and i felt bad for not being more welcoming and forgiving. Sorry.

 

[smshaaban]

No offense taken, my friend

 

[GinaWhipp]

Hmm, let me give you a proper welcome...

Welcome to the Forum!

 

[smshaaban]

I'm starting to love this forum already. Thanks for the hospitality. I really appreciate it