Apologies for the length of the post first .
This is actually spurring off another one of my threads. I cant find a solution to this problem, nor can I see what the problem is, its been bugging me for a bout 3 weeks on and off now, any help would be greatly appreciated.
I have three tables,
tblInvoice(InvoiceNo,InvoiceDate,Customer,MileageFee, CallOutFee,MOTFee)
tblParts(InvoiceNo,PartDescription,PartQTY,PartPrice)
tblLabour(InvoiceNo,LabourDescription,LabourCost)
InvoiceNo is Primary key in tblInvoice, and foreign key in the other two tables. A one to many relationship (because one invoice can have more than one part or labour cost)
I want to make a series of queries to calculate the sum of the Invoice overall, the process of this is:
qryPartsTotal
Grouping all the parts in tblParts that have the same InvoiceNo and summing them.
qryLabourTotal
Grouping all the parts in tblLabour that have the same InvoiceNo and summing them.
qrySubTotal
Adding the two outputs from the above two queries and also adding MileageFee and CallOutFee.
qryVATCalc
multiplies the SubTotal output by 0.175 to get the current VAT rate here in the UK.
qryInvoiceTotal
Adds the SubTotal and the VAT, and also adds the MOTFee(because this fee must not have VAT added to it). Outputs the final invoice total.
That’s basically what i want to do, and what I have done, using the above queries. But, after the grouping stages of the first 2 queries, if I use the outputs from them queries in another query, it tends to double up the outputs, and I end up with two of the same invoice number, hard to explain, must be harder to understand, ill attempt to show below. The test data im using is InvoiceNo 1 has £4.00 of parts and £3.00 of labour. InvoiceNo2 has £1.00 of parts and £1.00 of labour.
----------
qryPartsCalc
----------
PartsTotal InvoiceNo
£4.00 1
£1.00 2
----------
qryLabourCalc
----------
LabourTotal InvoiceNo
£3.00 1
£1.00 2
The results from the two above queries are correct.
-----------
qrySubTotal
-----------
InvoiceNo SubTotal PartsTotal LabourTotal
1 £2.00 £1.00 £1.00
1 £4.00 £1.00 £3.00
1 £5.00 £4.00 £1.00
1 £7.00 £4.00 £3.00
2 £2.00 £1.00 £1.00
2 £4.00 £1.00 £3.00
2 £5.00 £4.00 £1.00
2 £7.00 £4.00 £3.00
Please note that I have not put in the addition of the MileageFee or CallOutFee as not to make this more complicated than it needs to be at the moment.
This is the problem ive been getting, when you try to do anything with the outputted fields from the two base queries, it brings in the data as multiple records, of which I have now clue where they come from. There expected output is:
-----------
qrySubTotal
-----------
InvoiceNo SubTotal PartsTotal LabourTotal
1 £7.00 £4.00 £3.00
2 £2.00 £1.00 £1.00
To get round this, I joined qryPartsCalc and qryLabourCalc on the query design grid by InvoiceNo. This worked, and gave me the expected outputs. But, when I try to use the qrySubTotal in say qryVAT or qryInvoiceTotal; the outputs are split right up again, by the time I have got to InvoiceTotal and run the query, I end up with 1024 records being outputted, all with the InvoiceNo 1 or 2. Its ludicrous…
Any help you can give me will be gratefully received.
This is actually spurring off another one of my threads. I cant find a solution to this problem, nor can I see what the problem is, its been bugging me for a bout 3 weeks on and off now, any help would be greatly appreciated.
I have three tables,
tblInvoice(InvoiceNo,InvoiceDate,Customer,MileageFee, CallOutFee,MOTFee)
tblParts(InvoiceNo,PartDescription,PartQTY,PartPrice)
tblLabour(InvoiceNo,LabourDescription,LabourCost)
InvoiceNo is Primary key in tblInvoice, and foreign key in the other two tables. A one to many relationship (because one invoice can have more than one part or labour cost)
I want to make a series of queries to calculate the sum of the Invoice overall, the process of this is:
qryPartsTotal
Grouping all the parts in tblParts that have the same InvoiceNo and summing them.
qryLabourTotal
Grouping all the parts in tblLabour that have the same InvoiceNo and summing them.
qrySubTotal
Adding the two outputs from the above two queries and also adding MileageFee and CallOutFee.
qryVATCalc
multiplies the SubTotal output by 0.175 to get the current VAT rate here in the UK.
qryInvoiceTotal
Adds the SubTotal and the VAT, and also adds the MOTFee(because this fee must not have VAT added to it). Outputs the final invoice total.
That’s basically what i want to do, and what I have done, using the above queries. But, after the grouping stages of the first 2 queries, if I use the outputs from them queries in another query, it tends to double up the outputs, and I end up with two of the same invoice number, hard to explain, must be harder to understand, ill attempt to show below. The test data im using is InvoiceNo 1 has £4.00 of parts and £3.00 of labour. InvoiceNo2 has £1.00 of parts and £1.00 of labour.
----------
qryPartsCalc
----------
PartsTotal InvoiceNo
£4.00 1
£1.00 2
----------
qryLabourCalc
----------
LabourTotal InvoiceNo
£3.00 1
£1.00 2
The results from the two above queries are correct.
-----------
qrySubTotal
-----------
InvoiceNo SubTotal PartsTotal LabourTotal
1 £2.00 £1.00 £1.00
1 £4.00 £1.00 £3.00
1 £5.00 £4.00 £1.00
1 £7.00 £4.00 £3.00
2 £2.00 £1.00 £1.00
2 £4.00 £1.00 £3.00
2 £5.00 £4.00 £1.00
2 £7.00 £4.00 £3.00
Please note that I have not put in the addition of the MileageFee or CallOutFee as not to make this more complicated than it needs to be at the moment.
This is the problem ive been getting, when you try to do anything with the outputted fields from the two base queries, it brings in the data as multiple records, of which I have now clue where they come from. There expected output is:
-----------
qrySubTotal
-----------
InvoiceNo SubTotal PartsTotal LabourTotal
1 £7.00 £4.00 £3.00
2 £2.00 £1.00 £1.00
To get round this, I joined qryPartsCalc and qryLabourCalc on the query design grid by InvoiceNo. This worked, and gave me the expected outputs. But, when I try to use the qrySubTotal in say qryVAT or qryInvoiceTotal; the outputs are split right up again, by the time I have got to InvoiceTotal and run the query, I end up with 1024 records being outputted, all with the InvoiceNo 1 or 2. Its ludicrous…
Any help you can give me will be gratefully received.