Date based summary reporting

[adh123]

Apologies if this has been posted before, I have not managed to find it so far!

Creating an access database for use as a CRM system and need to start building reports into it.

I have a table named tblSpeak (2 key fields here are s_date and s_action) and need to create 2 reports initially:

1 - Contact made on a specific day - defaulted to today but can be changed before running the report.
tblSpeak has a column s_action which stores a numerical value from a combobox depending on the type of call a salesperson makes (numbered 1 to 14)
Need the report to show the total number of calls in a given day, along with a breakdown of how many fall under each category (to complicate it, some categories should be merged, e.g. Totals for "1", "2", "3 & 4", "5", "6 & 7"




2 - Contact made over a range of dates (typically 1 week)
This is the same as above only with totals for each day out of a range of selected dates, with a Grand Total at the bottom of the page


I have not delved into report writing at all in Access so far so any help is much appreciated. Once I have a framework to go on then hopefully the rest can fall in to place!

 

[GinaWhipp]

First, you're going to need a query to give you the results you want then you can create the report. I would take a look at Crosstab queries as that should give you the results you want in both cases.

 

[adh123]

Thanks Gina

I've got it working (to a point) now, and thanks to thread 64141 (sorry cant link!) it shows 0 values as well

Edit:

However, it has presented a new problem:

the subsequent query qrySpeakPerAction contains the date field and 14 types of action available (1 action per contact with a customer). eg, a salesman makes 50 calls in a day, therefore 50 actions expected.

Running the query results in a date line for each action type. i.e. rather than a date of "23/01/2015" appearing once with 14 actions, it appears 14 times with 1 count per line (rather than 14 seperate counts per line)

Changing the sort options does not improve this, I am wondering if I have gone wrong with the SQL code below?

TRANSFORM Nz(Sum(tblSpeak.s_action))+0 AS CountOfs_action
SELECT tblSpeak.s_date
FROM tblDrpSpeak INNER JOIN tblSpeak ON tblDrpSpeak.ID = tblSpeak.s_action
GROUP BY tblSpeak.s_date
ORDER BY tblSpeak.s_date
PIVOT tblDrpSpeak.ID;

The issue appears on the call reports run by the managers, for a given day may appear on 14 seperate pages which is not ideal!

 

[GinaWhipp]

Are you storing Date/Time or just Date?

 

[adh123]

tblSpeak has 2 columns for this:

s_date
s_time

if this is bad practice I can amend to be 1 single date & time field?

 

[GinaWhipp]

No, just trying to determine the 14 pages of the Report. Hmm, so it looks like it is the Report that is the problem. Do you have any *Group By* set in the Report itself?

 

[adh123]

The query has been updated to the below (noticed some odd results which was a result of including SUM.

TRANSFORM Nz(Count(tblSpeak.s_action))+0 AS CountOfs_action
SELECT tblSpeak.s_date
FROM tblDrpSpeak INNER JOIN tblSpeak ON tblDrpSpeak.ID = tblSpeak.s_action
GROUP BY tblSpeak.s_date
ORDER BY tblDrpSpeak.ID
PIVOT tblDrpSpeak.ID;

The 14 columns refer to the different action someone might record against an item. I.e a product development meeting might be recorded as a '1' in the table, whereas a meeting with the client could be a '6', a customer call out maybe a '12'.

Its odd as I loaded test data in for 25th December, of which the query shows 1 line for 25th December, with 5733 '11's' and 741 '12's'.
The next date is 2nd Jan. 5 differnent types recorded and it has pulled 5 different lines. (3 of these are the same category ('4')

All the query is looking at is tblSpeak data (s_date and s_action (s_action is counted)) and tblDrpSpeak (a dropdown list enabling the user to make sense of the numeric categories which when removed makes no difference to the way the query appears)

No group by is set so far on the query.

(Sorry, I understand now this should have been put in the query section as opposed to reports, if someone wants to move it then thanks in advance!)

 

[GinaWhipp]

No, not the query but the report... have you set any grouping or sorting in it?

 

[adh123]

No not that I am aware of

 

[GinaWhipp]

Hmm, open the Report in Design View and select the Group & Sort button in the Ribbon and see if anything is there.

 

[adh123]

Hi Gina

The report shows the same as the query. I think I have found the issue though!

looking at the query the s_date column for 25/12/2014 shows the date and when I click in the record it shows only the date.
If I go to the 02/01/2015 records it shows the date AND a timestamp (25/12 data was imported, 02/01/15 data was entered through forms). So I need to go back to the forms and amend the input data and I expect it will now work.

Thanks for your help

 

[adh123]

Yup, just run a test after amending the form which inputs the data and the query now runs as it should. I now remember this is one of the reasons for seperating out the date and time element when the table structure was designed.

Thanks again!

 

[GinaWhipp]

Glad you got it worked out!