Query is working but.....

[Lateral]

Hi guys

Newbie here.

I am using Access 2007 and have a database that has a table called "WorkOrders" that are basically an Invoice type of thing that contains some information such as when it was created, the ID of the Customer etc.

I also have a table called "payments" that contains a record of every payment that is created.

Here is the problem....

I have created a split form for the Workorders table and have it all working really nicely as it allows the user to see all of the 274 Workorders that are in the table and to double click to view the details of the actual Workorder..

I have added a text box to the form called "Total Payments". I have also added a query called "sum total payments" to the actual form query so that I can link the "Total Payment" text box to it......so far so good.

The problem is that it is only showing 116 Workorders in the lower half of the split form. I figured out that there 116 are the only Workorders that have actual payments against them. The other 158 Workorder don't have any payments so therefore there are not records (even $0.00) in the Payments table.

So, in summary, I want the total 274 Workorders to be displayed in the lower half of the split form regardless if they have payments or not....

I have spent a lot of time mucking around to try to figure this out but I give up and hopefully one of you guy can provide me with a simple solution.

Here is the SQL code for the query:

SELECT DISTINCTROW Payments.WorkorderID, Sum(Payments.PaymentAmount) AS [Total Payments]
FROM Payments
GROUP BY Payments.WorkorderID;


Thanks for your help.

Regards
Greg

 

[CJ_London]

you need to link it to your worksorder 'header' table with a left join. something like

SELECT WorksOrders.WorkorderID, Sum(Payments.PaymentAmount) AS [Total Payments]
FROM WorksOrders LEFT JOIN Payments ON WorksOrders.WorkorderID=Payments.WorkorderID
GROUP BY WorksOrders.WorkorderID

 

[Lateral]

Thanks CJ

I away for a few days and will try your suggestion when I return and will let you know how I go.

Regards
Greg

 

[Lateral]

Hi CJ,

I just came back and opened the form to implement your suggestion but I am now seeing the following SQL code:

SELECT Customers.ContactFirstName, Customers.ContactLastName, Customers.BillingAddress, Workorders.*, Customers.City, Customers.StateOrProvince, Customers.PostalCode, Customers.Country, Customers.MobNumber, Customers.EmailAddress, Customers.CompanyName, [Parts Totals by Workorder].*, [Sum Of Payments Query].[Total Payments]
FROM Customers INNER JOIN ((Workorders INNER JOIN [Parts Totals by Workorder] ON Workorders.WorkorderID = [Parts Totals by Workorder].WorkorderID) INNER JOIN [Sum Of Payments Query] ON Workorders.WorkorderID = [Sum Of Payments Query].WorkorderID) ON Customers.CustomerID = Workorders.CustomerID;

I have attached a few screen shots in order to provide you with more information. On screen shot Greg02.png, the red arrow is showing to 136 Workorder records being displayed as there are onloy 136 Workorder with records in the "Payments" table" where in fact, there are 289 total Workorders in the Workorder table.....

Thanks for any help you can provide.

Regards
Greg

 

[James Deckert]

right click your join line between workorders and sum of payments and change to left join.
then try again.

 

[Lateral]

fantastic!!!!!

I just did it and it work!!!!

Yippee!

Thanks mate!

Regards
Greg