Select fields from different table for generating report

[2624]

I have two tables; Abstract table and the Completion table. They submit a tentative proposed budget goes into the Abstract table after they completed the activity they submit actual budget which goes into the Completion table. I would like to generate a report that if Completion reports submitted use these budget numbers if not use Abstract table’s budget field. I have two identifier fields that tells me we have received Abstract or/and Completion reports. These fields are check boxes and included in the Abstract table; AbstractRecieved or CompletionReceived.

Budget fields are look like this
Abstract Table:
AbstractSPATotal (field)
AbstractCommCash (field)
AbstractInKindTotal (field)

Completion Table:
CompletionSPATotal (field)
CompletionCommCash (field)
CompletionInKindTotal (field)

If [CompletionRecived]= No then I should use Abstract Table’s budget fields. How should I check this for every single record?

 

[lameid]

If you have to have the Abstract record before it is complete and it won't ever be deleted or altered once complete, you could outer join from Abstract to Completion. Either use IIF function with your test field or NZ to take the value from Abstract if it is not in completion.

Alternately you would need to use a Union query. The first sub query would return the complete records. The seocond would return the abstract.

These are all query based solutions... I guess you could use the functions in my first paragraph in a report instead.