Data Entry Form and Subform for a many-to-many relationship

[Varda]

MS Access 2010


Intermediate level of knowledge. Not a beginner, but not an expert.
VERY limited knowledge of SQL (assume none).



I'm developing a database to track all the types of items (issues) on a project. There are several different documents that will be printed - Weekly Status, To Do List, Monthly Status, etc.
Each issue can appear on multiple documents ("reports").
Each document ("report") can show multiple issues.

It's a many-to-many relationship. See image attached for the relationships diagram.





tblItems: Has all issues and pertinent information related to the issues (who it's assigned to, when it was initiated, due date, etc). ItemID - unique identifier.
tblReportType: Has the types of documents that will include each issue. ReportTypeID - unique identifier.
tblReportDates: Each Week can have multiple reports created that week. ReportDatesID - unique identifier.
tblReports_Items: the junction table that contains ItemID, ReportDatesID.

I'm trying to develop a data entry form that does the following:
1. In the main form (based on tblReportDates): The user will select a ReportTypeID, a StartDateRange, a FinishDateRange.
2. In the subform (based on....a query, I presume): The user will enter all the information about this Issue.
3. The ReportDatesID from tblReportDates will be saved into the junction table tblReports_Items.
4. The ItemID from tblAllItems will be saved into the junction table tblReports_Items.

HOW???

I've researched YouTube for a tutorial video and couldn't find anything relevant to my particular question. I'd appreciate any pointers on where to go check a solution for this problem.

Thank you in advance,

Varda

[June7]

Options:

1. one form with two comboboxes to select item and report date record

2. main form bound to tblReportDates and subform bound to tblReportsItems with combobox to select item

3. main form bound to tblAllItems and subform bound to tblReportsItems with combobox to select report date

With form/subform arrangement, set the Master/Child links properties of subform container and the PK of main form will automatically save as FK in subform record.

[Varda]

Thank you for your response.

I've considered all these options before posting and kept getting confused because of their inability to do what I want. Here are the issues I have with each option. Maybe you can help me figure out the ideal solution:

1. One form with two comboboxes requires a separate data entry in other forms for the Report Date and for the Item, in order for these to be available in the combo boxes. Cumbersome process for the user.
2. Main form bound to tblReportDates and subform with combo box would require that I enter the entire item separately, and then find it in the combo box among a LONG list of items (I'm expecting hundreds of items on this project).
3. Main form bound to tblAllItems, and subform bound to tblReports_Items: The cleanest option, but... the way the user thinks is - "First I enter the date of the Weekly Report and then I enter all the items for this Weekly Report." This option forces the user to select the ReportDate for every single item he enters. Not the most user-friendly form.

Is there any way I can create Option 4: Main form bound to tblReportDates and subform bound to tblItems, so the user first enters the Report Dates, and then enters each item for this Report Date? I was trying to figure out placing a "save" button that would perform the action of placing ItemID into tblReports_Items, and ReportDatesID into tblReports_Items. But I'm not sure if this is the most efficient way to resolve this?

Varda

[June7]

The more 'user friendly' the more code.

Items can be added to combobox list 'on-the-fly' during data entry. This is what the NotInList event is for. A list of hundreds of items is not unusual. The combobox AutoExpand property aids in finding item in list.

Your option 4 is not conventional but probably can be done - pretty much anything can be done with code.

Table relationships are set for many-to-many and this is what you indicated. But fields (ActionBy, LastUpdated, ResponsiblePerson) in tblAllItems leads me to believe each item can be associated with only one report date. Perhaps need to explain more about what 'items' are and more about this tracking process?

[Varda]

The fields you saw (ActionBy, LastUpdated, ResponsiblePerson) are relevant only for one type of report and are not going to appear on every report.

I need to go into more details when I explain what an 'item' is. I'm going to try and simplify it to daily activities and not relevant to my project, so don't take this as literal as it may seem.

So for example, an item is "Check up whether lunch is ready." An item can also be "develop standards and processes for creating sandwhiches." Or an item can be "The sandwhich making is being delayed and costing more than we planned. We are going to work on Plan B."

Some of the items will appear on a "TO DO list" - an immediate report that will be printed weekly and allow the manager to see who's responsible for each To Do item. (who's going to "check up if lunch is ready?")
Some of the items will appear on an "Issues Tracking" list - a long term report that will be reviewed weekly to see if it's nearing the due date and we need to resolve it once and for all. ("does anyone have time to develop the standards and processes for sandwhich making?"
Some of the items will appear on a "Leadership" report - items that leadership specifically wanted to hear about. ("we have a delay and it's costing us time and money. We're working on solutions.")

The main reason I had for going with the junction table was to be able to report in FUTURE on any items that appeared on a Leadership report on the Week of 11/1/2013-11/4/2013. Or if I want to show all items related to Sandwhiches and on which dates they appeared on which reports.


Because I'm getting increasingly confused on how to handle the above, I've decided to simplify my database a bit and reduce the many-to-many relationship to something much simpler, hoping it will allow me to still report on what I need to report.

I'll have tblItems with the fields Start Date and End Date. Each item can be identified with multiple Report types (Weekly, Monthly, Quarterly, or Leadership); and each item can be 'checked' as a "To Do", and/or "Issues Tracking."

My report will only filter for Active Items, and the Start Date will show how long the item has been 'active.' Subreports will identify To Do items, and Issues Tracking items.

I wish I had more time and patience to really figure out what I wanted to do to begin with, but this database was just meant to be a tool for managing a project, and is not my entire scope of work. I appreciate your time in reviewing this issue and trying to help me out.

I'd still like to know how to create a command button that inserts values into a table that is not the basis for the Form or Subform? I'm familiar with some basic Visual Basic, but not sure if I'd know how to handle "if this field is empty, ask for value." I'd likely end up with more error messages that would frustrate me.

Varda

[June7]

I still don't understand how one item can be associated with multiple reports because of the start and end dates. If you have a weekly report produced for March 8, 2015 and another report for Oct-Dec 2015 quarter, how can an item record with start/end dates of 1/1/2015-4/1/2015 be selected for association with both reports?

I don't understand the table structure for reports. If you want a quarterly report that displays items that occur within a particular quarter, then build a report that shows the tblAllItems records and apply filter criteria.

[Varda]

Yes, you're right, I saw the flawed thinking once I went back to the database and tinkered with it. So I still have a many-to-many relationship to allow for multiple reports per item, and multiple items per report. The simplification is in placing the start date and end date in tblItems, and then like you suggest - filter the report by the date to know which items would have appeared on each report at any given time in history.

Here's the final relationship I've ended up doing.