Formating and Summaries

[kentwoodjean]

There are some things that always seem to give me grief. I have a few questions and I am showing a sample of my data.

I have hundreds of pages of raw data that have been provided to me in a text file format that is received weekly. I import that into my database and below is an example of 1 query set up that I use as a basis for my report. There are about 40 Applications (Appl) that run each week, each with their own totals and internal measurements as demonstrated below. Here is what I need to do: Come up with a 1 line summary for each month (total offered, handled & abandoned, and Avg TSF & Seconds). When I tried to do it in my query design (sums & grouping) or even in the report wizard, I cannot seem to get my 1 line per month to work right. Can you please help help me?

Also, the date format in the raw data has a week from Mon thru Sunday, even though data is only accumulated Mon Thru Friday. To avoid confusion and be consistent in our reporting, is there any way I can have the date show, based on example below, date ending 1/3, 1/10, etc. In other words, knock off the Sat & Sunday and drop the year.


Week Endg Appl TSF offered handled abandoned Seconds
1/05/2003 20 72.3 65 64 1 52.4
1/05/2003 19 72.6 84 82 2 22.5
1/05/2003 42 75 8 8 0 76
1/05/2003 33 83.3 24 24 0 15.6
1/12/2003 20 0 0 0 0 0
1/12/2003 19 0 36 35 1 0
1/12/2003 42 33.3 6 6 0 62.7
1/12/2003 33 56.3 5506 5288 218 53.1

 

[randysmid]

Hi,
I wrote an FAQ on date field groupings. Here is the FAQ: faq703-2980

With this, your report will show the summary lines for each week, with no detail.

It seems that a formula can be created that will get your Friday date. I haven't experimented with it, but once you know what the date is that appears for the week, you can do a simple calculation to add or subtract the necessary days with a function. HTH,
Randy Smith
California Teachers Association

 

[kentwoodjean]

Thanks for your response, but unfortunately that did not work. Perhaps it has something to do with the query, where in the application section, I set the criteria as: in(20,19,42,33,52,67,89,45 .......etc) for a total of 22 appliations and their associated totals based on the entries above. So I have 1/5 listed 22 times with all other week ending dates doing the same. Grouping is set for the App as well as the "group on" for the date.

I am sure it is something somewhat dumb, but looking again for your thoughts.

As far as reformating the date to coincide with our other reporting, I would need to subtract 2 days. I tried to set this up the way I thought, but I am sure I probably havae my brackets etc in the wrong sport and will have to just keep playing with it.

 

[PaulBricker]

LastDayofWeek[DateField] - Weekday([DateField])) + 5

will give you the Friday date. You can copy this and paste it in a new column of your query. Just change DateField to the name of your date field (you don't need the Datefield in a column of your query to have this work) then you can Group On this field instead of your other date field.
As for summary, you say you import your data weekly but you want monthly summaries. Could you post a sample of what you want your summary to look like based on the sample info in your original post.

Paul

 

[kentwoodjean]

Paul,

Below is how I want it to appear as monthly, however I need to report in the same format for week ending, which is What I am working on now. The text format I am receiving comes to me 2 ways, by week totals and monthly totals per application # (Appl). I want to combine those application #'s belonging to each reporting department. Currently, I would show 16 Appl #'s as criteria in a query for 1 department, 18 Appl #'s as criteria for another depart. etc. Each department will need a report combining the totals of their dept. appl's for week ending and month ending in the format below. When I use the report wizard, I group by the date, then use the summary to "sum" the call numbers and "Avg" the TSF, %CA and ASA. The result is an error message (Overflow) and it will only let me go into design mode.

Month TSF TOTAL TOTAL TOTAL %CA ASA
>80% CALLS HANDLED ABAND <5% <30 SEC
Jan 41.4 32802 30321 2481 7.6 118.8
Feb 36.4 27124 24258 2866 10.6 159.3
Mar 33.2 27269 24448 2821 10.3 166.7
Apr 67.6 28057 27180 877 3.1 44.6
May 72.4 25855 25245 610 2.4 31.7
Jun 80.4 23898 23560 338 1.4 20
Jul 74.1 27394 26722 672 2.5 33.4
Aug
Sep
Oct
Nov
Dec
TOTALS 192399 181734 10665
AVERAGES 57.9 27486 25962 1524 5.4 82.1

 

[PaulBricker]

Well it's still pretty foggy for me. Any chance you could send me a sample. pbricker@attbi.com I will have some time this morning to look it over. Otherwise, you could try Group By App# and MyWeekatePart(&quot;ww&quot;,DateField) which would give you the week number and the App# to Group By and then try and sum your values. You could do the same for month. Group By App# and MyMonth:Month(DateValue). Your date field doesn't need to be in a column to be available to the date functions. It just needs to be in the table. This would give you two fields to group by and then your can try summing or averaging the other values.
Also, the formula I gave you for weekending is not correct. The last integer should be 6 not 5. 5 will give you Thursday.

Paul