How to set up a database for multiple members

[tamisachris]

Hello all, it's been a long while since I've truly used access. I'm wanting to set up a database at my new work because multiple spreadsheets are just not cutting it. I need to track the following: MemberName, School, GradeLevel, StockShow, Animal, DatesOfAnimalsShow, CostPerEntry. Not sure how to set up the tables and queries. Each show of course will have different dates that the kids are showing depending on the animal they are showing. Years ago I was adequate with Access in setting these up (basic queries and such) relationships in queries were not my strong point either. Any help would be appreciated. I'm thinking one table with the MemberName,School,GradeLevel and another table with the Stockshow information? Basically track everything on an entry form for a show.

[June7]

And a third table that associates kids with shows (a "junction" table).

[Micron]

Every entity should have its own table, and that is usually up to the designer to determine what the entities are, depending on the process that the db is supposed to support. I'm seeing maybe 6 entities in that description, if not more - which may or may not include junction tables (for many to many relationships). Since it's been a while, maybe you should review these, with an emphasis on db normalization.
https://www.accessforums.net/showthr...773#post521773

[ArviLaanemets]

...I'm thinking one table with the MemberName,School,GradeLevel

And when the member changes the school?
And what about next year show, when the member has next grade (unless you create a new database for every new year - what is a really bad idea!)?

With information given, you may need something like:
tblMembers: MemberID, ForeName, LastName, ...;
tblSchools: SchoolID, SchoolName;
tblMemberSchools: MemberSchoolID, MemberID, SchoolID, ValidFrom;
tblGrades: Grade, GradeInfo; -- In case grades are all simple numbers, and same grade system is used in all schools, this table may be skipped --
tblMemberGrades: MemberGradeID, Grade, GradeYear;
tblStockShows: StockShowID, StockShowDate, CostPerEntry...;
tblAnimals: AnimalID, Animal;
tblMemberAnimals: MemberAnimalID, MemberID, AnimalID; -- this table is needed when some members can have several animals - when not, the animal info may be added into tblStockShowMembers --
tblStockShowMembers: StockShowMemberID, StockShowID, MemberID;
tblStockShowMemberAnimals: StockShowMemberAnimalsID, StockShowMemberID, MemberAnimalID - in case every member has a single animal, this table can be dropped --

[NoellaG]

Maybe if you could share some of the spreadsheets (with anonymized data of course) you actually use, we would have a better idea of what you need.

[mike60smart]

Upload a copy of your spreadsheets with no confidential data, so we can see what you currently do.