Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2003
    Posts
    29

    Unanswered: Combining Queries

    Is it possible to combine the results of, say 5 or 6 queries into one table or query? The individual queries return a count integer and nothing else. I would like to combine all of them into one query or table so I can have access to all of them in one place.

    Is this even possible?

    thanks,

    Fisk

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Almost anything is possible ... From your description, what you're looking for a union query of your other queries ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Apr 2003
    Posts
    29
    Here's what I came up with. I went ahead and copied the sql statement of three of the queries and joined them with Union. I stuck this in a seperate query called "all"

    SELECT Count(dbo_AeXInv_AeX_OS_Add_Remove_Programs.Name) AS adobe5
    FROM dbo_AeXInv_AeX_OS_Add_Remove_Programs
    WHERE (((dbo_AeXInv_AeX_OS_Add_Remove_Programs.Name)='ad obe acrobat 5.0'));


    union

    SELECT Count(dbo_AeXInv_AeX_OS_Add_Remove_Programs.Name) AS adobe6
    FROM dbo_AeXInv_AeX_OS_Add_Remove_Programs
    WHERE (((dbo_AeXInv_AeX_OS_Add_Remove_Programs.Name)='ad obe acrobat 6.0 professional'));


    UNION SELECT Count(dbo_AeXInv_AeX_OS_Add_Remove_Programs.Name) AS adobe6std
    FROM dbo_AeXInv_AeX_OS_Add_Remove_Programs
    WHERE (((dbo_AeXInv_AeX_OS_Add_Remove_Programs.Name)='ad obe acrobat 6.0 standard')) OR (((dbo_AeXInv_AeX_OS_Add_Remove_Programs.Name)='ad obe acrobat 6.0.1 standard'));


    And this is what I came up with:


    adobe5
    12
    122
    423


    What i'm looking for is something like this:

    Name count
    adobe5 12
    adobe6pro 122
    adobe6std 423

    or something like that.

    fisk

  4. #4
    Join Date
    Apr 2003
    Posts
    29
    Ok, permit me to change my question a little. Or a lot. I figured out how to use Union to get it to display what i'm looking for. Here's the main problem i'm working on.

    I have one field in an Altiris database that i'm trying to get a count of applications installed in our company. For example, there might be 100 installs of adobe acrobat 6 professional. it's easy enought to do: select count(name) from table where name = 'adobe acrobat 6.0 professional' That will return what i want. the problem is there is more than one version of acrobat 6 pro listed. there is also 6.0.1 That's what's throwing me off. for the life of me, I cant figure out a way to combine them into one record. If I do a count, then I have to list the other columns "group by". That gives me two records instead of 1.

    My latest union query gave me this:

    count Name
    12 Adobe Acrobat 6.0 Professional
    34 Adobe Acrobat 6.0.1 Standard
    88 Adobe Acrobat 6.0 Standard
    423 Adobe Acrobat 5.0

    That's close, but I want the two lines of 6 standard to be in one record and the count to be 122

    I've been trying multiple ideas of ways to work around this, including a dozen google searches. It seems like somthing that would be simple, but i'll be damned if i can figure it out.

    Any suggestions

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Seems like a goup by clause would be more appropriate...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it is tempting to parse the single string you have into separate fields for
    name
    major-rev
    minor-rev
    type

    i had a similar parser "in stock" -- adapted version attached: it's DAO (of course!).
    the code might need a tweak or two if your data is inconsistent.

    izy
    Attached Files Attached Files
    currently using SS 2008R2

  7. #7
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by fisk
    Ok, permit me to change my question a little. Or a lot. I figured out how to use Union to get it to display what i'm looking for. Here's the main problem i'm working on.

    I have one field in an Altiris database that i'm trying to get a count of applications installed in our company. For example, there might be 100 installs of adobe acrobat 6 professional. it's easy enought to do: select count(name) from table where name = 'adobe acrobat 6.0 professional' That will return what i want. the problem is there is more than one version of acrobat 6 pro listed. there is also 6.0.1 That's what's throwing me off. for the life of me, I cant figure out a way to combine them into one record. If I do a count, then I have to list the other columns "group by". That gives me two records instead of 1.

    My latest union query gave me this:

    count Name
    12 Adobe Acrobat 6.0 Professional
    34 Adobe Acrobat 6.0.1 Standard
    88 Adobe Acrobat 6.0 Standard
    423 Adobe Acrobat 5.0

    That's close, but I want the two lines of 6 standard to be in one record and the count to be 122

    I've been trying multiple ideas of ways to work around this, including a dozen google searches. It seems like somthing that would be simple, but i'll be damned if i can figure it out.

    Any suggestions
    First of all I would not use UNION. I can see why Mark suggested it as your first question didn't give much detail about the queries that were to be combined. But since your data source appears to be the same throughout then just use an aggregate (group by) query.

    As regards grouping versions together, I would do this in one of two ways... Either split the string into two columns in your database e.g. "software", "version" and then just run the count on "software".

    Or (my preferred suggestion), if you don't want to split the strings, create another table that decodes the strings e.g. Table(Names):

    Name________________________________CommonName

    adobe acrobat 6.0 standard__________adobe6std
    adobe acrobat 6.0 professional______adobe6pro
    adobe acrobat 5.0___________________adobe5
    adobe acrobat 6.0.1 standard________adobe6std


    Then your query becomes rather simple:
    SELECT CommonName, Count(CommonName) AS SoftwareCount
    FROM dbo_AeXInv_AeX_OS_Add_Remove_Programs INNER JOIN [Names] ON dbo_AeXInv_AeX_OS_Add_Remove_Programs.Name = Names.Name
    GROUP BY CommonName

    If you choose this route it's probably worth forcing the 1:N relationship to ensure that all software is allocated to a CommomName thus catching spelling errors, new versions etc.

    You could also try parse as izy says.

    hth
    Chris

  8. #8
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by Teddy
    Seems like a goup by clause would be more appropriate...
    Sorry Teddy, didn't spot that you'd already suggested it.

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by howey
    First of all I would not use UNION. I can see why Mark suggested it as your first question didn't give much detail about the queries ...
    Chris,

    You're close ... My first name is MIKE ... And this correct. No detail as to the queries How do you help ??? This is Ted's lament also ... And for that matter Izy's and the Flumpster's ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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