Nested query not working (1 Viewer)

[JITA]

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

 

[sneuberg]

I think you need a comma after tblO.DateList and I don't think you can return two results in the same subquery. I suggest looking through the examples on this site to get started on this.

 

[arnelgp]

Csn you use union query

 

[JITA]

Hi Sneuberg
I tried your suggestion to enter the comma. The result was the same, same error.
I had already found the site you suggested and tried according to what I understood of these examples. Since I am not familiar with nested queries I probably did not understand the examples.

 

[Minty]

I think you should post up some sample data. So start with your actual fields and a set of data large enough to cover all eventualities.
Then show us what you are expecting to see as a result from that example data.

You can remove any personal information we just need enough to work with, sometime an excel sheet or two can be easiest or you can upload a stripped down database in zip format.

 

[JITA]

Minty this is a short example of my table

ClNr DateList
10 24-10-2017
12 24-10-2017
14 24-10-2017
18 24-10-2017
20 24-10-2017
10 25-10-2017
12 25-10-2017
16 25-10-2017
18 25-10-2017

Today (25-10-2017) ClNr 16 would be new on the list and ClNr 14 and 20 would be "solved"

 

[JITA]

arnelgp

I tried your suggestion.

Select
tblO.ClNr,
tblO.DateList
FROM tblO
WHERE(((tblO.DateList=Date() )))
UNION
Select
tblO.ClNr,
tblO.DateList
FROM tblO
WHERE(((tblO.DateList=Date()-1 )));

The result is not what I need
ClNr DateList
10 24-10-2017
10 25-10-2017
12 24-10-2017
12 25-10-2017
14 24-10-2017
16 25-10-2017
18 24-10-2017
18 25-10-2017
20 24-10-2017