Trying to get a field in a table to be populated by a field in a related sub-table. Help!

[sir savage]

Tidings! I'm sub-novice when it comes to Access but I can make a mean table! I can't make magic happen, though. So I'd like some help. I'm trying to get calculated data in a field in a sub table to autofill in a field in a related table. Here's the picture:



So all of the data in 'total hours' column is calculated by the variable inputs in the 'hours' fields. Once there, I want it to feed itself into the 'total hours' field on the main table. Is that possible?



Also - the 'total hours' field in the sub table won't calculate anything unless a value is placed in EVERY 'hours' field in the same sub table (e.g. 0). Is there a way to get around that? Thank you!

EDIT: Here's my sobering attempt at hacking together an equation:

[pbaldy]

Well, if we can back up, having the hours fields like that is not a good design. The concept is called normalization:

Fundamentals of Relational Database Design -- r937.com

Those should be records in a related table, not fields in this table. That all said, I don't think you can have a calculated field refer to a different table.

[sir savage]

Well, if we can back up, having the hours fields like that is not a good design. The concept is called normalization:

Fundamentals of Relational Database Design -- r937.com

Those should be records in a related table, not fields in this table. That all said, I don't think you can have a calculated field refer to a different table.

I see. What would you suggest? Thanks for the theory posting, by the way. I just need a place for people to track their hours and I want it all related to that same table. It's not pretty and it's not pretty effective but it's something. And just something.

I've encountered the issue with a calculated field before. So I created the field next to it 'Reflection Hours' so that I could get that one to feed into the 'total hours' on the main table. It was still additional work BUT all they had to do was tab one over and enter the number into the 'Reflection Hours' field and it would find its way into the 'total hours' field on the main table. That didn't work either. But I think it's because I don't know how. If the field is not a calculated one can I then get it to pull the data? And if so - how?

Also - any suggestion on the calculation?

Thank you!

[pbaldy]

The "correct" (normalized) design would be something like:

tblEvents
EventID (primary key, perhaps an autonumber)
Various other fields

tblHours
HoursID (primary key, perhaps an autonumber)
EventID (foreign key from tblEvents)
Hours
Remarks

If in your original design you had 5 of the hours fields filled out for an event, that would be 5 records in this new table. Don't get me wrong, you can do it the way you are, but you'll likely find yourself having to jump through hoops because Access isn't designed to work that way (that's a spreadsheet, not a database). The way around having to have a value in every field in your design is wrapping each in the Nz() function.

[sir savage]

The "correct" (normalized) design would be something like:

tblEvents
EventID (primary key, perhaps an autonumber)
Various other fields

tblHours
HoursID (primary key, perhaps an autonumber)
EventID (foreign key from tblEvents)
Hours
Remarks

If in your original design you had 5 of the hours fields filled out for an event, that would be 5 records in this new table. Don't get me wrong, you can do it the way you are, but you'll likely find yourself having to jump through hoops because Access isn't designed to work that way (that's a spreadsheet, not a database). The way around having to have a value in every field in your design is wrapping each in the Nz() function.

It is definitely a spreadsheet and not a database. I wanted some way to keep the hours tracked in the same place as the database and that's why I did it that way. If I had it encompass a number of records then I couldn't have them easily linked and I would essentially have to create a new databse just for tracking hours for each event instance. I don't want that.

Also - Access told me that I couldn't use the Nz() function in a calculated field. Terrific! I researched it and it's definitely good to know. I did, however, just set the default value to zero. So it calculates just fine. One problem solved.

[pbaldy]

If you want a spreadsheet, leave it in Excel.

You're not following the design, which is fundamental to a relational database. You don't need a database for each event. You can have any number of events in the events table, and any number of records for hours for each event in the hours table. Typically you'd use a form bound to the events table and a subform bound to the hours table. Master/child links keep them in sync with each other, so that as you change events in the main form, only the hours for that event are displayed in the subform.

[sir savage]

If you want a spreadsheet, leave it in Excel.

You're not following the design, which is fundamental to a relational database. You don't need a database for each event. You can have any number of events in the events table, and any number of records for hours for each event in the hours table. Typically you'd use a form bound to the events table and a subform bound to the hours table. Master/child links keep them in sync with each other, so that as you change events in the main form, only the hours for that event are displayed in the subform.

This...actually makes sense to me. Your explanation is pretty clear. I'm going to get to work on designing something like that. Shouldn't be too hard. Ha! Thanks, Paul!

[pbaldy]

Happy to help! I think you'll be happier in the long run, and be better positioned to use the power of Access.

Welcome to the site by the way!

[sir savage]

Happy to help! I think you'll be happier in the long run, and be better positioned to use the power of Access.

Welcome to the site by the way!

Thank you for the warm welcome! So I've created the form and subform and the record sources for both are appropriate BUT I can't enter multiple records of hours because it creates a new Hours ID while keeping the Event ID, to which the forms are related. What do you think I'm messing up?

Thank you for your continued assistance!

James

[pbaldy]

That sounds like what it should do. The hours ID is the key field for the hours table, so would be new for each record. The event ID would stay the same as the record displayed on the main form. Can you post the db here?

[sir savage]

That sounds like what it should do. The hours ID is the key field for the hours table, so would be new for each record. The event ID would stay the same as the record displayed on the main form. Can you post the db here?

I thought so, too. The problem is that there will be multiple hour entries for every event ID. We have to be pretty specific with what we're accounting for. I tried to post the db here but it's 1MB...so it exceeds the filesize allowable for the site. I did upload it, however. http://ul.to/g9s9266l

Thats the link but it may prove too tedious to download.

Anyway - I don't want to miss the moment to thank you again. You're a hero to us Access pre-adolescents. I've been wrangling with this db for in excess of a week and speaking with you has already broadened my db horizons. Much appreciated, sir!

[pbaldy]

If you compact/repair and then zip, it may be small enough to post here (I prefer not to download from unknown sites).

[sir savage]

If you compact/repair and then zip, it may be small enough to post here (I prefer not to download from unknown sites).

I understand completely!

Also - your suggestion worked (of course). Please see the attached. Database2.accdb

[pbaldy]

In the hours table, you've got Event ID indexed with no duplicates. Change that to duplicates ok and you should be good to go. Oh, you'll need to fix the relationships window too. Because of the index, it has a one-to-one relationship defined.

[sir savage]

In the hours table, you've got Event ID indexed with no duplicates. Change that to duplicates ok and you should be good to go. Oh, you'll need to fix the relationships window too. Because of the index, it has a one-to-one relationship defined.

Super easy. It's perfect and far more simple than the mess I had originally going. Sheesh! Thank you again, Paul!