query with max date and grouping

[tricman10]

I have some accounts and payments based on a user. In a date range I need to sum the balance for the user. My problem is that when a user has made more than one payment in the date range, I need to get the MAX date of that. It always returns the MAX date, not the specified date.
Example: If I have two payments, one was made on the 28th of February, and the other was made on 3rd of March, when I define the query to be less or equal to the first of March, it should return 28th of February, but because of grouping it returns 3rd of March.


This is my query:

SELECT Z1.korisnik_id, Korisnik.imePrezime, Z1.datumRacuna, Z1.uDatum, Z1.dug, Z1.sumUplata, (Z1.dug-Z1.sumUplata) AS Balance, Z1.zgrada_id
FROM (SELECT Z.korisnik_id, Z.sCena AS dug,SUM(UnosUplata.iznos) AS sumUplata,Z.datum AS datumRacuna, MAX(UnosUplata.datum) AS uDatum,Z.zgrada_id
FROM (
SELECT korisnik_id, datum, SUM(cena) AS sCena,zgrada_id
FROM (
SELECT korisnik_id,datum,cena, zgrada_id
FROM Racun
UNION
SELECT korisnik_id,datum,cena, zgrada_id
FROM RacunP
) AS a
GROUP BY korisnik_id,datum, zgrada_id
) AS Z
LEFT JOIN UnosUplata ON Z.korisnik_id = UnosUplata.korisnik_id
WHERE Z.zgrada_id=1
GROUP BY Z.korisnik_id, Z.datum, Z.sCena,Z.zgrada_id
)  AS Z1 INNER JOIN Korisnik ON Z1.korisnik_id = Korisnik.ID
WHERE (((Z1.uDatum)<"3/01/2018"))
ORDER BY Z1.korisnik_id;

[ranman256]

youd want a text box on the form to say, pick the month,
txtMonthDate = 2/1/2018

other text boxes to fill in the full month
txtSTartDate = txtMonthDate
txtEndDate = dateadd("d",-1,dateadd("m",1,txtStartDate))

the query can now use this a limiter, (this is example sql, use the query designer for the correct one)

select Max(Date), ClientID from table where [date] between forms!myform!txtStartDate and forms!myform!txtEndDate

[tricman10]

I have found the solution. My mistake was that I was putting the max date in the range.
This is the solution for the query:

SELECT  Z1.korisnik_id, Korisnik.imePrezime, Z1.datumRacuna,Z1.uDatum, Z1.dug, Z1.sumUplata, (Z1.dug-Z1.sumUplata) AS Balance, Z1.zgrada_idFROM(
SELECT Z.korisnik_id, Z.datum AS datumRacuna, Z.sCena AS dug, SUM(UnosUplata.iznos) as sumUplata,MAX(UnosUplata.datum) AS uDatum, Z.zgrada_id
FROM (
SELECT korisnik_id, datum, SUM(cena) AS sCena,zgrada_id
FROM (
SELECT korisnik_id,datum,cena, zgrada_id
FROM Racun
UNION
SELECT korisnik_id,datum,cena, zgrada_id
FROM RacunP
) AS a
GROUP BY korisnik_id,datum, zgrada_id
) AS Z
LEFT JOIN UnosUplata ON Z.korisnik_id = UnosUplata.korisnik_id
WHERE z.zgrada_id=1 AND UnosUplata.datum <= '2/25/2018'
GROUP BY Z.korisnik_id, Z.datum, Z.sCena,Z.zgrada_id
)AS Z1
INNER JOIN Korisnik ON Z1.korisnik_id = Korisnik.ID
WHERE Z1.datumRacuna >= '2/2/2018'
ORDER BY z1.korisnik_id