multiple counts

[legorli]

Hi,

I have a database that has 3 different date fields: received, initiated, and completed. Is there a way I can produce a report which has a count of all the requests received during a specified period, all the requests initiated during that specified period, and all the requests completed in that same period?

Thanks,

L

[legorli]

To clarify... I also have multiple people processing these requests so it also needs to be grouped by person as well (done by a subsummary rpt right now).

[Ugo DI LUCA]

Thanx for clarifying...

Yes it can be done !

How depends if you throw another 2 lines clarification

Relationships, brief file structure, keys !!!

[legorli]

Okay... I'll see if I can elaborate. I didn't create it but it's a fairly simple db.

It is just a single database with no relationships to other tables or anything. It just has a whole bunch of fields with information about the requests. As I mentioned before, the key fields being when a request was received, initiated, and completed. The person who processes the request is entered per record via radio buttons. Each record is identified by a field: "record number" which is an automatically entered serial number.

What I can do now is specify a date period (ie. a specific month) in a script step and then if I enter that date in the received field, I can count how many were received in that period, and how many out of those have been initiated or completed in that period. I've done that using simple calculation fields of count(initiated) and count(completed) and grouping the results according to the employee. Is this the right track? Or am I way off?

I'm having trouble figuring out how to count the requests that have been initiated or completed in this month that were received last month.

Any help would be greatly appreciated. Thanks!

L

[Ugo DI LUCA]

Not sure....

If 10 items were received in January, you mean you would have 10 records with 1 in the "received" field ?

Then, use a relationship (self relationship using "received field" for both sides of relationship).

Then Count(Selfjoin::"received") should give you all counts for January.

Well, even for Jan 2002,2003,2004...

Turn your script to return the Month and Year

"I'm having trouble figuring out how to count the requests that have been initiated or completed in this month that were received last month. "

This will be another calculation, but what are the fields ?

Case("received" = "initiated +1", 1, 0) :??

[legorli]

Hi,

Actually, it is the date that will be entered into the fields. I'm sorry, guess I still wasn't too clear. Because I've been looking at it for such a long time, I just seem to assume that other people will know what I'm talking about.

But I figured out a solution. It ain't pretty but it works. I just made calculation fields from an If statement (so it would have a value in it if it's during a specified period) and then counted those fields. That probably doesn't make much sense but I'm just happy I got it working. There's probaby a better way of doing it but this'll do for now.

Thanks Ugo!!

L

[Ugo DI LUCA]

legorli !

But what on hell are you talking about ? You're killing me !

God I wish I had a decoder for that one....for both your initial querry and your final solution.

[paulage77]

Ugo, your response made me laugh.

I think in general if you are in need of grouping, a self join is in order. Or in this case relationships in general.