Solved Composite Key Advise

[stonegold87]

Dear Members
I know this question has been answered many times but still i need some help in my project
i m designing a student fee collection system
how can i create a unique feeID for every month that include month and year information
FeeID needs to be unique so that student can't be charged twice
To achieve this i thought of composite key by combining (StudentID+MonthID+YearID)
i need a better approach to achieve my goal
any advise or a sample project will be really appreciated
Regards

 

[CJ_London]

rather than a composite key, use a composite index.

keys automatically set allow duplicates to false (otherwise it cannot be a key) - you can do the same with a composite index

Not sure about your MonthID and YearID, just use the month (1-12) and the year value. Note that month and year are reserved words - they are functions which return the relevant values for a date - e.g. for today

?month(date())
1
?year(date())
2021

 

[stonegold87]

thx for your reply CJ i will try to achieve this with composite index
i have month table from where i get month ID
and Year Table from where i get year ID
and i was planning to combine student ID + MonthId + Year ID
Suppose :
Student ID is 1
Month ID is 12 (December)
YearID is 1 (2021)
Result would be #S-1/12/1# (concatenate) now this will be unique
Is this approach wrong?

 

[CJ_London]

no need to concatenate.

In table design, click on the indexes option in the ribbon. You will see how the composite primary key is notated. Select the primary key and untick the primary key option at the bottom. Then rename it to something else - perhaps compositekey. Close the indexes window

Now create a new field called something like tablenamePK of type autonumber and make it your primary key

 

[gemma-the-husky]

...
....
Now create a new field called something like tablenamePK of type autonumber and make it your primary key

This is the point. Add whatever indexes you need to manage the data integrity, but use a simple numeric PK to relate the fee notes to other tables. Its far easy to manipulate a single numeric value.

 

[stonegold87]

i have implemented your solution CJ and its working as desired one more thing how can i get unmatched records based on multiple fields?

 

[CJ_London]

left join between all relevant fields.

 

[stonegold87]

Done thx to all members
Regards