Recomendation for Database Relationship

Hi,

It's been a while since I last used Access.

I wanted to Assign Many data to a single Day, and Many Days to a Month.

Something like that:

Relationship.png

Form.png

When I created the form with a sub-form (Table 1 > Table 2, using "Day" as a Relationship), I was expecting that when I changed the month it would automatically update the "day" field. Instead, both forms don't seem to be connected at all.

I keep receiving "Error 3201: You cannot add or change a record because a related record is required in the table 1".

What am I forgeting?

Thank you, very much.

The way you have the relation set up. In table you can have records like

Jan | 1
Jan | 2
Jan | 3

In Table 2, you can have records like

2 | Data 1
2 | Data 2
3 | Data 3

You will be linking the Data element to a day of ANY month.

Also you should not be using Month, Day and Data as element names since these are reserved words and could get you in trouble.

The relationship you have outlined doesn't make sense, but if you can explain what it is you are trying to do, maybe we can come up with a set of tables and relationships that can support it.

You have just three data elements and you don't have a relationship to an outside table. This is a classic "flat file" and could be done in Excel. The data are organized perfectly for a pivot table. The pivot table will do the gathering of the dates for you.

It can also be done in Access. You would have two tables: One which you populate with dates, and another where you link a date-metric to the date. This makes the first table sort of redundant. Also it is a lot more complicated than it needs to be.

The attached worksheet has a mock up of the data and what a sample pivot table would look like.

The data is in an excel table. There are a lot of advantages to an Excel table, one of them being that they automatically copy down formats, data validations and formulas. I have three "helper columns" in the table. I added them just to show the kind of thing that can be done with Excel Tables and Pivot tables.

Here is information on Excel Tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

I made two pivot tables:

One sorts out the measurements by Morning, Mid Day and Night. This pivot table may not be of much value since if you take two measurements during the same time of day, they will be added together.

The other pivot table is more in line with what you want.

I put slicers against both tables - Do a web search on slicers. They are easy to implement and are very intuitive. I wanted you to be able to filter date based on Year and Month, so that's why I put those two helper columns in.

Enter a date in column A immediately below the table and that row becomes part of the table. The formats and formulas are copied down automatically.

After you enter data, it will not be reflected until you refresh the pivot tables. You can select Data -> Refresh All or you can right click on one of the pivot tables and select refresh. Since both pivot tables are built from the same data source, if you refresh one, the other table refreshes as well.

You can use this sheet as is. Delete my data (read the wiki on how to delete table rows) and fill in your own data. Since the data are in a table and the pivot tables are built off them, they will follow along. All you have to do is refresh.

Let me know if I can give you any further help or explanation.