query totals

[shutzy]

i would like to generate an apend query. the results must include

ClientDetailsID
Last Of OrderDate
LoyaltyPointsSum

i have tried this many times but just cant seem to get it.

i have included the relevant tables and my query that i have tried.

i want a balance of the loyaltypoints a client has and their last visit.

thanks for any help

 

[plog]

Other than using 'LAST' instead of 'MAX' your query looks good. In design view, under 'OrderDate' you should use MAX, not LAST; it doesn't do what you think it does. MAX will return the most recent OrderDate value which is what I believe you want.

Do that, and if you still have issues, post a specific record that is incorrect. Post what the data is and then what the data should be.

 

[shutzy]

thanks for that. it didnt work but i have uploaded the same database but with an additional query with what the client LoyaltyPoints total should be. i am guessing that they are all wrong. in particular ClientDetailsID 413,414,415 should be £8.38, £11.48, £16.15 respectively.

thanks

 

[plog]

Because both tblOrders and tblLoyaltyPoints do not contain unique ClientDetailsID, when you join them, you are essentially mutlipying the number of ClientDetailsID records in each. You need to create a sub-query which contains unique ClientDetailsID and then use that in place of one of those tables. I chose to use the tblOrders to create that unique list and to replace it in your main query.

So, use this sql to create a query named 'sub_Attempt':

SELECT tblOrders.ClientDetailsID, Max(tblOrders.OrderDate) AS LastOrderDate
FROM tblOrders
WHERE (((tblOrders.Status)=3))
GROUP BY tblOrders.ClientDetailsID;

Then in qryAttempt, replace tblOrders with sub_Attempt. This would be the new SQL for qryAttempt:

SELECT sub_Attempt.ClientDetailsID, Max(sub_Attempt.LastOrderDate) AS MaxOfLastOrderDate, Sum(tblLoyaltyPoints.LoyaltyPointsAdjustments) AS SumOfLoyaltyPointsAdjustments
FROM sub_Attempt INNER JOIN tblLoyaltyPoints ON sub_Attempt.ClientDetailsID = tblLoyaltyPoints.ClientDetailsID
GROUP BY sub_Attempt.ClientDetailsID;

 

[shutzy]

thanks thats great. is it possible to have the sub within the Attempt query. by this i mean to not have to have an additional query. i would like it all to be in one.(dont know if i am explaining this well). i like to put sql in vba behind a form. so i prefer to have it all in one nice tidy pile.

ive never done queries within queries before. is it just like vba (" ***** ") or is it a little more complex than that?

thanks

 

[plog]

Technically you have to have sub-queries to achieve what you want, however you do not have to do it like I have done it. You can create one SQL statement that would include the sub-query. It would look something like this:

SELECT Department, COUNT(EmpID) AS UniqueEmployees), SUM(EmpPay) AS TotalPay
FROM 
    (SELECT Department, EmpID, SUM(Compensation) AS EmpPay
    FROM EmployeeCompensation
    GROUP BY Department, EmpID
    )
GROUP BY Department;

However, its up to you to combine the two query objects I gave you into one SQL statement. You can use VBA to execute existing queries too--so that may be an easier way to achieve what you want.