Database for music schools

[emi]

Hello everyone. I am not an access expert but i try to learn.
I am working on a database in Access for a music school. It consists of two tables personal and lessons that are related and give me forms queries and everything works fine but only for the current year. For example. A student studies piano on level a for 2020 and next year on level b. i can update my table lesson but i need to keep a copy of the past years to keep an archive for each student through time. Also if i rename the table to lessons1 i loose the relations with table personal and have to re-design all of my forms and reports.
Can anyone please help?


Unfortunately my database is in Greek but if someone needs more details i could upload some screenshots or the database
Thank you in advance!

[ranman256]

you need a 3rd table, tPersonLessons
note: * = keyed field

*ClientID -long , from tPerson table
*LessonID- long, from tLessons table
*EventDate, date/time
PersLsnID - autonum , to identify this rec.
etc...

this tbl would be a subTable to the tPersons tbl. Then yo can see all the lessions 1 person had thru history.

[emi]

Thank you very much for your help! I am afraid that my problem remains. Every year the table lessons has fields that change. So i need a new table lessons for every year with the same structure related to personal table to get my forms and queries. If i make a new table for each year i have to rename it so i loose all the relations and have to redesign everything. This is the problem i try to fix. Maybe my original table design does not help? I dont know. Any ideas are welcome!

[Welshgasman]

Yes, sounds like you have not got the structure correct.
You would not change/add fields for new lesson subjects, but add new rows for them.

[emi]

thank you for your intrest and help. I tried to make a sample of the database in english and upload it so maybe this will help you undrestand how i worked. This design is working fine for me for one year. I need a way to update the Lessons table but keep all relations same and not having to redisign my fomrs and reports. I hope this will help.
Thanks againMUSIC SCHOOL.zip

[Welshgasman]

Well it is not as bad as I was expecting, but still not good.

You have sort of got the correct idea, but as Ranman256 stated you need another table to join students to lessons.?
At the moment you are duplicating data in Lessons (like you would in an excel worksheet )

I would be having an autonumber in each table, and use that as the foreign Key (FK) where needed.

You appear to not want to correct the DB, so there is little I can offer to help, sorry. You have barely started with this, and now would be the time to correct it before going any further.?
I will say however that as you develop this DB the way that you have it now, things are only going to get worse, not better.

Good luck with it anyway.

[ranman256]

add year to the tLessons table.

[emi]

I would do anything to make the DB work. Problem is i am not an access expert ,as you are obviously ,and dont understand certain things. I am willing to try and improve though. Thanks for your time

[emi]

will do ! Thanks

add year to the tLessons table.

[Welshgasman]

I would do anything to make the DB work. Problem is i am not an access expert ,as you are obviously ,and dont understand certain things. I am willing to try and improve though. Thanks for your time

No I am not an expert, despite what it says here in the forum. I was like you, starting off and learning at the same time. I made mistakes (still do ), but try to learn from them. Even though I do not use Access much these days, I am still keen to learn using it.

I was hesitant in suggesting adding a year field, as whilst that would perhaps solve your problem now, I would just be handing you a bigger shovel to dig yourself deeper into problems.

As I am not an expert it would take me almost as long as you to set up the DB correctly, however I have tried to give you a start with the attached. Which is how I would have started out. get the structure correct, BEFORE creating forms, reports etc.

It still needs work as I do not know if the exam grade should be with the lesson or the PersonLessons, depending on whether that is the grade that must be attained or student grade of the lesson/exam?

I've attached my ERD from my first DB. That links table contains nothing but LINKS to the other tables, but that allows any ship on any date with any rank and any crewmember.

That then allows me to show data as can be seen @ http://www.bibby-gazette.co.uk/

No duplication involved.

HTH

[Micron]

Depending on your needs/wants/business it might be more complicated than 3 tables. Maybe look here for models and see if there is one you like as is or can adapt. In some cases, you would just not include certain fields in your design (like car license number for driving lessons). I find what I'm looking for on the page by doing search from my browser. Ctrl+F if you're using FireFox.

http://www.databaseanswers.org/data_...all_models.htm

[emi]

Thank you very much for taking the time to work on my DB! I will study it and i am sure it will help me a lot

No I am not an expert, despite what it says here in the forum. I was like you, starting off and learning at the same time. I made mistakes (still do ), but try to learn from them. Even though I do not use Access much these days, I am still keen to learn using it.

I was hesitant in suggesting adding a year field, as whilst that would perhaps solve your problem now, I would just be handing you a bigger shovel to dig yourself deeper into problems.

As I am not an expert it would take me almost as long as you to set up the DB correctly, however I have tried to give you a start with the attached. Which is how I would have started out. get the structure correct, BEFORE creating forms, reports etc.

It still needs work as I do not know if the exam grade should be with the lesson or the PersonLessons, depending on whether that is the grade that must be attained or student grade of the lesson/exam?

I've attached my ERD from my first DB. That links table contains nothing but LINKS to the other tables, but that allows any ship on any date with any rank and any crewmember.

That then allows me to show data as can be seen @ http://www.bibby-gazette.co.uk/

No duplication involved.

HTH

[emi]

Very useful! Thanks a lot!!

Depending on your needs/wants/business it might be more complicated than 3 tables. Maybe look here for models and see if there is one you like as is or can adapt. In some cases, you would just not include certain fields in your design (like car license number for driving lessons). I find what I'm looking for on the page by doing search from my browser. Ctrl+F if you're using FireFox.

http://www.databaseanswers.org/data_...all_models.htm

[Welshgasman]

Also look at these links that Micron posted in another thread.

https://www.accessforums.net/showthread.php?t=83326#5

[emi]

Hi! I have worked a little bit on my Db. Here is a sample. Unfortunately its in greek! Hope you get the idea. not knowing VB is a problem so i try to overcome difficulties in other ways.I hope i am on the right path at least.Thanks!

βασηηηηη - Αντίγρα&#9.zip