I am still having problems getting the records I need to display via last date entered which is DateDepositMade. I get the record I want however the OnSite field is a sum of all records for a particular company. This is not the result I need. I need the OnSite to show the last amount entered on the last date delivered. So the below doesn't really do the job.
SELECT Customers.CompanyName, BottlePaymentsTable.BottleDeposit, BottlePaymentsTable.AmountofDeposit, DMax("[DateDepositMade]","BottlePaymentsSubformTable"
AS DateDepositMade, Sum(IIf(IsNull([Previous]),0,[Previous])+IIf(IsNull([Delivered]),0,[Delivered])-IIf(IsNull([Returned]),0,[Returned])) AS [On Site]
FROM (Customers INNER JOIN BottlePaymentsTable ON Customers.CustomerID = BottlePaymentsTable.CustomerID) INNER JOIN BottlePaymentsSubformTable ON (BottlePaymentsTable.BottlePaymentsID = BottlePaymentsSubformTable.BottlePaymentsID) AND (Customers.CustomerID = BottlePaymentsSubformTable.CustomerID)
GROUP BY Customers.CompanyName, BottlePaymentsTable.BottleDeposit, BottlePaymentsTable.AmountofDeposit;
Now when I try what is below the OnSite amount is right but I can't get it to only display the last date entered. I am at a complete loss and have no idea where to go from here.
SELECT Customers.CompanyName, BottlePaymentsTable.BottleDeposit, BottlePaymentsTable.AmountofDeposit, Max(BottlePaymentsSubformTable.DateDepositMade) AS MaxOfDateDepositMade, BottlePaymentsSubformTable.Previous, BottlePaymentsSubformTable.Delivered, BottlePaymentsSubformTable.Returned, [Previous]+[Delivered]-[Returned] AS OnSite
FROM (Customers INNER JOIN BottlePaymentsTable ON Customers.CustomerID = BottlePaymentsTable.CustomerID) INNER JOIN BottlePaymentsSubformTable ON (BottlePaymentsTable.BottlePaymentsID = BottlePaymentsSubformTable.BottlePaymentsID) AND (Customers.CustomerID = BottlePaymentsSubformTable.CustomerID)
GROUP BY Customers.CompanyName, BottlePaymentsTable.BottleDeposit, BottlePaymentsTable.AmountofDeposit, BottlePaymentsSubformTable.Previous, BottlePaymentsSubformTable.Delivered, BottlePaymentsSubformTable.Returned;
What I need to happen is: On a form the user inputs the following:
CompanyName: ABC
DateDepositMade: 10 Jun 02
Previous: 2
Delivered: 2
Returned: 2
OnSite: Does necessary math: [Previous]+[Delivered]-[Returned]
User inputs again for same CompanyName:
CompanyName: ABC
DateDepositMade: 21 Jun 02
Previous: 2
Delivered: 3
Returned: 2
OnSite: Does necessary math: [Previous]+[Delivered]-[Returned] Note the OnSite is always current with the record no running sum needed.
This has been driving me around the bend for about a week now. Any help would be greatly appreciated. Thx in advance
Tina
SELECT Customers.CompanyName, BottlePaymentsTable.BottleDeposit, BottlePaymentsTable.AmountofDeposit, DMax("[DateDepositMade]","BottlePaymentsSubformTable"
FROM (Customers INNER JOIN BottlePaymentsTable ON Customers.CustomerID = BottlePaymentsTable.CustomerID) INNER JOIN BottlePaymentsSubformTable ON (BottlePaymentsTable.BottlePaymentsID = BottlePaymentsSubformTable.BottlePaymentsID) AND (Customers.CustomerID = BottlePaymentsSubformTable.CustomerID)
GROUP BY Customers.CompanyName, BottlePaymentsTable.BottleDeposit, BottlePaymentsTable.AmountofDeposit;
Now when I try what is below the OnSite amount is right but I can't get it to only display the last date entered. I am at a complete loss and have no idea where to go from here.
SELECT Customers.CompanyName, BottlePaymentsTable.BottleDeposit, BottlePaymentsTable.AmountofDeposit, Max(BottlePaymentsSubformTable.DateDepositMade) AS MaxOfDateDepositMade, BottlePaymentsSubformTable.Previous, BottlePaymentsSubformTable.Delivered, BottlePaymentsSubformTable.Returned, [Previous]+[Delivered]-[Returned] AS OnSite
FROM (Customers INNER JOIN BottlePaymentsTable ON Customers.CustomerID = BottlePaymentsTable.CustomerID) INNER JOIN BottlePaymentsSubformTable ON (BottlePaymentsTable.BottlePaymentsID = BottlePaymentsSubformTable.BottlePaymentsID) AND (Customers.CustomerID = BottlePaymentsSubformTable.CustomerID)
GROUP BY Customers.CompanyName, BottlePaymentsTable.BottleDeposit, BottlePaymentsTable.AmountofDeposit, BottlePaymentsSubformTable.Previous, BottlePaymentsSubformTable.Delivered, BottlePaymentsSubformTable.Returned;
What I need to happen is: On a form the user inputs the following:
CompanyName: ABC
DateDepositMade: 10 Jun 02
Previous: 2
Delivered: 2
Returned: 2
OnSite: Does necessary math: [Previous]+[Delivered]-[Returned]
User inputs again for same CompanyName:
CompanyName: ABC
DateDepositMade: 21 Jun 02
Previous: 2
Delivered: 3
Returned: 2
OnSite: Does necessary math: [Previous]+[Delivered]-[Returned] Note the OnSite is always current with the record no running sum needed.
This has been driving me around the bend for about a week now. Any help would be greatly appreciated. Thx in advance
Tina