Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make-table Queries (Access 2000)

    If I understand your problem correctly, you should try replacing the fourth field (Energia) in your Make Table query with an expression something like:

    <font face="Georgia">DifferenceOfEnergies: IIf([TipoMov]="PER",-1,1)*[Energia]</font face=georgia>

    This makes any Energia that has a TipMov of "PER" negative. That value is then added to the "ACQ" Energia, thus giving you the subtraction you're looking for. This may be a little too "hard-wired" (e.g., using "PER" to determine if the Energia needs to be negated), but it should get you started down the right path.

    Hope this helps.

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

    Er, One More Thing...

    Please take a look at the Energy1 table again. You can see I've added the "CON" value to the TipoMov field for three records but there are three more records which, though tagged "PER", pertain to the same Dipendenza field value( "CEM. COLLEFERRO" in this case).
    How can I modify the Energy Query so as not to apply the Sum operation whenever the "CON" tag is involved and simply return the Energia field value relevant to the "CON" tag?
    Basically the query you already suggested should work as is for the first six records in the Energy1 table but disregard the values tagged PER for the last six records of the Energy1 table so that the result of the query for the last six records is equal to records 7 through 9 of the Energy1 table.
    <font color=ff69b4>Ciao</font color=ff69b4>
    Attached Files Attached Files

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

    Make-Table Queries (Access 2000)

    How can I design the make-table Energy Query of the attached mdb so that the last three records in the Energy1 table are subtracted, instead of summed, from the first three, based on the fact that the TipoMov field in the Energy1 table groups them differently?
    Attached Files Attached Files

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

    Re: Make-Table Queries (Access 2000)

    Thanks Tom.
    That's a clever piece of thinking.
    <font color=ff69b4>Ciao</font color=ff69b4>

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

    Re: Er, One More Thing...

    Not sure of what you want but have a look at query3 made of query1 and query2.
    Attached Files Attached Files
    Francois

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

    Your Brain Rides Shotgun!

    Perfect! Thanks.
    I need some time to digest it though <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    <font color=ff69b4>Ciao</font color=ff69b4>

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

    Re: Your Brain Rides Shotgun!

    Second thought:
    Couldn't you change the structure of energy1 and put the value of energia in an new field when it is PER ?
    The table would be halve so long and query would be much simpler?
    Francois

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

    Re: Your Brain Rides Shotgun!

    I wish I could do that.
    I use the Get External Data command in Access to import the data from a delimited text file(attached) created with a SQL procedure run on a DB2 mainframe database using SAS(tm). As far as I know the Access import procedure doesn't allow me to split the values of the Energy1 field into two fields depending on what value another field(TipoMov) has.
    <font color=ff69b4>Ciao</font color=ff69b4>
    Attached Files Attached Files

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

    Allow me to pick your brain again

    I've tried and applied Query3 to the Energy table(which is a larger version of the Energy1 table) but I can't understand why not all the values from the Energia field from Query1(which is one of the source queries for Query3) show up in the Expr1 column of the Query3 result set. Do you?
    Attached Files Attached Files

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

    Re: Allow me to pick your brain again

    I have add the Nz function to query3 in case there are no PER records for some ASC or CON records.
    Attached Files Attached Files
    Francois

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

    Re: Allow me to pick your brain again

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    Thanks Francois!

Posting Permissions

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