Pls Help for setting the special Member ID !!!

[kqueen1]

Hi, I am new babies to use Access 2007. I am prepared the data for the National Martial Art Club. The structure for the Member ID is as follow:

1. The first 2 digits are the first letter of the
Club Name (e.g. Apple Club - 01, Boy Club - 02,
C - 03, D - 04 ... etc)

2. The next 3 digits are the number of the club
(e.g. Apple Club - 001, Aaron Club - 002, Alan
Club - 003 .... etc)

3. The last 3 digits are the number for the Members
(e.g. Crystal Mui - 001, Quinton Kung - 002,
Armanda Rhee - 003 ... etc)

So, if I want enter the Member ID for Armanda Rhee and she is in Apple Club and she is the first one member enter in the system ... the Member ID is 01-001-003

So, have any idea ??? Pls help ppppp!

Thkssssss.

[patjones]

You haven't actually asked a question. Tell us what it is you are having trouble doing and then maybe we can help you. Thanks.

Pat

[NeoPa]

How would anyone (or the computer) know that Apple Club is associated with the number 01? Where is this information made available from?

You leave a lot of important information out of your question. It's hard to help if you don't even provide a question.

[kqueen1]

@NeoPa
I am sorry for my posting not clearly.

I want to enter all the althetics information in the data including their competition results. It is because we will have the Wushu competition event in August. The winner will be represent Ontario to compete with the other provincial in Sept.

So, I use Access Students template for the basic set-up, but the club decided to assign the member ID to every althetics following the above rules. That means they followed their 1st letter of the club name, then which clubs register first is 01, second is 02 ... third is 03 ... etc. The last group is for the althetics.

I tried to create a table for 1st Letter :

ID 1st Letter Number
1 A 01
2 B 02
3 C 03
4 D 04
... until Z #26

then, I have the table name "Clubs" and field name "Clubs ID". When I enter new record for the new club, I want the Access 2007 will auto generic the Club ID #.

e.g.

Club ID Club Name Address
01-001 Apple Club 123 abc st
01-002 Aaron Club 456 def st
02-001 Bay Club 78 Howard st
02-002 Brick Club 90 College St
02-003 Block club 90 King St
... etc

If I have new record for "Advance Club", the Access 2007 will auto post the ID # as "01-003" in the record. How to set the code in the Club ID field?

I am so sorry for my english. I hope all the experts can understand.

Thank you so much.

[NeoPa]

That's much clearer now thank you :)

What you haven't said though, is what data will be entered on the form to work out the number from. Would they eneter the name of the club then the name of the entrant? That can be made to work, but it is not very reliable as names can be duplicated. Normally, one would enter references for items that already exist (as I would hope would be the case for the clubs and the entrants). Thus, if it were done properly, we would probably be looking at just appending the entrant code onto the club code but with a "-" in between.

Can you explain how you understand this would work in your real-life database?

[kqueen1]

Thank you for your fast reply.

Our Associate is the offical wushu association. All the clubs and althetics in Canada have to register in our association. If they failed to register, then they can't compete in the event. Now, I need to create a new data file to record and update their information including althetics and clubs.

The chairman has created the Member ID # format because he want whenever he looked at the Member ID #, then he will know that this member is under which Club and the Club has how many members registered.

So, I created the "Members" table including the fields of Members ID #, Members Name, Address, Telephone, Gender, Year of Registration date, Club Name, .... etc.

Then, I also created the other "Clubs" table including the fields of Clubs ID #, Club Name, Address, Telephone, Contact person.

I also created the "Parents" table including the fields of Name, Address, Telephone, Members Name.

Now, I have the Members as follow:

Name: Aaron Rhee Club Name: Apple Club
Name: Apple Wood Club Name: Aaron Club
Name: Mary Ling Club Name: Apple Club

The Member ID for Aaron Rhee should be # 01-001-001
Apple Wood # 01-002-001
Mary Ling # 01-001-002

We want the Access 2007 can auto generic the Member ID #.

For my limit knowledge in Access 2007, I think I need to solve the Club ID # first, so I created one more table for the 1st Letter of the Club Name, then when I enter the Club Name in the "Club" table, the Access will lookup the 1st Letter of Club Name, pick up the 2 digits code for the 1st Letter and auto adding 1 count from last reord. What code should be enter in Club ID?

I think the Member ID is similar Club ID.

Thks.



but I don't know which code should be enter in "Club ID" field in "Clubs" table. Then, I can enter the almost same code format in "Member ID" field in "Members" table

[patjones]

First, when you enter a new club in the form, will you just be entering the name in a text box?

Second, when you enter a new participant, do you have a means of checking to see whether their club is already in the table? What I'm concerned about is, suppose you enter Aaron Club for the first time and put it's first participant in the table. Then, sometime later, the second participant for Aaron Club is entered, but the user erroneously writes out Araon Club...you need a means of validating the data as it goes in.

Pat

[kqueen1]

1. Yes. I will enter the club name in a text box.

2. When I enter a new participant, I want Access 2007 checking whether the club is already in the table.

Thks.

[patjones]

I think the best thing for you to do is have a section on your form where you enter participant information (last name, first name, address, etc.) - for the club information...you can have a combo box that contains all the clubs that have already been entered.

If the participant's club does not appear in the combo list, perhaps consider clicking command button - say "Add New Club" - which opens up a text box for new club entry.

To pick out the first part of the ID, I would have a simple table setup that relates the letter to the code - 01 for "A", 02 for "B" and so on. The DLookup function can easily retrieve the code based on the first letter of the club name typed in.

The second part of the ID will just be the count of how many people already have been entered for the club, obtained using the DCount function perhaps, plus one. Similarly, the third part of the ID is how many clubs have already been entered that begin with the letter in question - which can also be obtained via DCount.

The first and third parts of the member ID are values that should be stored with the club name when it is first entered.

I hope this is making some kind of sense. It is late and I don't usually post on so little energy.

Pat

[NeoPa]

Pat is talking about the same issue I was trying to get you to see with my posts. He also suggests a viable approach to enable you to handle this smoothly and easily.

There is an alternative to storing letter data in a separate table, which is to use :

Format(Asc({ClubName}) - Asc("A") + 1, "00")

Either way will work well of course.

At the end of the day though, what is important is that you don't simply take names as typed. Get them entered once, then always refer to that item directly, via the unique identifier - not by someone typing in the name. ComboBoxes are a good way to handle this.

[kqueen1]

Oh.... I am very appreciated for all the experts ... I will try tonight and let you know how's going.

[patjones]

I would say that you should get a form setup with some code and try it out - then when you run into specific issues you can let us know and we can help you with those issues. Good luck.

Pat