Subtraction with a twist (1 Viewer)

Please help me if you can.

I have two separate queries that give the following results.


QueryOne
EventCode, EventWkCode, SumOfwkHours
23062, PP, 89.5
23062, B, 141
23062, F, 47.5


QueryTwo
EventCode, EventWkCode, SumOfwkHours
23062, F_UST, 5

I need to subtract QueryTwo from from QueryOne. The number needs to subtract from Item reported as EventWkCode F in QueryOne. It would mean that 47.5 - 5 would equal 42.5.
The final results I would like to achieve is a report with the following

EventCode EventWkCode SumOfwkHours
23062, PP, 89.5
23062, B, 141
23062, F, 42.5
23062, F_Ust, 5

I can use a union query to put the two items together but in the record set I am pulling this information from F and F_UST are the same. QueryTwo separate them using different criteria and adds the F_UST to EventWkCode. If I don’t subtract this amount then my total would be off by 5 hours. I have racked my brain to figure this problem out but I am drawing a blank. Any help will be appreciated.

Obviously, the two codes (F and F_UST) are related to one another. Are there any other codes that are related, but different?

If this is the only case, you can use an expression for the sum in the first query that checks the code and if it starts with "F", lookups the related code from the other query and subtracts it from the sum. Something along these lines

IIF(left(EventWkCode,1)="F",SumOfwkHours-Dlookup("SumOfwkHours","query2","left(query2.EventWkCode,1)='F'"),SumOfwkHours)) as WkHrsSum

To get the output you want for the report, you will have to use a UNION query that brings together query 1 and query 2.

Of course if there are other codes that are related to one another, the above approach will not work.

Fairman:
First alias the hours fields so they have different names, query1: TotalHours and query2 ExtraHours. Then in query two, you need to pull the first letter of the EventWkCode so you can match up F with F_UST, so EWC: left(query2.EventWkCode,1)
Now in the third query, using 1 & 2, you draw lines between query1.EventCode and query2.EventCode and another line between query1.EventWkCode and query2.EWC. Then you right click on each line and select option two, all records from query 1 and only those from query 2 where they match. This is called a left join and you should get an arrow on each line pointing to query 2. Union queries are not needed.
Also in this third query you need to do some math. The formula is:
NetHours: IIf(IsNull([ExtraHours]),Query1.TotalHours,Query1.TotalHours-[ExtraHours]). And that should get you the results you want. Good luck.
Privateer

Thanks jzwp22 for getting me back on track. I was able to fashion another query that used subtraction on the codes. The application is used for billing services and so I was able to fix this subtraction of related items. I ended up with three queries all brought together in a union Query. Thank you also Privateer your solution was very slick but in the actual database application I am using two of the queries have calculated fields. They need to be delt with so using a union made the most sense to me. Thank you both for your help in this matter.

jzwp22 said:

Obviously, the two codes (F and F_UST) are related to one another. Are there any other codes that are related, but different?

If this is the only case, you can use an expression for the sum in the first query that checks the code and if it starts with "F", lookups the related code from the other query and subtracts it from the sum. Something along these lines

IIF(left(EventWkCode,1)="F",SumOfwkHours-Dlookup("SumOfwkHours","query2","left(query2.EventWkCode,1)='F'"),SumOfwkHours)) as WkHrsSum

To get the output you want for the report, you will have to use a UNION query that brings together query 1 and query 2.

Of course if there are other codes that are related to one another, the above approach will not work.

Click to expand...