relations many to many for large amount of data

[Sandro]

Good morning and thank you in advance for your attention.


I have to link a calendar table, made up every day of the year (in date format) to another table containing names.
Every day of the year I must be able to link one or more names (maybe more than 100) so that by selecting a date I can see all the names present on that date, or by selecting a name I could see all the dates in which this name is present.
Kindly I would like your help on how to structure the two tables and how to structure the relationships.
Thank you
Greetings

[ranman256]

if this is like a classroom,
the tStudent table would relate to the tAttend table on the StudentID field.
1 to many. 1 student has many attend dates.

in a single form put the tStudent record.
in a subform it connects to the tAttend table, joined on StudentID to see all dates for the 1 student.

or
in a single form, unbound , put a text box (for date)
in a subform it connects to the tAttend table, joined on DATE, to see all students for 1 date.
(set the query of the subform to look at the date on the form)

[Sandro]

Thanks so much,
I'll try.

[ssanfu]

You will need 3 tables.

tblCalendar
==========
CalendarID_PK - Autonumber
CalendarDate - Date/Time

(DO NOT use DATE as a field/object name - it is a reserved word and a built in function in Access)


tblEmployees
============
EmployeeID_PK - Autonumber
EmpFirstName - Text
EmpLastName - Text

(DO NOT use Name as a field name - it is a reserved word in Access)


jctEmployeesCalendar (junction table , aka many-to-many)
===================
EmpCalendarID_PK - Autonumber
EmployeeID_FK - Number Long
CalendarID_FK- Number Long

--------------------------------

I would create 2 queries and 4 forms:
Queries:
qryEmpNames
qryCalendarDates

Forms:
A main form ("frmEmpNames") with the record source of qryEmpNames
A sub form ("sfCalendarDates") with the record source of qryCalendarDates
Set the default view of each form to Continuous forms - Access will conplain when you add the sub form to the main form, but reset both to Continuous forms view.
Link the main form to the sub form.

Create another main form ("frmCalendarDates") with the record source of qryCalendarDates.
Create another sub form ("sfEmpNames") with the record source of qryEmpNames. Set the default view of each form to Continuous forms
Link the main form to the sub form.


And welcome to the forum.......

[Sandro]

Thanks Steve, I guess this is the right direction.
I'm having some difficulties because I've Office 2007 and something is different, and also because I'm a really a sub-first level expert.
Thanks
I'll let you know about it.
Thanks again.

Sandro

[ssanfu]

Doesn't matter what version of Access you have. This works in Access 97, 2000, 2002, 2003, 2007, 2010, 2013, 2016, a SQLServer back end, Oracle, and dB2.
Many to many is a database concept of how to relate two 1-to-many tables.

If you have an A2007 dB and are having problems/, post your dB.

[Sandro]

Thanks Steve, it works very well. Thank you very much.
What's the best way to prevent the possibility to insert a double identical name or data in the mask? To modify the tbl or what?
In exemple if I use the msk frmEmpNames and I have the name "Smith" I can insert more than one record with the same date, the same for reverse with frmCalendarDates.
Thank again
Sandro

[ssanfu]

In table "jctEmployeesCalendar ", create a compound INDEX.

Open the table in design view.
1) In the yellow "Table Tools" menu,
2) click on Indexes. You should have one index named "PrimaryKey"
3) Enter an index name (maybe "EmpCal"), and
4)in the next column, select the field "EmployeeID_FK"
5) In the next row, select "CalendarID_FK"
6)Click back on the index name "EmpCal" and in the index properties set UNIQUE to Yes.
Close the dialog pane.

[orange]

Sandro,

You may get some ideas from this free data model on StudentRoster.
It is more complex than your set up, but the Roster table is same concept.
There are several free generic data models at the site. They are intended as a starting point
you can add more, remove parts etc as you wish to meet your requirements.
Good luck.