Forms Subform field calculation (1 Viewer)

[lukekelly]

Hi all,

have never had to do anything like this before so im a little confused how im supposed to go about it.

Basically I have a form for a project (on table 1), with in the form i have a subform on a tab that has the financial information which is from original quote details (table 2) I have another subform that enables the user to record the work they do on that project each day (table 3) This all works fine.

What I have been asked to do is at the bottom of the 3rd subform, to total up the hours entered of that column (only the ones that are shown), and check that against the quoted hours in the 2nd subform and to format accordingly. I think my main problem is that table 2/subform 2 does not have an identifier that table 3/subform 3 does.

However table 1/main form has A) the project number and B) the quote number. Table 2/subform 2 has the quote number recorded, and Table 3/subform 3 has the project number recorded.

I think I may have confused you but if you understood any help appreciated

Thanks


Luke

 

[jzwp22]

Can you provide the table structure of the 3 tables involved? I assume that table 2 is related to table 1 in some way and that table 3 is related to table 1 as well, correct?

 

[lukekelly]

Hi jzwp22,

There are no 'relationships' as such but they are related in the sense that the hold information to query and report on. If I explain the dataflow that may help.

A user creates a quote (table 2/subform2), once that quote is 'awarded' the quote details are copied to the Project table (table 1/main form), including the original quote number.

The project form (table 1/main form) has the financial details of the original quote in a subform (subform 2) from table 2 (quotes table) linked by the quote number. This is there so the user can see the original quoted time estimate (a key field here for example is "CADHours")

Another subform is created for the user to enter in the time they worked on that project, records to be stored in a different table "timesheets" (table 3/subform3).

When the user enters in information into the timesheets subform (subform 3) 5 key pieces of info are captured, the project number (taken from the project table (table1)) their name, the date, their position and most importantly the hours they have worked on that particular session. This is on a continues form so additional works time can be recorded.

So what initially I would like to do is total up the hours they have worked on the project. I have a field in the footer of the form ready to do this, but is must only calculate the hours for that project.

What I would secondly like to do is for it to check the reported total against the quoted total and format to say red if it is greater.

Hope that makes it slightly clearer? Here are the key fields involved (i think)

Table 1/Main Form:
- Project Number

Table 2/Subform 2 (Quote Details)
- CADHours
- EngHours
- QuoteID

Table 3/Subform 3 (Timesheet)
- HoursSpent
- Project Number
- StaffType - I have this as there are two different recordings going on, for the CAD work, and for the Engineering work - I am doing CAD first so I can implement the same to the Engineering subform (not yet created).

Thanks

Luke

 

[vbaInet]

CONDITIONAL FORMATTING is what you need. Have a look through the form's menus for that. For Access 2007 and above it would be under the DESIGN tab, in the FONT group.

This links might help:

http://www.access.qbuilt.com/html/conditional_formatting.html
http://www.databasedev.co.uk/conditional-formatting.html

 

[lukekelly]

Hi vbaInet,

yes im fine with the actual formatting part, its just the totalling part thats driving me nuts! tried a query but done something wrong there, as im getting #name? - Ill keep persevering

Thanks

Luke

 

[jzwp22]

Let's focus on your tables first and then we'll get back to the forms. Without the correct table structure you will struggle with everything in the future.

tblProject (equivalent to your table 1)
-pkProjectID primary key, autonumber
-txtProjectNumber
-txtProjectName
-fkQuoteID foreign key to your quote table when the project is awarded

With referencing the quote ID there is no need to copy anything else; you now have a logical join between the quote and the project

tblQuote (basically the header info for the quote)
-pkQuoteID primary key, autonumber
-fkCustomerID foreign key to a customer table
-dteQuote (quote date)

tblQuoteDetails
-pkQuoteDetailID primary key, autonumber
-fkQuoteID foreign key to tblQuote
-txtQuoteItem (if quote items can be used between many quotes then it would best to set up a table to hold the items and then just reference the primary key of that table as a foreign key here)
-currItemCost
-Quantity

tblProjectTime (equivalent to your Table 3)
-pkProjTimeID primary key, autonumber
-fkProjectID foreign key to tblProjects (this creates the logical join)
-dteWorked (date worked)
-HoursSpent
-fkStaffTypeID foreign key to tblStaffType
-fkEmployeeID foreign key to your employee table

If an employee is categorized by the job they do (either CAD or Engineering in your case) there would be no need for the fkStaffTypeID in the project time table, but you would need to capture the role of the person in the employee table.


tblStaffType (2 records in this table: CAD and Engineering)
-pkStaffTypeID primary key, autonumber
-txtStaffType

Now as to your forms, your main form would be still be based on the project table. The subform for the quote would be bound to the quote detail table via the fkQuoteID fields. The timesheet subform would be joined to the main form via the pkProjectID--fkProjectID relationship. Now as to the employee's total hours, I assume that multiple employees will be working on the project and all will enter their hours via the same form. The only thing I can think of is to have a calculated control within the subform that activates when the person selects their name from a combo box because you need to know which employee is entering information in order to display their particular hours on the project and not the total hours. Having a control in the footer would not allow you to identify the specific person but rather the total for the entire project which could then be compared to the total in the quote. I guess we need to understand more about how the quote items relate to the project time items.