Update query between multiple tables

[Xina5280]

I was wondering if I can use an update query for these tables

-tbl cornerstar tickets- columns are: phase, date, ticket, employee, equipment, rate, description, hours, loads, price

-tbl cornerstar invoices-columns are: phase, date, company, invoice #, item description, loads, tons, price

(I have a union query that combines the above 2 tables for phase, date and price. I have the price totaled through a report since I was not able to do it through the union query)

I have a main tbl Projects with columns Project, start date, end date, bid amount, current totals, last updated
(Just as an FYI I have 17 projects on there and I eventually would like to do this for each one)

What I would like to see happen is when new information is entered in tbl cornerstar invoices or cornerstar tickets for the tbl Projects to update Current totals and last updated(date) columns. Any recommendations for the best way to go about this? Im not so sure since I have the totals on a report and not a query cause I couldnt figure out how to do that. Thank you!!!!!!!!!

 

[Pat Hartman]

The problem with what you want to do is that it violates standard rules of normalization. That is why you are also finding it so dificult to do. No relational database worth its salt would provide a built in method to do this since the moment you update the table with the totals, the value can be incorrect.

Use a query to calculate the total as you need it for reporting.

 

[Xina5280]

Thank you so much Pat! I just read up on 'normilization' (why couldnt they put that in Access for Dummies ) and I think my problem may be design flaw but before I go changing things around I would like your opinion. So I want to keep my main [Projects] table the same with colums that summerize the project. But as for my other tables, Instead of having seperate tables for tickets and invoices for each project, I would have 1 table for tickets and 1 table for invoices. I would just need to add a column for which project it pertains to. From that point I can just run the reports and querys as nessessary sorting by project. Am I on track or did I just miss the point here? I would still like for it to be able to update the summary page automatically but if not I can just enter that in manually.

Thanks a ton!!!!!!!!

 

[Pat Hartman]

Remove the summary columns. They are not necessary. I don't have enough information to say whether combining the tables is correct or not. Although the combination sounds correct.