I have a rather complicated question so I hope I can ask it correctly. I have written an estimating program for a construction company. Due to the large amount of pricing changes I have also added a change order form to the program so here is basic tables for original project or as I call it Jobmaster
tbl 1= Jobmaster
tbl 2= Takeoff
tbl 3= Jobcase
tbl 1 is basically the jobname and bid information
tbl 2 is the description qty and location on drawings of individual items
tbl 3 is the breakdown of the item such as parts
To enable Change orders I duplicated the tables and form and called them all the same name except I added a CO to each so I have a COJobmaster, COtakeoff, and COjobcase
when they need to change an item I wrote an export query that copies the original data to the new tables and they modify the pricing. Then I wrote a query that looks back at the original item and the new item and in a report I do the math that subtracts the total of the original item from the new COItem which is easy for the customer to follow. All this works fine. The Problem is that the changed item or COItem may change again so I have written an append query to copy the item into the cotable and the customer can input a new CO number. This works fine. What I do not know how to do is to get the report to look at the COTakeoff table to see if the item number exists and then deduct the amount of the CO Item and ignore the original pricing since a credit for that has already been issued. I am thinking I should do an IIf but do not know how to tell the query to look for an existing item with a diffrent co number and change the report to deduct that amount. I hope I am being clear it is a little beyond my abilities and any insights would be appreciated.
tbl 1= Jobmaster
tbl 2= Takeoff
tbl 3= Jobcase
tbl 1 is basically the jobname and bid information
tbl 2 is the description qty and location on drawings of individual items
tbl 3 is the breakdown of the item such as parts
To enable Change orders I duplicated the tables and form and called them all the same name except I added a CO to each so I have a COJobmaster, COtakeoff, and COjobcase
when they need to change an item I wrote an export query that copies the original data to the new tables and they modify the pricing. Then I wrote a query that looks back at the original item and the new item and in a report I do the math that subtracts the total of the original item from the new COItem which is easy for the customer to follow. All this works fine. The Problem is that the changed item or COItem may change again so I have written an append query to copy the item into the cotable and the customer can input a new CO number. This works fine. What I do not know how to do is to get the report to look at the COTakeoff table to see if the item number exists and then deduct the amount of the CO Item and ignore the original pricing since a credit for that has already been issued. I am thinking I should do an IIf but do not know how to tell the query to look for an existing item with a diffrent co number and change the report to deduct that amount. I hope I am being clear it is a little beyond my abilities and any insights would be appreciated.
