Include "blank" child records.

[Homegrownandy]

Every "job" my company does has potential for "additional works".

These are works not outlined at the start of the project and they require us to bill for additional costs.

For example if we are working on a panel for a power plant, It needs to have an isolator switch near by. If its not there we install it then bill for it. The billing side of things for this is working perfectly.



When it comes to invoice I have a query that marks out which jobs have an approved manual. We write a Manuel for all the works we do for our client. When they are happy with this document we can start seeking payment. So for the query this is a good criteria.

the query also sees which jobs have not been invoiced yet.. This leaves us with just the ones we need to invoice.

so far so good, but now... Some jobs don't have any additional works at all... there will be no additional cost for them on top of the agreement. however, if they fall within my criteria, I still need them to appear on the report..

there will be no record for these in the "additional costs" table, so in this case I need them to appear with a £0.00 value on that document.

This is so our customer knows what they have/haven't paid for. They need to see that there is or isn't more costs also.

How would I achieve this? I have a list of the "job_IDs" in the query. I could do this manually but that's not reliable haha.

[Homegrownandy]

Sorry to add to the confusion; the query I described there just puts them on a form for a director to review and add the costs. Once this is done the form for that job/additional work has a checkbox changed to true. meaning its approved and ready for billing.

Perhaps in the VBA for that checkbox we can have a statement that adds a blank record if none exist. Does that sound feasible?

[NTC]

well, in not really following your post easily all I can advise is: with a left join (arrow join line) in a query one can force the fields of the table where there is no record to be part of the join and they will be blank. Using a calculated field in a query one could create an iif where there is IsNull(FieldName),$0.00 ...... in order to generate the $0 that is seems you seek...

that is all query oriented; as far as report objects go - if there is no record in a sub report then it will not display. so that's that..... It strikes me as unwise to generate a record that has all blanks (except the mandatory key / foreign key).....

[HiTechCoach]

well, in not really following your post easily all I can advise is: with a left join (arrow join line) in a query one can force the fields of the table where there is no record to be part of the join and they will be blank. Using a calculated field in a query one could create an iif where there is IsNull(FieldName),$0.00 ...... in order to generate the $0 that is seems you seek...

that is all query oriented; as far as report objects go - if there is no record in a sub report then it will not display. so that's that..... It strikes me as unwise to generate a record that has all blanks (except the mandatory key / foreign key).....

I agree.

Ther eis not need to create blank records. This can easily be handled within the query.

[Homegrownandy]

thanks guys, I cant work on this today as the machine with access is not available. but ill refer to this when I can.

[HiTechCoach]

thanks guys, I cant work on this today as the machine with access is not available. but ill refer to this when I can.

You;re welcome. Glad we could assist.

We will be here if you need further assistance