Solved Composite Key Advise (1 Viewer)

stonegold87

Member
Local time
Today, 16:40
Joined
Dec 10, 2020
Messages
37
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

Super Moderator
Staff member
Local time
Today, 11:40
Joined
Feb 19, 2013
Messages
16,554
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

Member
Local time
Today, 16:40
Joined
Dec 10, 2020
Messages
37
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

Super Moderator
Staff member
Local time
Today, 11:40
Joined
Feb 19, 2013
Messages
16,554
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

Super Moderator
Staff member
Local time
Today, 11:40
Joined
Sep 12, 2006
Messages
15,614
...
....
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

Member
Local time
Today, 16:40
Joined
Dec 10, 2020
Messages
37
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

Super Moderator
Staff member
Local time
Today, 11:40
Joined
Feb 19, 2013
Messages
16,554
left join between all relevant fields.
 

Users who are viewing this thread

Top Bottom