Count of two Queries (Crosstab) (1 Viewer)

[Chrism2]

I have a problem which seems fairly simple, though the execution is beyond me.

I need to monitor the activity of my clients, based on records in two tables; tblCases and tblNotes. I only need to know how many "hits" each customer gets per month.

A new case would be a hit, as would be a note (a customer can have many cases and a case can contain many notes). Counting each ID from each table gives me the numbers I need. (As you'll see from the crosstabs in my db).


I simply need to add the totals for each company for every month. E.g: Customer 1 has 9 Case hits and 3 Notes hits in August - I just need another table to summarise this.

I also notice that the months are alphabetical rather than in date order.. can someone help me get that right?

Thanks for helping folks..

 

[the_net_2.0]

Chris,

Crosstabs groups data up to 3 fields at a time (i think) through the wizard. is this format the way you want your data to look like:

cust1|case1|numHits|month
cust1|case2|numhits|month

is that what you want it to look like? or do you want the months going across the top of the crosstab and the cust/case/numHits fields going down as records?

(I did not look at your database...)

 

[Chrism2]

Hi there,

No; I need a table/query that shows the number of IDs for each month in both tblCases and tblNotes added together.

In essence
cust1,((Jan),CountOfcases(3)+CountOfnotes(1))
cust1,((Feb),CountOfcases(1)+CountOfnotes(2))
cust2,((Jan),CountOfcases(2)+CountOfnotes(3))

...I think!

 

[the_net_2.0]

In essence
cust1,((Jan),CountOfcases(3)+CountOfnotes(1))
cust1,((Feb),CountOfcases(1)+CountOfnotes(2))
cust2,((Jan),CountOfcases(2)+CountOfnotes(3))

chris,

I believe you can do this in one select query...

select cust, hitmonth, 

count(casehits) + count(notehits) as [Hits in Month]

from...

group by cust, hitmonth

if there is info coming from two table, there will be a join in the sql obviously. I'm almost certain you can do that sort of addition. if there is more than one year involved in the table though, you'll have to select another timeframe to assure that you get all of the records analyzed and not consolidated, such as year(hitdate) & month(hitdate) and then group by that as well:

group by cust, year(hitdate) & month(hitdate)

 

[vbaInet]

It will give undesirable results because of the OP's table structure. Cases has a one to many with Notes.

I've attached your db with one way of doing it. You will notice that the column headings are static and are sorted, plus the Year(Date()) condition.

 

[the_net_2.0]

It will give undesirable results because of the OP's table structure. Cases has a one to many with Notes.

very good. thank you for looking at his database. nice work.

 

[vbaInet]

very good. thank you for looking at his database. nice work.

The OP may never return to view it

 

[Chrism2]

The OP may never return to view it

Au contraire, vbaInet!

Thank you so much for the working example. (I'll just need to apply the theory to my proper DB, but I get the point).

Your example fits perfectly to what I'm trying to achieve - I did suspect a union query was involved... as soon as I saw that there was no "handholding-access-designer" for union queries, I ran a mile! I should be fine from here on in.

Thanks, all, for helping.

 

[vbaInet]

Ah!! He proved me wrong

Glad we could help.

 

[Chrism2]

Ah!! He proved me wrong

Glad we could help.

...and there was me thinking "now it'll be easy to make a nice line graph to show the activity by each company for each month".

Er, no.

Mainly because I've *never* had to use a chart in Access... until now.

If I want the months - Jan-Dec on the X Axis and the value of "hits" on the Y and a line to show the movement; what kind of chart is this? I played with PivotCharts, but it did not go well.

 

[vbaInet]

I don't really use the charts in Access but before you can use it, you need to create another query based on the crosstab. Include the Customer name and Jan to Dec fields in there and see if you can make a Pivot chart from it.

 

[Chrism2]

I elected to use a form with conditional formatting in the end. No amount of fiddling with charts got me far.

Cheers!

 

[vbaInet]

Good thing you found an alternative. Thanks for getting back.