help with counting

M

Mike

Hey everyone, I have a small problem I've been trying to figure out how to
overcome. I'm an Incident Manager and have a few databases I use for
reporting purposes.

I work with call tracking systems and have to match up tickets from one
system to another - this is done. For a certain report I have, I need to
match up one tracking system's ticket(s) to events in another ticket (from
the other call tracking system)

Here is an example of the data I'm getting from my simple query (which is
wrong):
WFMTicket REMTicket REMOpen
WFMOpen
9999680576 NUM003438282 7/31/06 18:40 7/31/06 19:42
9999680576 NUM003438282 7/31/06 19:38 7/31/06 19:42
9999680806 NUM003438282 7/31/06 18:40 7/31/06 19:48
9999680806 NUM003438282 7/31/06 19:38 7/31/06 19:48

Now, the time stamps are event times from the REM system (REMOpen), and the
WFMOpen is when the WFMTicket was opened for that REM event.

as you NUM see, the REMOpen time stamps repeat for ever WFM ticket... the
data results I need should actually look like this:

WFMTicket REMTicket REMOpen
WFMOpen
9999680576 NUM003438282 7/31/06 18:40 7/31/06 19:42
9999680806 NUM003438282 7/31/06 19:38 7/31/06 19:42

So, what I figure I should do is have the REM data counted per ticket
numbers, so the count should look like this:
REMTicket Count
NUM003438264 1
NUM003438268 1
NUM003438275 1
NUM003438282 1
NUM003438282 2
NUM003438282 3
NUM003438282 4
NUM003438337 1
NUM003438337 2
NUM003438337 3
NUM003438337 4
NUM003438337 5
NUM003438337 6
NUM003438338 1
NUM003438338 2

My question is: How do I do this?
I've been looking high and low for answers on this, but to be honest, I'm
too ignorant about Access to formulate the right question. I know what I need
to do, but not how to do it. I know I would also have to count the WFM ticket
and assign a number to it, so I NUM match up the WFM ticket to the REM Event.

NUM anyone point me in the right direction? I have been trying to do this in
a Query, but I'm not sure that's even possible.

Thanks much,
Mike
 
J

Jeff Boyce

Mike

Take a look into the "Totals" queries. It sounds to me like you want to
GroupBy REMTicket and Count one of the fields.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
M

Mike

Yup, gave that a try, but it only gave me the total of the count, instead of
displaying each line it counted.
 
J

Jeff Boyce

Mike

If you include a "GroupBy" field (you did say you want a count of REMTicket
#s, right?), you should get a count per group.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
M

Mike

Well, not exactly. I believe the terminology I should be using is "to rank"
each entry and use it to link the WFM tickets to the REM Event.
 
J

Jeff Boyce

I'm not sure you and I mean the same thing by this term.

Did you originally show an example of

I can see that the NUM003438282 is repeated, and has "count" 1, 2, 3, and 4.
On what basis are you deciding which NUM003438282 is #1, which is #2, ...?

And if the above example is the only thing you want generated, I don't see
any difference among them (except the "ranking"). How will you use this
info?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
K

Ken Sheridan

Mike:

From the sample data you've given your query seems to be producing what is
known as the Cartesian Product of two tables. This is when each row in one
table is joined to each other row in the other table and results from the two
tables being included in the query but not joined, whether by a JOIN clause
or by a join criterion in a WHERE clause (strictly speaking they are joined
but by what is known as a CROSS JOIN). So the first question is what are the
columns in each table which relate the WFM tickets to REM tickets?

To return the cumulative counts per ticket you would first JOIN the tables
on the relevant columns, and within the query's SELECT clause include a
subquery on the REM table which counts the number of rows WHERE the REM
ticket maps to the current WFM ticket AND the REMOpen value is =< the current
REM ticket.

To advise you in detail how to implement this we'll need more details of
each of the tables, in particular the columns which map the tickets in one to
those in the other. The data you have given so far includes no columns which
can be used to join the tables as far as I can see.

Ken Sheridan
Stafford, England
 
Top