Qry not working? Retrieving via last date?

[BCre8iv]

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&quot 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

 

[cheerio]

Create a new query that simply shows for each company the company ID /Account code or whatever uniquely identifies them and the last date a deposit was made using the max function.

Now join the output from the new query into your main working query and you will only be looking at the last date for each customer.

Ken

 

[BCre8iv]

Hi,

Well the new query works fine, but as soon as I join it with the main query I end up with all records showing for the individual customer but the Date is the last date used. In other words, instead of having the different dates that the transactions actually happened they all have the last date in them.???? It seems that as soon as I bring in the BottlePaymentsSubformTable, which is were the previous, delivered, etc is located, I lose the grouping.
Tina

 

[cheerio]

I may be misunderstanding what you are trying to do. It has taken me a while as I needed to create a little test data.

What seemed to work for me was to take your original query;
Remove the max and sum functions;
Join orginal query as modified to a new query constructed as follows

SELECT SubFormTable.CustID, SubFormTable.BottleID, Max(SubFormTable.DateDepositMade) AS MaxOfDateDepositMade
FROM SubFormTable
GROUP BY SubFormTable.CustID, SubFormTable.BottleID;

(sorry I changed your table names slightly but you will see what I mean)
The join to the new query was on
bottlepaymentsID and CustID and to make it all work on
subformtable.datedepositmade= query.maxofdatedepositmade

Basically, what I was trying to do was separate the agregate query from the main data query.

If this does not work for you I think I would need to see more test data to understand what you are doing.

On a completely different point, try looking at the Access Nz function as it will deal more simply with null values than your own solution. For example Nz([Previous]) will evaluate as a zero whenever [Previous] is null - a much simpler construction than your IIF.

Ken