join with count

[robinCris]

I got a query: select id, phone, email, fType, user, date, area, formName, unit, eName from (SELECT Send.identity as id, Send.phone, Send.email, Send.form_type as fType, Send.user, Send.date, Ezer.area, Ezer.formName, UserName.unit, UserName.eName FROM (Send LEFT JOIN Ezer ON Send.form_type = Ezer.form_type) LEFT JOIN UserName ON Send.user = UserName.user);
Got another table ("Receive") with the columns date, idintity, and formType
I need to get the first query but with another flag column. I need to check if in the receive table there is a row with the same identity and form_type with date between the sent date and dateAdd('m', 1, send.date)
I am going in circle and can't find the answer
Please advice

[aytee111]

Create a new query, join in the first query with receive table, join on identity and form_type, have as the criteria for the date : Between SentDate And DateAdd('m',1,SentDate)

[robinCris]

Create a new query, join in the first query with receive table, join on identity and form_type, have as the criteria for the date : Between SentDate And DateAdd('m',1,SentDate)

but when I do something like this....

SELECT distinct sendInfo.identity, sendInfo.Date, sendInfo.unit, sendInfo.User, sendInfo.manager, sendInfo.eName, sendInfo.phone, sendInfo.email, sendInfo.form_type, sendInfo.area, sendInfo.formName, qryReceive.form_type as qqq
FROM (SELECT Send.date, Send.identity, UserName.unit, UserName.User, UserName.manager, UserName.eName, Send.phone, Send.email, Send.form_type, Ezer.area, Ezer.formName
FROM Ezer RIGHT JOIN (UserName RIGHT JOIN Send ON UserName.user = Send.user) ON Ezer.form_type = Send.form_type) AS sendInfo LEFT JOIN
(SELECT DISTINCT Receive.form_type, Receive.identity, Receive.Date FROM Receive where Send.date=Between Send.dateAnd DateAdd('m',1,Send.date)) as qryReceive
ON (sendInfo.identity = qryReceive.identity) AND (sendInfo.form_type = qryReceive.form_type);

The Send.date is not recognize.
That was exactly my problem

Please advice

[aytee111]

Get the query working first, the one in your first post, without the receive table.

Make a second query, join in the receive table.

Repeat of instructions in post #2.