Crosstab IIf function - Need query between dates (1 Viewer)

[Jakboi]

Hello,

I have this so far:

TRANSFORM Count(tblclientcontacts.ENTERED_BY) AS CountOfENTEREDBY
SELECT "Count" AS [ENTERED_BY+OFFICER1+OFFICER2+OFFICER3]
FROM tblclientcontacts
GROUP BY "Count"
PIVOT tblclientcontacts.ENTERED_BY;

However I need this to show data that is for a certain date. I have a field named CONTACT_DATE which has the date in this format: xx/xx/xxxx.

I have been messing with the above and havent had any luck trying to get the crosstab query to only query say from months 10,11,12 or even for month 10.

Anyone have any pointers for me or a direction I can look at.

I keep getting not part of a aggregate function error, or syntax error in my attempts.

Thanks.

 

[EMP]

You can filter the records with a Where Clause.

TRANSFORM ..........
SELECT ..........
FROM ..........
WHERE Month([CONTACT_DATE]) In (10,11,12)
GROUP BY ..........
PIVOT ..........;

^

 

[Jakboi]

Thanks. I was really close but thought I needed a date function of some sort. Geesh sometimes things are easier then they seem. I tried the Where Clause several times but had used it incorrectly.

+Rep

 

[Jakboi]

I just noticed my count isnt working how it should be, or how I would like it to be.

SELECT "Count" AS [ENTERED_BY+OFFICER1+OFFICER2+OFFICER3]

This is only counting field ENTERED_BY. I have tried different things today and no luck.

I have tried using a Count (*) and a few SUM options. I have also tried to redo this line.

Any pointers.

Thanks.

 

[Jakboi]

Hmmm looking around at posts here I am starting to think this isnt possible. Does anyone know if the only way would be to make 4 Crosstab queries and then join them in a 5th?

I guess that would work...just didnt know if there was a simplier way.

 

[CraigDolphin]

Can you post what the data in your original table looks like and what you want the output to look like?

 

[Jakboi]

Ok. Well first of all its all in 1 table still. I was going to split it up here shortly when I realized all the fields I needed. (Hope thats not a big issue)

I attached an image of what it looks like so far.

Basically in these fields:

Entered by
Officer1
Officer2
Officer3

Are employees who go on service and sales calls. Multiple people can go on these calls. Then they are enterend into a form. I need to know how many per month and per quarter. Also some of these calls are not credited. So if they do count I have a checkbox in the form named ELIGIBLE.

So I am trying to count all the contacts made by each person that are eligible for the 4thq or months 10,11,12 so I can make the query into a report.

Hope that makes sense. If you need anything more let me know.

 

[CraigDolphin]

Ok, so the problem you're having is that you do not have a normalized db design and, consequently, queries are a PITA for you. Classic case of, as the Doc_Man would say, pay me now or pay me later. You took a shortcut in setting up your table and are paying the price in query difficulties now. Sorry

You want to track entities like Calls, Locations, and People. That's three tables right there.

tCalls
CallID (autonumber; PK)
CallDate
CallTime
LocationID (FK)
CallCredited (Y/N)

tLocation
LocationID (autonumber; PK)
LocationAddress1 (Text)
LocationAddress2 (Text)
LocationCity (Text)
etc

tPeople
PeopleID (autonumber; pk)
PeopleFirstName
PeopleLastName
etc

You also need to track which people went on which call. There's another table. Let's call it tCallsPeople.

CallPeopleID (autonumber;pk)
CallsID (FK)
PeopleID (FK)

For every person who goes on a call, there's a record in tCallsPeople listing the callID and the PeopleID.

With this kind of structure, your queries would be a piece of cake. My advice is to rebuild your data structure and make your life easier in the long run.

As another example as to why your structure is a disaster waiting to happen, consider what happens when 4 or 5 'officers' go on a call. You would need to start adding fields to your table, changing forms, queries and/or code to reflect the need for a 4th and 5th Officer field. Is that the kind of future you want for yourself? Forever patching your db when your old assumptions (eg no more than 3 officers per call) break down?

With a properly normalized design, you don't need to do anything (except maybe add that person to the People table if they're new to the db).

Fix your design, then work on your queries. I promise it'll come a lot easier once you have your foundation right.

 

[Jakboi]

Ok. I just started it anyhow last week - Its replacing me doing it on Excel. I thought maybe I could just do that in a bit and things would work out. I wasnt sure what fields I needed to after I made the form and tested to make sure that I had everything I needed.

I will normalize it first since I believe I have all the fields I need now. I shall return. Thanks for the advice on the structure.

+rep