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 Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Summarise per day from one table with relation to other table with dates

Status
Not open for further replies.

JITA

MIS
Sep 6, 2009
28
BE
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?

 
You seem to be missing some commas in the sql you posted???

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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top