weird form calculation

[pwaterma]

My boss came up with this wonderfull (sarcasm) idea for our statement forms.

right now we have a subform at the bottom of statement showing net sales and royalties due to our authors.

This is what it looks like:

SalesYear | Qtr | ISBN | Qty | Net Sales | Royalty Due
2006 1 ### 8 34.48 17.24
2006 2 ### 5 21.55 10.77
2006 3 ### 2 8.62 4.31

He wants another column to the right of Royalty Due to include Paid.

The thing is Payment for the Quarter is only made if the Royalty Due is more than $10.00 and if it is more than ten dollars it is paid the following quarter.

so in the example above the 17.24 for qtr 1 is not paid until Qtr 2 so he does not want the 17.24 in the Paid column to show until Qtr2, the 10.77 to show in the Paid Column for Qtr 3.

Also I have to determin whether that quarter more that 10.00 to show in the next Qtr. If not then it checks the next Qtr to see if it adds up to 10 dollars then shows paid in the following quarter after that.

My head hurts.

current Query is
SELECT tblCustomers.CustomerName, tblCustomers.ContactName, tblCustomers.Address, tblCustomers.City, tblCustomers.Region, tblCustomers.PostalCode, tblCustomers.Country, tblCustomers.SalesRepID, tblOrders.OrderID, tblOrders.OrderDate, tblOrders.ShipDate, tblCustInvoices.InvoiceNo, tblCustInvoices.CustomerID, tblCustInvoices.InvoiceDate, tblCustInvoices.DueDate, tblCustInvoices.InvoiceSubTotal, tblCustInvoices.Tax, tblCustInvoices.Adjustment, tblCustInvoices.AdjustmentReason, tblCustInvoices.InvoiceTotal, tblCustInvoices.Final, qryRptOrderProductSum.Description, qryRptOrderProductSum.QtyOrdered, qryRptOrderProductSum.SellBy, qryRptOrderProductSum.QtyPerUnit, qryRptOrderProductSum.Price, qryRptOrderProductSum.Charge, qryRptOrderProductSum.Status
FROM (tblCustomers INNER JOIN tblOrders ON tblCustomers.CustomerID = tblOrders.CustomerID) INNER JOIN (tblCustInvoices INNER JOIN qryRptOrderProductSum ON tblCustInvoices.OrderID = qryRptOrderProductSum.OrderID) ON tblOrders.OrderID = tblCustInvoices.OrderID
WHERE (((qryRptOrderProductSum.InvoiceNo)=[tblCustInvoices].[InvoiceNo] Or (qryRptOrderProductSum.InvoiceNo) Is Null));

any help or suggestions would be appreciated. and know I can't whack my boss!

Thanks
Paul

 

[pwaterma]

Try to clarify

Quarter 1 would look like this
SYear | Qtr | ISBN | Qty | NSales | RoyDue | Paid
2006 1 ### 8 34.48 17.24

Quarter 2 would look like this
SYear | Qtr | ISBN | Qty | NSales | RoyDue | Paid
2006 1 ### 8 34.48 17.24 17.24
2006 2 ### 5 21.55

Quater 3 would look like this
SYear | Qtr | ISBN | Qty | NSales | RoyDue | Paid
2006 1 ### 8 34.48 17.24 17.24
2006 2 ### 5 21.55 10.77 10.77
2006 3 ### 2 8.62 4.31

Quarter 4 would look like this
SYear | Qtr | ISBN | Qty | NSales | RoyDue | Paid
2006 1 ### 8 34.48 17.24 17.24
2006 2 ### 5 21.55 10.77 10.77
2006 3 ### 2 8.62 4.31
2006 4 ### 5 21.55 10.77 15.07

hope this makes sense

 

[lespaul]

Is there a table where the 'paid' amount is stored?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[pwaterma]

yes there will be, not sure where yet

 

[lespaul]

ok, once that table exists you should be able to get the quarter the payment was made and add the paid amount to the correct quarter in the report query.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi