Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Still having problems getting the records I need?!

Status
Not open for further replies.

BCre8iv

Programmer
May 21, 2002
28
CA
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
Tina

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top