Form with fields that reference multiple records

[Swatkins]

I'm not sure I'm going about this the right way, but here's the gist of what I'm working on:

I'm trying to track a certain activity (let's say number of calls made, although that's not the actual activity I'm tracking) and progress toward a goal related to that activity. Tracking the activity itself was easy enough to figure out (I've got a table with dates and times for starting and stopping and the amount of the activity completed in that time frame - i.e. number of calls made in that time), but figuring out how to compare against a goal has been more challenging.

I've got my database set up so that the user can select one of several different types of goals: having a goal of a certain number of calls made per day, or a certain amount of time spent making calls each day with the option of using the same daily call goal for every day of the week or to have a different goal for each day of the week. I store which mechanism the user has selected in a separate settings table.

The way I've been working this, I've got a table with fields for the Day of the Week (with a separate table listing 8 possible days: Monday through Sunday plus the eighth representing a daily constant), and two fields for the goal for that day: one that's the activity goal (number of calls made) and one that's the time goal (amount of time spent making calls), with the relevant field being used determined (in theory) by the selection saved in the settings table.

Right now I'm trying to design a form (or forms) for the user to enter the actual goals. My thought process was that I'd have a different form based on which goal setting the user had selected, and when the user goes to enter the goals the appropriate form is opened.

Where I'm having trouble is on the form for having a different goal for each day of the week, I wanted to have separate entry fields for each day of the week on one form, so that when a user enters a value in the form field for "Monday", the value is stored in the Goals table in a record where the Day is set to Monday and the Activity Goal field is the value the user entered, and the value entered into Tuesday is stored in a field where the Day is set to Tuesday, and so on. I recognize that initially these will all be null values because upon first entering the goals there are not yet separate records in the Goal table for each day of the week.

So far, when I've tried binding the form fields to the Activity Goal field in the table, entering a value into one means the same value is duplicated in all the other form fields, all linked to a single record.

So my question is: is there a way to do this? Or given what I've outlined I'm trying to do, is there a better way to achieve what I'm trying to accomplish? Frankly, I'm just stumped.

 

[AccessMSSQL]

Well, can you post your table structure? The table you are storing the user entries for their goals for each day will have to have a field store the day of the week as well.

I'm not sure how your form is set up - you say when a user enters a goal all the other fields are duplicated on the form? Sounds like all of your form fields are bound to the same field in the table perhaps??

 

[Swatkins]

Sure, here's the structure I've built (although none of this is set-in-stone yet as I'm developing this from the ground up, so I can make changes if needed):

Settings Table:
*SettingID (a numeric ID key)
*GoalSetting (the selected goal setting)
*Active (a Y/N where Y indicates the active setting; only one record in this table is allowed to have "Y" at any time, which I'm enforcing on the forms where the settings are entered)
There are also some other settings fields unrelated to this aspect of the database.

GoalTypes Table
*GoalSettingID
*GoalDescription
(This table stores the different types of "goal" that a user may select between, like "Daily Activity Goal", "Daily Number of Hours Goal", "Activity Goal by Day of Week", and so on). The GoalSettingID is linked to the GoalSetting on the Settings table.

Goals Table
*GoalID (An auto-numeric key)
*Weekday (this is where the day of the week would be stored, with possible values of Monday-Sunday or Daily; actually the values are numeric with a separate "Weekdays" table linking the number value to a textual Day of the Week)
*ActivityGoal (this is where the goal for that day of the week is stored, if the user is tracking goals by productivity on the activity, i.e. number of calls made)
*HourGoal (this is where the goal for that day of the week is stored if the user is tracking goals by amount of time spent on the activity)

Weekdays Table
*WeekdayID (a numeric key value, linked to the Goals Table)
*WeekdayDesc (the text name of the weekday, or "Daily")

So, at the outset, "Weekdays" has 8 records, with 1-7 being the named days of the week and 8 being "Daily". The Settings already have a default setting value stored. The GoalTypes table already lists the possible Goal setting options. But the Goals table is empty.

When setting up my entry form for the "daily activity goal" goal setting, I have 7 entry fields, labeled Monday-Sunday. Initially, I tied each to the "ActivityGoal" field on the Goals table, but doing that was when it duplicated the value across all the form fields (i.e. yes, they were bound to the same table field; which I intuited this would happen, but wasn't sure what the right approach was).

What I wanted to have happen is that when I enter the field labled "Monday" on the form, the value would go into a Record on the Goals table where the Weekday field was the ID for Monday (from the Weekdays table) and the ActivityGoal field was the value that the user entered.

At the same time... I have this nagging sense that there's a better way to do what I'm trying to do than the way I'm trying to do it...

 

[AccessMSSQL]

Yes. I see. You could accomplish what you want to do in many ways. One way is to have an unbound form with a save button. When the user saves the data you could then check the values of each undbound control for M-F and then programmatically save it back to the table. The problem is when a user opens the form you would also have to load the data dynamically if you wanted to display it. (kind of like a web application).

The other issue I see with your structure is that it assumes a user will only ever have one set of goals. You have GoalID and Weekday. What if the user stores different goals for each week? Are you storing the week number in the table also? Or do you care about the history of goals/weekays and assuming user is just going to overwrite whatever goal they have for that Monday or other day?

Your data structures are relationally correct and follow good normal forms but in your case you may want a table that has fields set up for M, T, W, Th, F, Sat, Sun, and your global goal field. You can probably use a crosstab query that displays the days of the week across as columns. Use the crosstab to create a temporary table for the user entering the data. When the user saves you will have to write VBA to save the values back into your relational table.

 

[AccessMSSQL]

I just thought of something else if you don't mind changing the design of your form. Have the user choose the day of the week from a drop down list and store it in your Weekday column. You would lose the pretty format of having the weekdays display across the screen - you would also have to have a continuous form and the user would have to go to the next record to add a goal for a different day.

 

[Swatkins]

Thanks for the response. I'd been going back and forth about whether I want to keep a running record of past goals or just overwrite with the current goal whenever the goals changed. There are, I think, theoretically uses for maintaining the historical data... so it might make sense to preserve that history, which I agree means adding in a separate column in my Goals table.

The drop-down box is pretty good idea, and helps tie the goal data back to a specific day of the week in a way that'll probably work pretty well.

Thanks for your thoughts and suggestions! I'll have to give this all a whirl and see if I can make it work.