Query for entries if in a specified month

[Lleuadwen]

Firstly, let me apologise for this thread if a similar question has already been answered in a previous thread (I am new )

Secondly, I wonder if anyone has a solution to the following:

I need to return all records for clients regardless of the records' date if there have been any records entered in a specific month, say July, but if clients have no records dated in July then those clients would be omitted.

I really hope I have explained it well enough.....sorry if it is complicated!

 

[Brianwarnock]

This a 2 query job
Query 1 looks for the clients who have had an entry in the given month
kind of like
Select ClientID, Month(fldate) as MTH
from tablename
Where Month(fldate) = [entermonth]

note month(fldate) returns a number

Type of query

Then this is joined back to the table on clientID to pull all of the records.

Brian

 

[Lleuadwen]

Thanks for your reply...I will give it a whirl and let you know how I get on.

 

[Lleuadwen]

Sorry couldn't get it to work.....I am using dates as opposed to month numbers or text not sure if this matters. I'll try and explain further....

The activity table holds multiple records for each client spanning many months.

At the end of each month we are to supply cumulative printouts detailing all activities for each client we have worked with in any given month but it must include all their previous activities. If we haven't worked with a client, in say July, then they are not required to be included.

At the moment the report includes all the records for all the clients so I need some way to filter out the clients we didn't work with and only include those we did.

I tried making 1 query to pull out clients we had worked with in July and it returned 1 client with 2 dates, so 2 records.

I then used the this in another query where I pulled all the relating information I needed from the activity table linked to the 1st query and tried the 3 different joins but none worked I either had all the records for everyone or just the 2 in july for the 1 client.....confused??? I am!!

 

[Lleuadwen]

Done it yippeeee! Changed the join field and it worked.....I'm a numpty at times!! thanks so much for your help

 

[Brianwarnock]

I'm a numpty at times!! thanks so much for your help

Aren't we all, glad its working.

Brian

 

[boblarson]

Just a comment about this. The original solution was only based on month, which means, from what I can see, that you would also get the records for ANY YEAR based on the month selected. You might want to add a year limitation to it so you get only the year's records you are wanting.