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

    Re: Crosstab Headings Headaches (2000)

    If I understand what you mean, you can make a new query based on the 2 crosstab queries and select the fields you want.
    see attachment
    Attached Files Attached Files
    Francois

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

    Crosstab Headings Headaches (2000)

    In the attached mdb I'd like to add the Power field of the Power table to Query1.
    I've done that in Query2 but how can I avoid having each record in Query1 get repeated twice in Query2 and have the Power field pivoted like the Energia field of the Energy table?
    Basically the column headings in Query2 should read, from left to right:
    Dipendenza, Mese, F1, F2, F4, TF, Totale, F1, F2, F4
    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

    Almost but not quite :-)

    Thanks Francois, I saw you mentioned in Woody's ACCESS Watch #4.01.
    The qryCombined query you see in the attached mdb comes closer to what I want to achieve but I have a problem:
    If you compare qryCombined with Query1, you can see that each record in Query1 gets repeated twice in qryCombined. That's because the three different values (P1, P2, P4) of the Power field relevant to each value in the Dipendenza field are shown vertically instead of horizontally.
    Do you think I can fix that?
    Attached Files Attached Files

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

    Re: Almost but not quite :-)

    I have made a new query 3 with the power table only, and a new qrycombined2 with query1 an query3.
    Is that what you're looking for ?
    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

    Bull's eye!

    I just tweaked it a little with a Right Outer Join et voil
    Attached Files Attached Files

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

    Just a small adjunct...

    How can I tell Access to eliminate duplicate records(which are the result of importing them from a delimited text file) from the Power table?
    If you open the Power table you can see that, since the first three records get repeated, the output of the qryCombined2 query gives me incorrect values for the P1, P2, P4 fields.
    <font color=ff69b4>Ciao</font color=ff69b4>
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Just a small adjunct...

    Don't you set this in a queries properties, using either the Unique Values or Unique Records <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15>
    Or do you mean actually delete the duplicate records from the table <img src=/S/question.gif border=0 alt=question width=15 height=15>, in which case I'd likely use a Make Table query, with the appropriate Unique property set

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

    Re: Just a small adjunct...

    Thanks Steve, the first option did not work because I kept on obtaining summed up values for the P1, P2, P4 fields in qryCombined2.
    But the second one did the trick.
    <font color=ff69b4>Ciao</font color=ff69b4>
    Attached Files Attached Files

Posting Permissions

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