Mike Magnant
New member
- Local time
- Today, 17:12
- Joined
- May 22, 2013
- Messages
- 6
Hello,
Thanks for reading this. First question is it possible to make a report so complex that Access can not handle all of the formulas?
Second and more important. We have a report based on this query:
SELECT Tasks.ID AS TaskNo, Customers.[Customer Number] AS CustomerNumber, Customers.Notes, Customers.Notes2, Customers.Company, Tasks.*, Projects.Origin AS Origin, Customers.CustomerName AS CustName, Projects.FSC, Projects.FSCPercent, Projects.Destination AS Destination, Projects.Category AS Category, Projects.EmployeeRate AS EmployeeRate, Projects.ProjectName AS ProjectName, Projects.CustomerRate, Users.FullName AS UserFullName, Customers.Terms, Projects.PO_Number
FROM Customers, Projects, Tasks, Users
WHERE ((([Projects]![CustomerID])=[Forms]![Demo]![cmbCustomerName] And ([Projects]![CustomerID])=[Customers]![ID]) AND (([Projects]![ID])=[Tasks]![ProjectID]) AND (([Tasks]![AssignedToUserID])=[Users]![ID]) AND (([Tasks]![Status])='10'));
in the detail section this formula works. =IIf([Tons]>0,([Tons]*[CustomerRate]),IIf([Yards]>0,([Yards]*[CustomerRate]),IIf([Hours]>0,([Hours]*[CustomerRate]),IIf([Gross]>0,(([Gross]-[Tare])/2000)*[CustomerRate],[CustomerRate]))))
This is an invoice and we need to be able to create a subtotal which adds the line in the detail section.
To multiply the subtotal by [FSCPercent] and then add those together along with sum([FSC]) to create a grand total.
There is another issue, but this one is the biggest. My effort lead to inaccurate and inconsistent results apparently depending upon the customer selected.
Any other details I can provide or a link to a Dropbox folder with the application. Mike
Thanks for reading this. First question is it possible to make a report so complex that Access can not handle all of the formulas?
Second and more important. We have a report based on this query:
SELECT Tasks.ID AS TaskNo, Customers.[Customer Number] AS CustomerNumber, Customers.Notes, Customers.Notes2, Customers.Company, Tasks.*, Projects.Origin AS Origin, Customers.CustomerName AS CustName, Projects.FSC, Projects.FSCPercent, Projects.Destination AS Destination, Projects.Category AS Category, Projects.EmployeeRate AS EmployeeRate, Projects.ProjectName AS ProjectName, Projects.CustomerRate, Users.FullName AS UserFullName, Customers.Terms, Projects.PO_Number
FROM Customers, Projects, Tasks, Users
WHERE ((([Projects]![CustomerID])=[Forms]![Demo]![cmbCustomerName] And ([Projects]![CustomerID])=[Customers]![ID]) AND (([Projects]![ID])=[Tasks]![ProjectID]) AND (([Tasks]![AssignedToUserID])=[Users]![ID]) AND (([Tasks]![Status])='10'));
in the detail section this formula works. =IIf([Tons]>0,([Tons]*[CustomerRate]),IIf([Yards]>0,([Yards]*[CustomerRate]),IIf([Hours]>0,([Hours]*[CustomerRate]),IIf([Gross]>0,(([Gross]-[Tare])/2000)*[CustomerRate],[CustomerRate]))))
This is an invoice and we need to be able to create a subtotal which adds the line in the detail section.
To multiply the subtotal by [FSCPercent] and then add those together along with sum([FSC]) to create a grand total.
There is another issue, but this one is the biggest. My effort lead to inaccurate and inconsistent results apparently depending upon the customer selected.
Any other details I can provide or a link to a Dropbox folder with the application. Mike