I have 2 tables:
Table1 is filled daily with information about clients from a legacy system.
Every day we determine which client is "new" in that table with a query that is comparing today's records with yesterday's records. The outcome is used in a form showing "new" records in the table. This form is used to enter a reason for why this client is now in the list. That reason is stored in a seperate table together with the date of the occurence.
It happens regurarely that the same client appears in the list multiple times and then drops of the list again. So the second table contains many occurences of a client with associated reason. Reasons can differ for the same client.
I am trying to make a query that shows the sum of "new appearances" per "reason".
The 2 tables are designed as follows:
tblO
ID Autonumber
Client Text
A Number
B Number
C Number
DateL Date
tblR
ID Autonumber
CNr Text
DateN Date
Reas Number
So far I have not been succesful in getting the data as I want.
My query so far is
SELECT
tblO.DateL
ROUND(SUM(tblO.A + tblO.B + tblO.C),2) as Ovs
COUNT(tblO.Client) as NumCl
tblR.Reas
FROM (tblO LEFT JOIN tblR ON tblO.CLIENT = tblR.CNr)
GROUP BY
tblO.DatumL, tblR.Reas, [tblR].[DateN]<=[tblO].[Datel], tblR.DateN;
This query sums up data but when a same reason occurs on different dates these records are shown individualy instead of aggregated
I have searched a lot on the internet but could not find a solution. What do I do wrong?
Table1 is filled daily with information about clients from a legacy system.
Every day we determine which client is "new" in that table with a query that is comparing today's records with yesterday's records. The outcome is used in a form showing "new" records in the table. This form is used to enter a reason for why this client is now in the list. That reason is stored in a seperate table together with the date of the occurence.
It happens regurarely that the same client appears in the list multiple times and then drops of the list again. So the second table contains many occurences of a client with associated reason. Reasons can differ for the same client.
I am trying to make a query that shows the sum of "new appearances" per "reason".
The 2 tables are designed as follows:
tblO
ID Autonumber
Client Text
A Number
B Number
C Number
DateL Date
tblR
ID Autonumber
CNr Text
DateN Date
Reas Number
So far I have not been succesful in getting the data as I want.
My query so far is
SELECT
tblO.DateL
ROUND(SUM(tblO.A + tblO.B + tblO.C),2) as Ovs
COUNT(tblO.Client) as NumCl
tblR.Reas
FROM (tblO LEFT JOIN tblR ON tblO.CLIENT = tblR.CNr)
GROUP BY
tblO.DatumL, tblR.Reas, [tblR].[DateN]<=[tblO].[Datel], tblR.DateN;
This query sums up data but when a same reason occurs on different dates these records are shown individualy instead of aggregated
I have searched a lot on the internet but could not find a solution. What do I do wrong?