Producing an Invoice

[whitebear]

Hi

I want to produce an invoice based on a number of tables.

The issue im having is that for each invoice there may be several "Order Details".

Order details consists of:
> Invoice Number
> Item Number
> Quantity

Each item corrosponding to each item number has a price.

Theefore how do I produce an invopice which calculates all of the "Order Details" corrosponding to that particular invoice number?

Hope that makes sense!

Thanks

WB

 

[rainman89]

so in essence you want to have a total section on your report that adds up all of the orderdetails prices?

 

[whitebear]

Yeah thats pretty much it. There will also be forms where the user will input "Labourer Costs" again linked thorugh the Invoice number.

 

[rainman89]

Have you created queries that pull all of the information that you are wanting?

 

[whitebear]

No, that is where I'm stuck

 

[rainman89]

Ok so you will need to add all the tables where there is an option for the invoice number and tie them all together to get every item for your invoice, then you will want to add in the extra tables, that contain rates/prices for the work.

We can total them up on the report so we don't need to worry about that until then

 

[whitebear]

So for my query i've added "Work Orders", "Order Details" (Which takes stock item prices from "stock Items" table), and "Job Detail" (Which takes employee ID from "Labourers" table, which in turn takes hourly rate from "Labourer rates" table). As they are both have a relationship with "Work Orders", the tables are all tied together.

Does this sound right?

 

[rainman89]

Yeah sounds like you are on the right track, if you put an invoice number in under invoice does it return all of the appropriate information?

 

[whitebear]

Do you mean in the query criteria field?

 

[rainman89]

Yes, sorry. That is what i mean

 

[whitebear]

no, however when i leave it blank, only one invoice appears over and over again??

 

[whitebear]

but i have resolved that using the totals button. Even so, just one tupple of data appears?

 

[rainman89]

Can you post up your database again.

 

[whitebear]

Here you go

Thanks

 

[rainman89]

Nothing is showing because you do not have any information in the job details table relating to invoice 2

On another note, you should not be storing calculated values in your table, such as total price, subtotal etc.. you can calculate this stuff on the fly in the queries

 

[whitebear]

ok, i've added data to the work orders table and removed the calculated values from my work order details. My query now brings up all the invoicez

 

[rainman89]

Even the ones where the invoice number is not equal to 2?

 

[whitebear]

no, when i leave the field blank. If i put 2 in there it brings up invoice 2, 3 brings up invoice 3 and so on...

 

[rainman89]

So that is what you want correct? Blank is normal. That will return all results no matter what

 

[whitebear]

Yeah thats correct. Now i need to create a report which will be the invoice. I need it to calculate all the different prices (job details and order details). Do i need to write any scripting in the query or is this all done via the report?