I have searched through the threads here but could not find an answer to my problem. I have no experience with nesting queries and I think I need a nested query.
I have a table that is filled daily with around 600 records containing amongst others client numbers, balances and the date a record is entered. These records come from a legacy system.
I want to keep track of clients who are not in the list of records the previous day and are today (new), also clients that are no longer included in the list of records compared to the previous day (solved). [Clients can appear and disappear in the list multiple times during a year, Therefor I need the dates of (dis)appearing and keep these dates in two other tables tblNew and tblSolved]
When I create two small tables one containing records of today and the other containing records of yesterday I am able to get results. But nesting the queries in one query is a different story.
Relevant details:
tblO.ClNr and tblO.DateList
Query1 (new)
SELECT tblO.ClNr, tblO.DateList, tblPrevious.DateList
FROM tblO LEFT JOIN tblPrevious ON tblO.ClNr = tblPrevious.ClNr
WHERE (((tblPrevious.DateList) Is Null));
Query2(solved)
SELECT tblPrevious.ClNr, tblO.DateList, tblPrevious.DateList
FROM tblO RIGHT JOIN tblPrevious ON tblO.ClNr = tblPrevious.ClNr
WHERE (((tblO.DateList) Is Null));
Embedded to get clients who are new in the list
Select tblO.ClNr, tblO.DateList
(SELECT
Previous.ClNr,
Previous.DateList
FROM tblO As Previous
WHERE
(((Previous.DateList)=Date()-1)))
FROM tblO LEFT JOIN ON tbl.ClNr = Previous.ClNr
WHERE(((tblO.DateList=Date() AND Previous.DateList IsNull)));
When I try to run this query I get an error message Syntax error in the FROM clause. I can't find what I am doing wrong
I have a table that is filled daily with around 600 records containing amongst others client numbers, balances and the date a record is entered. These records come from a legacy system.
I want to keep track of clients who are not in the list of records the previous day and are today (new), also clients that are no longer included in the list of records compared to the previous day (solved). [Clients can appear and disappear in the list multiple times during a year, Therefor I need the dates of (dis)appearing and keep these dates in two other tables tblNew and tblSolved]
When I create two small tables one containing records of today and the other containing records of yesterday I am able to get results. But nesting the queries in one query is a different story.
Relevant details:
tblO.ClNr and tblO.DateList
Query1 (new)
SELECT tblO.ClNr, tblO.DateList, tblPrevious.DateList
FROM tblO LEFT JOIN tblPrevious ON tblO.ClNr = tblPrevious.ClNr
WHERE (((tblPrevious.DateList) Is Null));
Query2(solved)
SELECT tblPrevious.ClNr, tblO.DateList, tblPrevious.DateList
FROM tblO RIGHT JOIN tblPrevious ON tblO.ClNr = tblPrevious.ClNr
WHERE (((tblO.DateList) Is Null));
Embedded to get clients who are new in the list
Select tblO.ClNr, tblO.DateList
(SELECT
Previous.ClNr,
Previous.DateList
FROM tblO As Previous
WHERE
(((Previous.DateList)=Date()-1)))
FROM tblO LEFT JOIN ON tbl.ClNr = Previous.ClNr
WHERE(((tblO.DateList=Date() AND Previous.DateList IsNull)));
When I try to run this query I get an error message Syntax error in the FROM clause. I can't find what I am doing wrong