Invoice Query not working properly

[Rushman21]

This will be hard to explain. I have created my first database and ready to roll it out but stuck on a couple of things. I want to create an invoice report with the total after the Workorder is completed. But my invoice query works only if there is 1 entry in each subform. (I uploaded a stripped down version of my DB). But if I add, lets say another user who worked on the same workorder, my query does not calculate the total cost properly. Also, the query does not come up with results until at least one entry in each subform. Sometimes, they didn't use Equipment or Materials. (You will see what I mean if you look at my DB). So, to understand, open the DB, Drop down a code, select a name and working hours. Run the invoice query. Nothing. You need to add one item in the Equipment and one item in the Material before the query works. Then add 2 people or 2 equipment items to the same work order. My other issue is FIFO but I will deal with that later. This will make more sense when you run the DB.
Thank you for your time.

 

[Beetle]

Also, the query does not come up with results until at least one entry in each subform. Sometimes, they didn't use Equipment or Materials.

You likely need to use a Left join instead of an Inner join. In the query design view click on your relationship line(s), select Join Properties and change it to;

"Include ALL records from 'WorkOrders' and only those recods from 'Table X' where the joined fields are equal."

Table X being the appropriate child table (Equipment, Materials, etc.)

 

[Rushman21]

Thank you Beetle. That worked great. I entered one name and it appeared in the query. Thank you very much. One hurdle down, a few more to go.

 

[Rushman21]

I got this resolved. I was unaware of subreports until someone mentioned it in a different forum. So, I created 3 new queries (Emp_invoice…etc) I have the totals in each query grouped by SUM. I was able to add a subreport if each query with the results I wanted.