Results 1 to 14 of 14
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subtraction Operation Within the Same Field (2000)

    In order to make matters as much understandable as possible I've prepared a table (see attached file) with only three records.
    The table has one text field, one date field and several numeric fields.
    How can I prepare a query that returns the difference between the values in any two successive months?
    I mean, basically I want to subtract the values referring to March from the values of April and the values of April from the values of May. In this case, the query should then return two records.
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtraction Operation Within the Same Field (2000)

    I have found something for one month in one query.
    Let us know if this is usable.
    See query1in the attachment
    Attached Files Attached Files
    Francois

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtraction Operation Within the Same Field (2000)

    Hi Francois, that's very good.
    Is there a way to make the query return both records without the need of selecting the month in a parameters box?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtraction Operation Within the Same Field (2000)

    Have a look at query2 and query3
    Query2 is still asking the month.
    Queyr3 do not. I use the DMin function.
    These are Union Queries, so you can't see them in design view. You have to write them in SQL.
    If you need help for this, post again.
    Attached Files Attached Files
    Francois

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtraction Operation Within the Same Field (2000)

    Another piece of magic on your part. Both your queries have their qualities and I'm still mulling over them to exploit them as best as possible. One thing I've noticed though is that if I use as many unions as to cover all months of the year and then run the query on a table which doesn't encompass the whole year, then one of the records returned by the query has fields with zero or negative values. In the case in question that record would be the one with the CurMonth value of 6.
    <font color=ff69b4>Ciao</font color=ff69b4>

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtraction Operation Within the Same Field (2000)

    You could create a new select query based on the Union query and filter out the last month(s).
    Francois

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Subtraction Operation Within the Same Field (2000)

    Excuse me for poking my nose in here, but do you want records returned for olmo and olmo1 for each of the months (except the last month) or do you want them summed across all names for each month?

    If you want them summed for each name and month then have a look at query4.

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Attached Files Attached Files

  8. #8
    Star Lounger
    Join Date
    Jan 2001
    Location
    Yorkshire, England
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtraction Operation Within the Same Field (2000)

    this creates a couple of dummy fields coverting date to a number and then matching on them. I've set it just to calc one field . Don't know how long it might take with lots of fields on a big file .
    Attached Files Attached Files

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Subtraction Operation Within the Same Field (2000)

    Nice solution, but be careful with your number calc, you will find that year 2002 month 04 converts to number 2006 as will year 2003 month 03.
    This assumes of course that the report could span multiple years, if not, your solution will work.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  10. #10
    Star Lounger
    Join Date
    Jan 2001
    Location
    Yorkshire, England
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtraction Operation Within the Same Field (2000)

    oops! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    yes you're right of course - think thats whats called lack of testing.

    Should have used year*12 + month which should give unique numbers

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtraction Operation Within the Same Field (2000)

    It took me some time to answer because I thought I had e-mail notification of replies on, but it wasn't! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Pat, you guessed right, I want them summed for each name and month so Query4 is fine.
    You're fantastic, all of you guys, thank you so much for your efforts, it looks like you're ganging up to compose a symphony and now the options available to me have exponentially increased...I need some time to sort them through.
    <font color=ff69b4>Ciao</font color=ff69b4>

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtraction Operation Within the Same Field (2000)


  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Let's resume the topic...

    How come your query4 gives negative results even for April and March if I put zero instead of 1.14 for June in the numeric field of the table?(see attachment)
    <font color=ff69b4>Ciao</font color=ff69b4>
    Attached Files Attached Files

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtraction Operation Within the Same Field (2000)

    I can't figure out why your query4 works even without putting single quotes around Format([tabella1].[data],'yymmdd') as in
    <font color=red>Sum(nz(DMin("[Prestazioni di Terzi]","tabella1","Tabella1.Dipendenza = '" & Tabella1.Dipendenza & "' and format(Data,'yymmdd') > <span style="background-color: #FFFF00; color: #000000; font-weight: bold">'</span hi>" & Format([tabella1].[data],'yymmdd') & " <span style="background-color: #FFFF00; color: #000000; font-weight: bold">'</span hi> "),0))</font color=red>
    Could you please shed light? <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •