Issue counting records in a report

[WCL01]

Once again I call on the vast experience of all of you on techtips.

Here is my issue. I have a query that pulls in a number of fields some of them being date fields that are formated down to a 2 digit number representing the month.

exp. Format(needlx.date,"mm") AS Needlx_date, needlx.n_in, needlx.n_out, Format(ai_clien.entered,"mm") AS Intake_Date

This query also pulls in a begin date and end date from a form. I then have 3 text boxes that use this to get its value.

TxtMonth1
Exp. =MonthName((Month([Forms]![QueryDateSelection].[enddate]))-2,True)

This will returm the value of end date minus 2 months in the form of an abreviated month name.

So here is my question now. I need to be able to count a specific field ND_IN where monthname(Needlx_date, true) = TxtMonth1.text. I would think if there was a way to convert my Needlx_date to an abreviated month name in the query it might make it a little easier and be able to just say needlx_date = TxtMonth1.text.

If anybody needs any further information please let me know.

Thanks in advance.

 

[PWise]

put this in the qurey grid
ND_IN : month(needlx.date)=Month([Forms]![QueryDateSelection].[enddate]))

 

[WCL01]

Ok, not sure if I understand where your going with that statement Pwise. My query pulls in all the data I need and what I'm looking to get in the report is this.

Month Total
Apr 12
May 7
Jun 32

The total is the count of the N_IN field however I only want the count where the needlx.date field that the query pulls in is equal to the month on the left. The 3 months are dynamic based on the begin date that is chosen for this particular report. The query pulls in all records within a given date range. Each record contains the following fields.

ID, N_IN, N_OUT, Needlx_Date, Intake_Date

The count needs to be on the value of N_IN or N_OUT where the Needlx_Date = whatever the dynamic month is. I guess its more of a sum possibly but I also need to be able to do a distinct count on ID where the Intake_Date field is also = to the particular dynamic date that it is next to.

 

[PWise]

Can you provide some sample data and sample output

 

[WCL01]

Ok, I'm going to include the full sql statement that pulls in the data that I will post in here as well. I'm going to maske a few of the fields due to the data that is in them. I have no sample output really but I will show what I'm looking for in the output.

SELECT agency.descript1, program.descript, needlx.tc_id, Format(needlx.date,"mm") AS Needlx_date, needlx.n_in, needlx.n_out, Format(ai_clien.entered,"mm") AS Intake_Date
FROM ai_clien INNER JOIN (needlx INNER JOIN (program INNER JOIN agency ON program.agency_id = agency.agency) ON needlx.PROGRAM = program.prog_id) ON ai_clien.tc_id = needlx.tc_id
WHERE (((needlx.tc_id) Like "LK*" Or (needlx.tc_id) Like "RI*" Or (needlx.tc_id) Like "N8*" Or (needlx.tc_id) Like "FY*" Or (needlx.tc_id) Like "GO*" Or (needlx.tc_id) Like "HD*" Or (needlx.tc_id) Like "BF*" Or (needlx.tc_id) Like "KJ*" Or (needlx.tc_id) Like "JR*" Or (needlx.tc_id) Like "JX*" Or (needlx.tc_id) Like "TF*" Or (needlx.tc_id) Like "ZA*" Or (needlx.tc_id) Like "SA*" Or (needlx.tc_id) Like "MB*") AND ((needlx.date)>=(([Forms].[QueryDateSelection].[enddate])-150) And (needlx.date)<=([Forms].[QueryDateSelection].[enddate])) AND ((needlx.PROGRAM)<>"LKAAD"));

That is the sql statement that I use to get the data that I need. Next is a sample of the output.

descript1	descript	tc_id	Needlx_date	n_in	n_out	Intake_Date
Agency Name	Program	00025	10	        5       20          04
Agency Name	Program	15586	10	        5         50	    04
Agency Name	Program	22404	10	        10	  50	    11
Agency Name	Program	21066	10	        0	  20	    07
Agency Name	Program	22404	10	        0	  20	    11
Agency Name	Program	23613	10	        0	  10	    08
Agency Name	Program	22404	10	        0	  20	    11
Agency Name 	Program	15917	10	        0	   1	    09
Agency Name	Program	22404	10	        0	  25	    11
Agency Name 	Program	15850	10	        0	  10	    08
Agency Name	Program	17038	10	        0	  20	    12
Agency Name 	Program	15916	10	        0	  10	    09
Agency Name 	Program	15916	10	        0	  10	    09

Here is an example of what I'm looking for.

[b][u]Agency Name   Month  Total N_IN  Total N_OUT Total TC_ID[/u][/b]
AGency name
                     Apr      10         15         5
                     May      21         17         8
                     Jun      43         9          9

The Total N_IN is the total sum of all the numbers in that field for that given month where the Needlx_date = month colum. Same goes for the Total N_OUT. The only one that really needs a distinct count is the TC_ID colum due to the fact that a person can be seen more then one time in any given month and we only want to know how many distinct people were seen that month. The persons TC_ID never changes no matter how many times they come back.

Just as a note. The Needlx_Date and Intake_Date fields were originally full dates however I had the sql statement format them into a 2 digit month to make it easier to compare against the months.

I hope that helps and doesn't confuse things more.

 

[WCL01]

Well I managed to solve my own problem. What I did was place this in my where statement to give me the previos 3 full months prior to the selected end date.

(needlx.date)>=(Format(([forms].[QueryDateSelection].[enddate]-63),"mm") & "/01/" & Format(([forms].[QueryDateSelection].[enddate]-93),"yyyy")) And (needlx.date)<=([Forms].[QueryDateSelection].[enddate])

Now if there was just a way to thank myself for the post.