Trying to obtain 2 different sums from one field

[Harloff]

This is my problem. The person who started developing this databas passed away before finishing it.
This database is used to chart, Call Records. It has fields for: Date, Location, Incoming/Outgoing, Call Length (Duration), Average Call Length (Avg Duration), and Total Calls (Call Count).

The table is named "Table1.' In it, I have a field called "Incoming/Outgoing" all entried in the columns are entered as either "Incoming" or "Outgoing.'

Another field is called "Duration" and contains a column of (numeric) numbers of minutes for each Incoming or Outgoing call.

I have no problem with Call Counts and Total Durations.

What I need to do is create a report that seperately shows the Sum of Incoming Minutes and Outgoing Minutes.

I have little experience with Access 2007 and have only been trying to resolve this problem for about 2 months, relying on a MS Access 2007 Textbook and whatever I can find on Google.

Is there something so simple to resolve this that is right in front of my face that i am not seeing?

[Rabbit]

Use an case statement to change the value to 0 if the field is not incoming and do a sum on that. Do the same thing except for outgoing.

[NeoPa]

You have posted this in SQL Server, and Rabbit has answered on that basis. I suspect though, that you put an Access question in the wrong forum (Please correct me if I'm wrong). I will therefore move this to the Access forum for you.

[NeoPa]

This will be harder because you haven't included what you already have. A must when asking questions on a forum. For now I will assume you have a query which is GROUPed BY the relevant fields.

In your QueryDef (Access saved query) you would need to include two fields as, both of which will have Sum in the Total: row underneath Field: and Table: :

IncomingMinutes: IIf([Incoming/Outgoing]='Incoming',[Duration],0)
OutgoingMinutes: IIf([Incoming/Outgoing]='Outgoing',[Duration],0)

[TheSmileyCoder]

Another approach could be to use the built in Dsum (look it up in the access help!)

Add a textbox to your report, and in the textbox property ControlSource write:

=Dsum("Duration","Table1","[Incoming/Outgoing]='Incoming'")

Do similar for outgoing.

[NeoPa]

I'm not sure when it would ever make sense to use DSum() when simple Summing is already available Smiley. You would also need to ensure that whatever GROUPing is already applied in the query is duplicated in your DSum() call.

[Harloff]

You are probably correct. This is all new to me and it's going to take me a while to parse out this wonderfull information people are posting.

[Harloff]

Sorry about that. As I hinted,Access 2007 is new territory for me and I've only been exposed to it for a few months.
The two fields I am trying to draw an association to are: Incoming/Outgoing (which the data entry person types in either the word "Incoming" or "Outgoing."

The field I am trying to associate it to is called "Duration," and only lists the minuts for every call whether it is Incoming or Outgoing."

In my query (table1), the only criteria I have stated, is in the Date field where I placed "Between [Start Date] and [End Date]."

The table is called: "Table1" ... the query is temporairly called "Kim's Query" ... the report is temporairly called "Kim's Total Report."

As I noted, I have to make Access distinguish the "Incoming" minutes from the "Outgoing" minutes which are grouped together in the "Duration" field. Does that help?

[NeoPa]

Yes. That's some help.

First you need to know if the report will include details for every individual record, as well as the grouped totals. If that is the case then the query should return all details and the summing should be done in a footer of the report. If, on the other hand, you are only showing totals in the report, then the query [Kim's Query] should be a GROUP BY query and the calculations done therein.

We need to know this in order to assist further.

NB. Never use characters like the quote (') in object names. That character is used within SQL syntax and will certainly cause problems and confusion. Good idea to include the names you've used though.

[Harloff]

I used your Dsum info and it is giving me a total for the entire table. I need it to only give results for the Date Range which I entered as between [Start Date] and [End Date] in the criteria for the "Date" field.

[NeoPa]

That would have been helpful information to include in the question of course.

As I indicated in an earlier post, using DSum() would not be the recommended approach for various reasons (Sorry Smiley), and you've fallen over one that I outlined earlier. Neither is it necessary to do so.

If you could find your way to responding to my earlier post (#9), I could give more detailed assistance. Without an understanding of what you're trying to do though, making more detailed suggestions (than I have already) is likely to waste both of our time.

[Harloff]

I am making two separate reports. One will just be a summary of the tally for individual counties, and the other will be detailed showing each call record.

This is what is posted at the bottom of each detailed report:
Summary for 'Location' = Broome (6 Daily records)
Total Call Count 6
Total Minutes 187
Total Incoming 5
Total Outgoing 1

What I need to add is: Total Incoming Call Minutes, and Total Outgoing Call Minutes.

These to Totals need to be included for each County listed in the report.

If you contact me at {snip}, I can send you an RAR file of the database, or a copy of a report page, or a screen capture of the report in Design view.

[NeoPa]

I believe that answers my question Kim. With such a setup I would recommend a query that includes all the details. That means the summing, when required, should be done in the report rather than the query (You can then use the same query for both objects).

Header and Footer sections in Access reports can have expressions that take the form :

=Sum([Field])

They only sum across the grouping for that specific section. So, if you have a Group Footer for months, for instance, the summing will only include values for the month covered. The expressions used would fundamentally follow the lines of the expressions posted in #4 (but include the extra Sum() part) :

=Sum(IIf([Incoming/Outgoing]='Incoming',[Duration],0))
=Sum(IIf([Incoming/Outgoing]='Outgoing',[Duration],0))

PS. I don't think I need the hands-on just now, but if we do get to that stage then I will give instructions on how to attach a ZIPped copy to a post. That can be secured by a password if you want to and passwords can be passed by Private Message to ensure it remains secure from others if that's your wish.

[Harloff]

I created 2 Text boxes in the Location footer and wrote in the control boxes what you posted.

In my Query, the only criteria I have is in the Date, which is Between [Start Date] and [End Date].

I also have a Sum of Duration and Avg of Duration in my Query. If that helps any.

Now, besides getting my Enter Start Date and Enter End Date parameters, I also get an Enter box for Duration. Also, the expression you provided returns '0'. But at least I believe we're on the right track.

[NeoPa]

Harloff:
I also have a Sum of Duration and Avg of Duration in my Query. If that helps any.

That indicates that the query is not returning the detailed data, but data that's already been aggregated.

At this stage I suggest you post the SQL of your query for us to look over for you.

For the report, it would be helpful to see the controls that are related to this issue, as well as a rundown of the sections included and any Sorting and Grouping you have set up (with an indication of which sections are related to this). If there's any of this you don't understand then please let us know and we'll go into finer detail for you.

[Harloff]

PARAMETERS [Start Date] DateTime
         , [End Date] DateTime;
SELECT DISTINCTROW
           Table1.Date
         , Table1.Location
         , Table1.[Incoming/Outgoing]
         , Sum(Table1.Duration) AS [Sum Of Duration]
         , Avg(Table1.Duration) AS [Avg Of Duration]
         , Count(*) AS [Count Of Table1]
FROM       Table1
GROUP BY   Table1.Date
         , Table1.Location
         , Table1.[Incoming/Outgoing]
HAVING     (((Table1.Date) Between [Start Date] And [End Date]));

[NeoPa]

Kim,

While laying out the SQL isn't mandatory here, it's always in your interest to do so. [code] tags are mandatory, and I've added them for you this time. Please remember to use them in future.

There is little I can say at this stage technically, as it's late and I don't have much time, but I would make a couple of points :

1. Your aggregate functions (Sum(), Avg() & Count()) are not required within the query if you wish to use it to show details as you've indicated. They will also aggregate across the whole data range too, rather than across the groupings required in the report.
2. Your HAVING clause should be a WHERE clause and should be found immediately after the FROM clause.
3. As explained in my previous post, we also need information about your report controls and setup.
4. The DISTINCTROW predicate of the SELECT clause is unnecessary in a GROUP BY query. I suspect when you change it to a standard SELECT query it will equally unrequired, unless you know something I don't about your data (and if that's the case then you probably ought to have shared that by now too ;-)).

I'll come and look again tomorrow (later today technically) when I'm awake again.

[Harloff]

Thanks for the info. I didn't know anything about code etiquette until now.

The Query was generated by the Query Wizard and that is the order it created.

At this point, this being a little over my head technically, I think the best thing to do is delete the Query and start from scratch sine this situation is growing more complex for the both of us whenever I post.

As I said previously, I know very little about Access 2007. I'm literally learning as we move along.

[NeoPa]

Harloff:
I didn't know anything about code etiquette until now.

Posting etiquette actually, but that's not too much of a problem. We like people to be aware of the rules, as they make everything work so much more easily, but we also understand that people rarely do. If you have a better understanding now then that's a step in the right direction.

Harloff:
The Query was generated by the Query Wizard and that is the order it created.

Indeed. Access isn't the best query creator around, and it can't read minds either, so wizard created queries are rarely the best. They're jolly handy for helping people start to understand SQL for themselves though.

As for the query itself, the main problem with it is that it doesn't do what you need. There is nothing greatly wrong with what you posted. The issue seems to be that, from my understanding of what you've said (which could possibly be faulty), it isn't what you need. I believe you need a simpler query that doesn't include the GROUP BY clause or any aggregating functions. Something like :

PARAMETERS [Start Date] DateTime
         , [End Date] DateTime;
SELECT [Date]
     , [Location]
     , [Incoming/Outgoing]
     , [Duration]
FROM   Table1
WHERE  ([Date] Between [Start Date] And [End Date])

[Harloff]

OK. I created a new query called Query1.

In it, I placed: Date, Location, Incoming/Outgoing, and Duration.

I don't understand steps 6 and 7 in your previous reply, what they are, or where to place them, or steps 1 -7.

In the Warmline Monthly Tally report, In my Location footer,I inserted a text box and a Control box. In the Control box I inserted:

=Sum(IIf([Incoming/Outgoing]='Incoming',[Duration],0))

When I now run the report, I get a 0 for the Duration. All other report functions, which are counts, are working fine.

Once I get this program working, Then I can go through it and see the relationships between all the steps. As for right now, I seem to only be able to "Paint by Numbers.'

[NeoPa]

Harloff:
I don't understand steps 6 and 7 in your previous reply, what they are, or where to place them, or steps 1 -7.

I don't have a message with 7 or more steps in Kim. Post #19 has SQL in with 8 lines, but as #6 is the FROM clause, which is common to all SQL posted so far, your comment doesn't make sense in that context. I'm afraid I have no idea what you're referring to :-(

Harloff:
In the Warmline Monthly Tally report, In my Location footer,I inserted a text box and a Control box. In the Control box I inserted:

What is a Control box? I would expect the formula you posted to be put in a TextBox control. I would also expect it to work (and give meaningful results).

It seems that, newbie or not, you've taken that step without specific instructions and got it right anyway :-) Now I don't understand why it isn't working.

Please do the following for me :

1. Post the SQL of Query1 in here (Please use the [code] tags).
2. Explain what you mean by Control box - or just explain again what you did in the footer with whatever controls you used.

Harloff:
As for right now, I seem to only be able to "Paint by Numbers."

That's fine for now. Think of yourself like a cameraman that enables some expert to handle the job remotely. That expert relies on your holding the camera level and passing the information back as accurately and clearly as possible. I'll do my part the best I can, but I do rely on your doing a good job feeding me the information. So far we're making progress.

While you're waiting for my next reply, why don't you run the query itself. On its own. Think about the data there and what you want and expect the report to produce. That level of understanding will help you when it comes to looking at the whole picture later on.

[Harloff]

My bad, it is a Textbox Control I was talking about.

This is what was automatically generated in the SQL when I created the new query, Query1:

PARAMETERS
  [Start Date] Date Time
, [End Date] Date Time;
SELECT Table1.Date
      ,Table1.Location
      ,Table1.[Incoming/Outgoing]
      ,Table1.Duration
 
FROM Table1
WHERE (((Table1.Date) Between [Start Date] AND [End Date]));
)Between

As for being a cameraman, yes, I've been one in a public access television studio.

I am thinking about taking an adult education college course on Access/Excel. The Microsoft textbook I am relying on is serving little purpose since it fails to note the results that can possible occur when I make a mistake. Something only a teacher can discuss in "Real-time" with a student.

[NeoPa]

I noticed your attempt to format the SQL Kim. Definitely the right approach :-) I added the tags for you though. They can be found in the box on the button that says :
<CODE/>
If you click on that with the code part of your post selected it will surround it all in the tags.

I suspect you made a mistake while working with the SQL though, the last line posted doesn't fit with the rest. The rest of it makes sense though.

The cameraman comment was a lucky hit. I had no idea. I was using it as a metaphor to help you understand that without the cameraman doing a good job, my skills won't help you. I need as good an understanding of what you're working on as possible. Cool that you actually have cameraman experience though :-D

As for learning going forwards, each increase in your basic understanding makes it easier to learn from what's around you. It may seem slow at the moment, but every step forward you take makes it more likely you will understand what others are saying and also that they will understand you. I expect it's quite frustrating at this stage, but persevere with the basics and I can assure you, you will be able to proceed and develop much more quickly as you go on. That said, a course early on never hurts. Be sure to find a course that's pitched at your current level though. They can be expensive, and buying one that is either too basic or too advanced can leave you with very little benefit for your money.

Back to the main question. The SQL posted, other than the extraneous last line, looks like exactly what you want as the Record Source for your report. Is that what you now have?

[Harloff]

Adding to my frustration and confusion is the fact that someone is editing what I post and now I cannot refer to previous posts, especially SQL that I posted in a reply.

It's nothing of your fault, I just feel like I'm back at square one and need to research a different solution to my problem.

This event has grown from a molehill to a mountain.

I was hoping to resolve this before the end of the month so that I do not have to hand tally about 300 forms to get monthly totals, but that dream has pretty much dissipated.

[Rabbit]

The code you posted in previous posts is still there so you can refer to it. If you are unable to see the code, you are probably using internet explorer, in which case you need to turn on compatibility mode and it will render correctly.

[Harloff]

I'm using Firefox, but I'll switch to Exp

[Rabbit]

I was not suggesting that you switch to internet explorer. I was only offering a fix if you were using internet explorer. If you're using firefox, the page should render fine.

No data was taken out of the posts that you made previously. Only the code was formatted so it's easier to read. What information are you saying is missing from your previous posts?

[Harloff]

I'm lost again. I don't see anything on this page resembling a button called: <CODE/> althought I do recognize it as similar to an HTML Tag. I must be overconcentrating again.

[Harloff]

I didn't know it was reformatted, I didn't recognize it. I drew that conclusion due to the fact that I have received several notifications telling me that my posts have been edited.

[Harloff]

It is 11:37pm here and I'm going to bed in about a half hour. I'll try to access a computer at work tomorrow, but even if I do, I have to inform my supervisor that I'll probably have to hand-tally the reposrs for December; that I just don't have enought of this under my belt to get Access running properly. CU tomorrow.

[Rabbit]

The button is at the very bottom of the page in that little toolbar above where you type replies.

[NeoPa]

All the edits made to your posts in this thread have been by myself Kim. I can assure you that none of the SQL was changed except the formatting (SQL syntax doesn't care about any white space characters except their presence or absence. IE. a new line followed by spaces is treated by SQL exactly as it would a single space or new line). If you have any doubts then any moderator has access to all versions, before and after all edits, so they can confirm that nothing of substance was changed.

If you find that you are unable to see what is in the code part of any post then please feel free to send me a PM with the details (A screenshot would help) and I will look into it further for you. As Rabbit says, switching to IE for your browser is certainly not recommended. I use FF without any problems, so that should be fine.

As for the problem, I feel your frustration. If you'd like to PM me later and if you may have some spare time this evening (I'm London based so not too late) then I may get a chance to talk your problem over with you later. Let me know if you're interested and Skype details if you have it. Maybe we can get you sorted before Christmas after all - but no promises.

[Harloff]

I want to thank, both, NeoPa and Smiley Coder for their assistance in helping me troubleshoot the Access Db I was trying to get working.

A lot of errors were in the Query and a few were due to data entries in the wrong fields due to other people, where I work, directly entering data into the table instead of using the Form. Problem resolved! Again, many thanks to all in this forum who replied to me :) Kim

[NeoPa]

Kim:
Problem resolved!

Very pleased to hear it :-)

We're here if you come across any further problems.