Hello,

Im requiring help on an database i am trying to create. I am making a database on universities in the area and the courses they offer.

Heres my problem, id like to make a primary key for a course offered(for example) 123456. Id like this primary key to be a concatenation for the information of the course. For example: 123456 would give the information for that course. So, '12' would be the university code (which would give the university name, eg, '12' = University of Sydney), '34' would be the faculty code (which would give the faculty name, eg, '34' = Business) and '56' would be the course code (which would give the course name, eg, '56' = Bachelor in Economics).

I would like the '123456' to be the unique course code, which in itself would give its own information.

Im not sure if im being clear in my problem and what i would like, if you have questions, be sure to ask.

Thank you in advance.
Patricio

You can do this very simply, but I would not make it the real Primary Key, since it is easy to see there will be duplicates.

Make an autonumber field and keep it hidden and use it as the real PK for the record.

Then pull the data for the fields you want to concatenate for you course identifier. Then make this number when you need to display it in a form or report (using a query) like this:

CourseNo.:([UnivCode] & [FacultyCode] & [CourseCode])

The problem is since these are numbers, the concatenation may give you this answer instead of "123456": 12 + 34 + 56 = "102". Therefore, even though you are using numbers, make this a text field since you are never going to do arithmetic with the numbers.

If you create a table for each of these entities, like:

tblUniversity
UnivID - PK (autonumber)
UnivCode - text
UnivName - etc

Then in your combo boxes you can pull the ID, code and name, and display the name, and still have the code to do your on form display of your compound number if you want.

Does this help?


mmcdonal