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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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
PeterBaileyUkAuthor Commented:
So my instinct was right to add that last field you can see it in the student table
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PeterBaileyUkAuthor Commented:
So I put that in the new students entry then it works
als315Commented:
Yes, sorry, I didn't noticed it
als315Commented:
Add tblStudents to Relationships once more and link it to first instance
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
PeterBaileyUkAuthor Commented:
I think this is correct nowex4
PeterBaileyUkAuthor Commented:
Thank you worked a treat
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.