Monthly Query Help

[mitchk]

struggling to get my head round this but I'm trying to get 2 separate counts in one query

I have a table with each record (Repaired Instrument in this case) has a DateIn and a DateOut

the query is being used for a chart which gives a realtime view of the total input and output of Repairs for the current month

would appreciate any help please

 

[arnelgp]

what are you trying to to count, Total In against total Out?
just a guess:

select Count(*) As TotalIn , Count(IIF(IsNull([DateOut]),Null, 1)) As TotalOut From yourTable.

 

[GPGeorge]

You probably need to add a GROUP BY clause to that SQL to get the counts for incoming repair orders for the current month. Something like this.

WHERE Month([DateIn]) = Month(Date()) AND Year([DateIn]) = Year(Date())

And, for completed repair orders for the current month. Something like this.

WHERE Month([DateOut]) = Month(Date()) AND Year([DateOut]) = Year(Date())

Sometimes, though, it's easier to offer more appropriate suggestions if we can get sample data and the actual fields in the table to work with.

Also, I think it's going to matter that repairs can come in during one month and go out in a later month. Real sample data can help sort it all out for you.

 

[Pat Hartman]

I'm trying to get 2 separate counts in one query

Therein lies the problem. SQL is set based. A query is a set of records so by itself, it can't count multiple different sets. Sometimes, you can use IIf's to separate values and use Sum(), but NEVER Count(). Count() counts the rows in the set which is all the rows returned by the query. Based on your problem statement, I cannot identify the two sets you want to count.

 

[mitchk]

sorry a few beers and I've missed all this

maybe I should have said I want to take all the records from the table and get a total of the DateIn and total of the DateOut for the current month.

is basically just a realtime tally of how well we are doing

 

[GPGeorge]

What did you try so far? @arnelgp offered a starting point. I offered additional suggestions. Did you apply either or both of them to your table? If so, how close were the results to what you need? How were they different?

All we really know about the table is that it has two fields in it: one for DateIn and one for DateOut.

Do you just want all of the jobs that were started during a month, i.e. those where Month([DateIn]) = Month(Date())? Does it matter if the DateOut is in the same month or not?

How about jobs that were completed during a month? Also simply the number of jobs completed regardless of when they were started, i.e. those where Month([DateOut]) = Month(Date())?

With something concrete to work with, it's easier for others to try to offer a concrete suggestion.

Thank you.

 

[Pat Hartman]

get a total of the DateIn and total of the DateOut for the current month

a total is a sum. Why would you sum dates? If you mean count the values that fall in the current month, that still makes no sense but it is doable.

Select IIf(Format(DateIn, "yyyymm") = Format(Date(), "yyyymm"), 1, 0) as CountDateIn, IIf(Format(DateOut, "yyyymm") = Format(Date(), "yyyymm"), 1, 0) as CountDateOut
From YourTable

Of course, if you use Date() in this expression, you can only get a full count for a month on the last day of that month after close of business, so, you probably want to run the query from a form where you specify the year/month you want the counts for.

 

[mitchk]

Hi Guys,

thanks for all advice and suggestions - I got it working using GPGeorge suggestions by creating two queries - one for the DateIn and one for the DateOut and used the results of these for the chart

thanks again - help much appreciated

mitch