Hi guys
I have an Access 2007 application that I am looking after. It is basically a custom invoicing/Work order system and was originally based on one of the Access templates.
It has a table called "Workorder Parts" that contains a list of the parts that are attached to a Workorder/invoice. There is also a Payments table that contains a list of all of the payment against an invoice.
I have the following existing query that works fine but I want to create another version of it that has a bit of extra logic in it that is causing me some grief:
SELECT Customers.ContactFirstName, Customers.ContactLastName, Customers.BillingAddress, Customers.City, Customers.StateOrProvince, Customers.PostalCode, Customers.MobNumber, Customers.EmailAddress, Customers.Notes, Customers.CompanyName, Workorders.DateReceived, Workorders.WorkorderID, [Sum Of Payments Query].[Total Payments], Customers.CustomerID, Workorders.ProblemDescription, Workorders.DatePickedUp, Customers.CustomerID, [Parts Total]-[Total Payments] AS [Total Owing], Workorders.WONotes, Workorders.WorkOrderType, [Parts Totals by Workorder Money Owing].*
FROM Customers INNER JOIN ((Workorders LEFT JOIN [Sum Of Payments Query] ON Workorders.WorkorderID = [Sum Of Payments Query].WorkorderID) INNER JOIN [Parts Totals by Workorder Money Owing] ON Workorders.WorkorderID = [Parts Totals by Workorder Money Owing].WorkorderID) ON Customers.CustomerID = Workorders.CustomerID
WHERE ((([Sum Of Payments Query].[Total Payments])>="$0.00" And ([Sum Of Payments Query].[Total Payments])<[Parts Total]) AND (([Parts Total]-[Total Payments])>0) AND ((Workorders.Valid)=Yes))
ORDER BY Workorders.WorkorderID DESC;
This query is attached to a form that is automatically displayed each time the application is opened and shows a list of all of the Workorders that are in the system that have money owing...this is working well.
I have added new functionality to the application so that it can now keep tracking of stock levels etc. When the invoice is paid, the user clicks a few buttons and the Stock on Hand is reduced. When this is done, the field called "SOH_Updated_Date" corresponding record in the "Work Order parts" table is updated with the date that the SOH was reduced....this is all working well.
What I need to do is provide a list of all Workorders that are paid but have NOT yet had the SOH levels of the attached Workorder parts reduced ie: the "SOH_Updated_Date" is empty.....
I'm sure that there is a simple way to do this but I'm struggling to find it!
Any help you can provide is greatly appreciated.
Cheers
Greg
I have an Access 2007 application that I am looking after. It is basically a custom invoicing/Work order system and was originally based on one of the Access templates.
It has a table called "Workorder Parts" that contains a list of the parts that are attached to a Workorder/invoice. There is also a Payments table that contains a list of all of the payment against an invoice.
I have the following existing query that works fine but I want to create another version of it that has a bit of extra logic in it that is causing me some grief:
SELECT Customers.ContactFirstName, Customers.ContactLastName, Customers.BillingAddress, Customers.City, Customers.StateOrProvince, Customers.PostalCode, Customers.MobNumber, Customers.EmailAddress, Customers.Notes, Customers.CompanyName, Workorders.DateReceived, Workorders.WorkorderID, [Sum Of Payments Query].[Total Payments], Customers.CustomerID, Workorders.ProblemDescription, Workorders.DatePickedUp, Customers.CustomerID, [Parts Total]-[Total Payments] AS [Total Owing], Workorders.WONotes, Workorders.WorkOrderType, [Parts Totals by Workorder Money Owing].*
FROM Customers INNER JOIN ((Workorders LEFT JOIN [Sum Of Payments Query] ON Workorders.WorkorderID = [Sum Of Payments Query].WorkorderID) INNER JOIN [Parts Totals by Workorder Money Owing] ON Workorders.WorkorderID = [Parts Totals by Workorder Money Owing].WorkorderID) ON Customers.CustomerID = Workorders.CustomerID
WHERE ((([Sum Of Payments Query].[Total Payments])>="$0.00" And ([Sum Of Payments Query].[Total Payments])<[Parts Total]) AND (([Parts Total]-[Total Payments])>0) AND ((Workorders.Valid)=Yes))
ORDER BY Workorders.WorkorderID DESC;
This query is attached to a form that is automatically displayed each time the application is opened and shows a list of all of the Workorders that are in the system that have money owing...this is working well.
I have added new functionality to the application so that it can now keep tracking of stock levels etc. When the invoice is paid, the user clicks a few buttons and the Stock on Hand is reduced. When this is done, the field called "SOH_Updated_Date" corresponding record in the "Work Order parts" table is updated with the date that the SOH was reduced....this is all working well.
What I need to do is provide a list of all Workorders that are paid but have NOT yet had the SOH levels of the attached Workorder parts reduced ie: the "SOH_Updated_Date" is empty.....
I'm sure that there is a simple way to do this but I'm struggling to find it!
Any help you can provide is greatly appreciated.
Cheers
Greg
