Invoice Query

[Analogkid]

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.

[June7]

There is a textbox bound to JobLocation that is sitting on the navigation subform Work Order tab behind the tab control. I think you want that textbox on the WorkOrderEmployee page of the tab control but it isn't really on there. Click on Equipment or Materials page and will see the JobLocation label sticking out behind subform.

One problem with the Invoice query is INNER joins. Need LEFT joins - 'show all records from WorkOrder_Main ...' The other problem is that of joining with 3 tables that have many related records for each work order. Fix the joins and then what does the query look like with data?

[Analogkid]

Thanks June7. Good catch on the Joblocation textbox. Ifixed that issue. I also joined the "Show all records fromWorkOrder_main". It all works fine until I add more than 1 employee, equipor more than 1 Material. The attachment shows the results when I add a secondemployee. It will total the equip and Material AGAIN which the grand total isnot accurate. I have a screen shot ofwhat happens when I add just a second employee. There must be some “sum”function or something I can run. Just don’t know how.
Thanks again.

[June7]

Exactly what I expected you would see because of the table relationships. Might have to do subreports to get the output correct.

[Analogkid]

Exactly what I expected you would see because of the table relationships. Might have to do subreports to get the output correct.

Is this not the proper way to do this database? I'm wondering if I need to restructure. This is the only way I could figure out how to the 3 different section. I’m hoping this is correct because I would hate to go live with this and a few months down the road, it gets all messed up due to the bad structure. This invoice issue and trying to get the inventory on a FIFO are the only things holding me back now. How do you do subreports?
Thanks!

[June7]

I think the db structure is okay, follows normalization. It is a balancing act between normalization and ease of data entry/output.

Create subforms/subreports with a subform/subreport container control. http://office.microsoft.com/en-us/ac...010209281.aspx

[Analogkid]

Thanks June7. I will give the subreport a try.

[Analogkid]

Create subforms/subreports with a subform/subreport container control. http://office.microsoft.com/en-us/ac...010209281.aspx

Hey June7! Thank you very much for the idea. This is my first database and have dealt with subforms but I didn’t know there were subreports. 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. This worked out perfect. Thanks again for your help. Now, I only have one more hurdle. FIFO inventory. But I will create a new thread for that.
Take care!