access table structure

I've been asked to add functionality to my existing db. I want to be able to gives students 5 % credit discount when they buy blocks of ten credits every month when they the parents of existing student recommend a new student and the student joins and for as long as the new student stays.

I didn't really want a new table of the parents so how is the best way to achieve this goal. Ive embedded the relationship diagram and I guess it revolves around the student and payment table.

They can make more than one recommendation and receive multiple discounts for each student they bring that stays even if that means we owe them money.

ex
Not sure if this is a good way
ex2
I have to count up the number of recommendations somehow
PeterBaileyUkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
You can add one field to table tblStudents (if one student can be recommended by only one existing student):
Recommended_By_Student_FK with link to this table itself.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterBaileyUkAuthor Commented:
ok so I dont need a new table
0
PeterBaileyUkAuthor Commented:
So my instinct was right to add that last field you can see it in the student table
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PeterBaileyUkAuthor Commented:
So I put that in the new students entry then it works
0
als315Commented:
Yes, sorry, I didn't noticed it
0
als315Commented:
Add tblStudents to Relationships once more and link it to first instance
0
PeterBaileyUkAuthor Commented:
ok yes

I extracted the data in a query like this

SELECT TblStudents.Student_ID, Count(TblStudents_1.RecommendedBy_FK) AS CountOfRecommendedBy_FK
FROM TblStudents LEFT JOIN TblStudents AS TblStudents_1 ON TblStudents.Student_ID = TblStudents_1.RecommendedBy_FK
GROUP BY TblStudents.Student_ID
HAVING (((Count(TblStudents_1.RecommendedBy_FK)) Is Not Null));

Open in new window


so now I can add to relationships too. do I need to enforce and cascade also not sure of the join type did I link between correct fields
ex3
0
PeterBaileyUkAuthor Commented:
I think this is correct nowex4
0
PeterBaileyUkAuthor Commented:
Thank you worked a treat
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.