Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59

    Arrow Unanswered: Multiple Queries to one????

    Hi

    I have a table which has stock in it, each item of stock has a unique ID/code, in the following format: D1, D2, etc, etc

    Depending upon what type of item it is determines the first piece of the code; there are 16 codes all together:

    D
    DM
    PR
    SN
    MS
    MP
    KA
    KP
    HD
    FD
    CD
    G
    M
    I
    SC
    N

    As an item goes out/sold I can mark it as sold in the db, I have designed a count query based upon the results of the 16 queries created to determine how many of any given item is left.

    Is there a way to put all 16 queries into 1 single query or do I need to keep all 16 queries and then base the count query on the 16 queries.

    Any help appreciated

    Mike
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Now do you understand why I was pushing for a schema redesign?

    If you STILL won't redesign, you COULD use a union query. It will be extremely inefficient and annoying to work with, but you can do it.

    SELECT someField
    FROM yourTable
    UNION
    SELECT someField
    FROM yourOtherTable
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    If I used a union query it would put all the records into one but how would i do a record count on each individual internalID type, how whould i get it to tell me how many of each componant i got i got without basing it on seperate queries.

    Mike

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Union queries combine the output of different queries into one recordset. Eg, if you had two queries like so:

    SELECT "a string", COUNT(*)
    FROM yourTable
    WHERE someField = "a string"
    UNION
    SELECT "another string", COUNT(*)
    FROM yourTable
    WHERE someField = "another string"

    you would get:

    a string | count of fields matching "a string"
    another string| count of fields matching "another string"
    oh yeah... documentation... I have heard of that.

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

Posting Permissions

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