Results 1 to 8 of 8
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Design Question (2k)

    Could you export the result of query#2 to Excel, and use Copy, then PasteSpecial with Transpose:=True to transpose it?

  2. #2
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Design Question (2k)

    Yeah thats basically what I have been doing, but I'd like to automate it to the point where it outputs the data into a template excel file in the first 2 columns, and my other columns already containing formulae automatically compute the rest. Since my chart is based on those 2 columns, it is automaticallys generated as well. so basically automating that last part would allow me to generate the charts in 'a click of a button'.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Design Question (2k)

    So what keeps you from automating it?

  4. #4
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Design Question (2k)

    I'm dumb! I don't know how to get my data organized the way I want before its output to excel.

    my current data is setup like this (Point A):
    N1 | N2 | N3
    9 | 3 | 5

    and I want it to look like my first post shows (Point [img]/forums/images/smilies/cool.gif[/img]. I don't know how to get from point A to point B.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Design Question (2k)

    Hi, I'm trying to automate some charts I generate in excel that come from my database. I'm handling most of the automation through my queries which I am having some trouble doing. I have the first 2 queries done that:
    first- sort through all my data finding repeats and assign them a value of 0 (for no problems) to whatever number X (denoting multiple problems)
    second- count the amount of times that there was any error, meaning it does a sum(IIF([status]>0, 1, 0)) type of thing.

    now at some point in this process, i need to tally up the total across all of my columns in query #2, and i would like to arrange the data to look something like this (Point [img]/forums/images/smilies/cool.gif[/img]:
    Nstatus1 | 6
    Nstatus2 | 3
    Nstatus3 | 5
    statTotal | 14
    (this is what my manually done excel spreadsheet basically looks like)

    So my problem is, I can't figure out how to transpose the data and total it at the end. I think that the total part isn't even the problem, I can just use the Total function in the query I make to do this, but I don't know how to do this transpose thing that I want to do.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Design Question (2k)

    Are your NStatus1 etc. fields (N1 here) fixed? If so, you could create a table listing those field names:

    <table border=1><td>FieldName</td><td>NStatus1</td><td>NStatus2</td><td>NStatus3</td></table>
    and create a query based on this table that retrieves the counts from your query # 2 using DLookup:

    DLookup([FieldName],"Query2")

    (no quotes around [FieldName] !)

  7. #7
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Design Question (2k)

    Thats an interesting suggestion Hans, that might actually solve two of my problems at once. I'm going to play around with that and see how it works out. Thank you!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query Design Question (2k)

    If you are a newcomer to automation, you might find the Automation Help file for 2000 useful - see <!mskb=260410>Microsoft Knowledge Base Article 260410<!/mskb>. You might also want to look at <!mskb=202169>Microsoft Knowledge Base Article 202169<!/mskb> and <!mskb=210148>Microsoft Knowledge Base Article 210148<!/mskb> which deal with automating Excel. You might also find our Automation Tutorial useful.
    Wendell

Posting Permissions

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