Looking for Access 2007 Help

[Saint Nick]

A friend and I are doing work for a client here in town for our senior project in college. We've been stuck at a point in our DB design where we are trying to take the hours they work in a bicycle shop and apply it towards their target goal (entered by the user). If you'll notice in the ERD, there is Goal-Current-Amount and Goal-Amount-Left. Both of those are NOT modified by the users, but should be calculated by Access based on a summation of DateWorked.Hours.

ss.jpg

I've attached a link to our ERD for viewing. Hopefully someone can help us out. Our project isn't due for about three weeks, but this is probably the hardest part for us right now and are trying to get it out of the way.

THANKS!

Jared

 

[KLin]

I see no link.

 

[Saint Nick]

Yep...fixed that...thanks

 

[KLin]

I wouldn't store calculated values in a table. I would calculate the values in a report, a form, a query, etc.

 

[Saint Nick]

I guess I would agree with you, but we've tried to get that to work, but when we try to sum DateWorked.Hours and place it into Goal.Current-Amount, it just shows up in Access as "#Name?"

Is it because we have no relationship between the two?

 

[KLin]

Do the hours worked need to fall between the start and end dates of the goal record?

 

[Saint Nick]

Originally posted by: KLin
Do the hours worked need to fall between the start and end dates of the goal record?

Yes, hours is calculated as the difference between Time In and Time Out. That functionality doesn't really work yet, either, but we're going to tackle that one last because it should be a little easier to do. We tried to do it, but couldn't make it work perfectly.

 

[KLin]

You basically need to design a query that sums the hours worked and references volunteer table and joins to the 2 child tables, where dateworked is between the goal start date and the goal end date.

You should then be able to do all calculations based off of that query, put it into a report that allows the user to see their target, and hours left to reach that target.

 

[Saint Nick]

Is there any way to have a query return the results of an individual ID in Access?

 

[KLin]

Originally posted by: NightDarker
Is there any way to have a query return the results of an individual ID in Access?

Of course. Add the volunteer id field to the result set of a query, and put in an id in the criteria section. Or put in "[Enter an ID]" w/o the quotes to make it prompt for an id.

 

[Saint Nick]

Man, I can't figure out what the hell I am doing wrong. It shouldn't be too hard -- this is like the most basic of queries.

I set the field in one of my forms to return the results of the summation of the hours, but it just returns #Name?.

 

[Saint Nick]

Query Design
Query Results

I think that is why the value in my form is returning "#Name?" as a result...it doesn't know what to pick from.

Come to think of it, I did a query where there was one lonesome result in my query, and it still said "Name?" in my form.

AUGH...

 

[KLin]

How do you want the form to work? More details you can provide would help us understand better.

 

[Saint Nick]

addeditvolunteer.jpg

The part of this form where it says target hours, hours achieved, hours remaining, and so on, is the part where the query should come into play. The "Hours Achieved" should be the total of hours (summation) from DateWorked.Hours according to a particular ID.