Access table links

[drluggo]

I have a database application where using VB 6.0 and Microsoft Access 2000. I have been asked to make modifications to the application by doing the following:

1)The Projects Table in the database has a field called ActualHours.

2)The customer wants to break down the ActualHours into several categories (Administrative, Sub-contractor, etc...).

I was thinking of creating a new table that has these breakdown categories and linking them to the key Field (Project_Name) in the Projects table. Then I want to make the ActualHours field equal the total of all of the hours in the subcategories in the connected table. When the display form is shown in the VB application, it is only to show the data in the ActualHours field

I think I can do this but I don't want to affect the thousands of records that are already in the database that were entered when the ActualHours were entered as a total. I am afraid that if I link the two tables the records that do not have the sub-categories will calculate out to zero when I perform the total

 

[930driver]

I would suggest that after you have created your child table for the sub-catagories of hours, that you run an append query on it, creating a default entry equal to the actual hours for all of your existing records. This should keep your totals correct.

Then from then on enter your new records using your new table as normal. "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[Blanch]

I agree with the creating of the child table to support 1 Project has many categories. When you run your append query to update the categories table make one the the categories "ORIGINAL" or "ALL" this will allow the customer to search out the records they want to fix themselves and insure proper data integrity as you roll your numbers from the categories table for a total.