Results 1 to 11 of 11
Thread: Math from a sum Query (2007)

20070518, 01:17 #1
 Join Date
 May 2007
 Posts
 8
 Thanks
 0
 Thanked 0 Times in 0 Posts
Math from a sum Query (2007)
OK, here's the situation, I manage 2 airplanes, calculating flight hours and landings, a sum query works really well for this and returns the number perfectly, this was after adjusting it so the it was bound to "0" on a drop box rather than 1, I am also able to create a subform as well as a stand alone form for these numbers and they show up just the way I want to, now the problem, both of the airplanes go through regular inspections, these are based on hours flown, what I cannot do is, take the total sum of hours flown and subtract the next inspection number to show how much longer we have to fly before the next inspection is due, Example the total hours flown from the logbook is 7878.9, the next inspection is 7978.9, math in the head says you have 100 hours left to fly, however, when the math is tried to subtract the summed(SP?) number from the next inspection number, the number that is summed comes back as "0" and leaves the next inspection due as 7978.9, manipulating anything other than just straight math returns the bound box as an "Error" or "Name", so the question is, how can I get the summed number to read as the summed number for math, or is this not possible.........any guidance would help dramatically, if requested I will send the sheet to actually see what I mean, not being a programmer, I may be conveying myself wrong on what I want to say, but I think I got this in the right direction.

20070518, 06:34 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Math from a sum Query (2007)
Welcome to Woody's Lounge!
Can you tell us what expression you used for the calculattions? If you'd like to attach a stripped down copy of your database, see <post#=401925>post 401925</post#> for instructions.

20070518, 10:46 #3
 Join Date
 May 2007
 Posts
 8
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Math from a sum Query (2007)
I tried to water down the database but could not get it below 154KB, don't have access 97 so here we go, the calculation is just a simple sum in the QBE, just a sum of numbers, a running total, This total can be selected and used in a sub form, or as a bound object with a form or report and works just fine, but, when you try to do math with it, the total acts as though it were zero, although, when using it in a form to total all the numbers, it works great, but when I try to take a number and subtract the sum, the running total, the one in the QBE, it acts as though it is not there, also, if I try to do math with it from a table or another form it acts like a zero, like nothing has ever been added to the sum, once again, I probably am not conveying my information correctly for someone who knows the right terminology, but the basic is, I sum a total, it does sum, and shows a total, but if I try to do math with it, it acts like a zero, but if I stand it alone, it becomes a total.

20070518, 11:22 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Math from a sum Query (2007)
You don't need to have Access 97, you should be able to convert your database to the Access 97 file format from Access 2007. This will reduce the size. And of course, you should zip the resulting file.

20070518, 17:15 #5
 Join Date
 May 2007
 Posts
 8
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Math from a sum Query (2007)
Hopefully this works, on the Next Inspections N2032 page, you will find several ways I have tried this, with some explanations, Once I get this figured out I should be ready to use the whole thing.
JB

20070518, 19:35 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Math from a sum Query (2007)
Your table 'Inspections Due N2032' has a very strange design. Both Current Time and Current Cycles have been set up as combo boxes (dropdown lists) with a row source that doesn't seem relevant. If you reset the display type to text box, you'll see that the value Current Time and Current Cycles seemed to have actually isn't there, and that their real value is 0.
What is the idea behind the combo boxes?

20070518, 20:33 #7
 Join Date
 May 2007
 Posts
 8
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Math from a sum Query (2007)
I know, that was the only way to draw the sum times into those boxes, unless I completely missed something, there was no other way to bring it in, I tried several variations and the numbers would not show up, that was until I changed the lookup to a drop box, then the numbers came in, and yes, that seems to be my problem as well, everytime I try to do something with the sum numbers, they show up as a zero, it does not matter where I pull them from, it's always zero when obviously I am inputting numbers, but they are not showing up for math.
JB

20070518, 22:25 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Math from a sum Query (2007)
I get the impression that you're trying to sum the field Hobbs Hours from Write Ups N2032, but it is a text field, so it is not suitable for calculations. You should change it to a number field (Double).
You can use the DSum function to calculate the sum of a number field from another table than the record source of the form, e.g.
<code>
=DSum("[Hobbs Hours]","[Write Ups N2032]")</code>

20070519, 03:01 #9
 Join Date
 May 2007
 Posts
 8
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Math from a sum Query (2007)
That is correct, that is where the numbers generate from, I will try the DSUM function tomorrow, just too blind from working on the rest of it tonight, then I will let you know how it works out.
Thank you very much, if i still have trouble I will let you know, well, I will let you know one way or the other.
JB

20070521, 11:42 #10
 Join Date
 May 2007
 Posts
 8
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Math from a sum Query (2007)
Thank you, that works just the way I was looking for it to do, NOW, the whole program is ready to go, thank you very much, if I have anymore trouble I will be sure to look you guys up.
J. Batcheller

20070521, 11:47 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Math from a sum Query (2007)
Glad it worked. Thanks for letting us know.