Page 1 of 4 123 ... LastLast
Results 1 to 15 of 46
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Combine Three queries for one Dynaset

    Being that I have a simple mind . . . I gofrom point A to point B to point C etc.

    this is what I would like to do . . .

    SELECT Scmls.ListName, Count([Scmls.ListName])*2 AS WSO, Sum([Scmls.SalePrice])*2 AS WSODV,
    AND Select Scmls.ListName, Count([Scmls.ListName]) AS TSO, Sum([Scmls.SalePrice]) AS TSODV,
    AND Selct Scmls.SellName, Count([Scmls.ListName]) AS WST, Sum([Scmls.SalePrice]) AS WSTDV
    FROM Scmls
    WHERE (((Scmls.SellName) Like [ListName]))
    AND WHERE WHERE (((Scmls.SellName)<>[ListName]))
    AND WHERE (((Scmls.ListName)<>[SellName]))
    GROUP BY Scmls.ListName, Scmls.SellName;

    The only error (So Far) is Syntax error (missing operator) in query expression AND Select Scmls.ListName . . .

    Am I close? Ha!

    Thanks . . . Rick

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    WHERE only needs to be there once, not 4 times.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I also think that (but I could be wrong)

    Scmls.SellName Like[ListName]

    is that same as

    Scmls.SellName = [ListName]

    Unless [listname] already contains a wildcard character.
    Inspiration Through Fermentation

  4. #4
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Required!

    WHERE (((Scmls.SellName) = [ListName]))
    WHERE (((Scmls.SellName)<>[ListName]))
    WHERE (((Scmls.ListName)<>[SellName]))

    The values in these fields are calculated against each other based on what value is in each field relative to the value in the other field as the queries runs down the table.

    Trust me, I need to have them.

    I'm also wondering how Access kows which WHERE statement applies to which SELECT statement? It does matter.

    I'm off to a meeting but will anxiously look forward to cheking in here on my return.

    Thanks much! Rick

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Sorry, my eye went right to the multiple "Where". I didn't notice you had
    multiple "Select" also.

    So you've got three seperate queries, and you want to see the results of the three joined together. Looks like you need to use a union query. Build each query seperately, then join them with a union. Unfortunately, I'm off to a meeting...
    Inspiration Through Fermentation

  6. #6
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Hi RedNeckGeek

    Problem is that UNION Queries are used to combine fields from several tables or queries into one field.

    My queries have field designators . . . WSO WSODV TSO TSODV WST WSTDV which are field names when the dynaset is displayed.

    These fields must remain separate as they hold diffeent values from on another.

    Also, UNION queries requir that each SELECT statement have the same number of fields, in the same order!

    Rats! I was hoping it would work.

    Thanks much RedNeckGeek

    Rick

  7. #7
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    Just passing through so I have not given the matter much analysis, but would it be possible to store each component as a separate external query and then have your SQL combine the output of all of the queries. After all, for SELECTS, queries can be used in lieu of actual tables. They are called Views in the major database systems.
    Regards,
    Jim Wright.

  8. #8
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Rick, a UNION query is used to combine two datasets into one. When you use 2 UNION statements, you will combine 3 datasets into one dataset.

    UNION is used like this:

    Code:
    Select Fld1, Fld2, Fld3 FROM qryQuery1
    UNION
    Select Fld1, Fld2, Fld3 FROM qryQuery3
    UNION
    Select Fld6, Fld4, Fld3 FROM qryQuery16;
    Please note, I used different field names in the third query that was UNIONed together. The only requirement is that the fields are of the same type, and there is the same number of fields in all UNIONed queries. The output of this query will use the names as defined in the first UNION query.

    Hope this helps,

  9. #9
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Like This . . .

    GolfGuy . . . my field names are Scmls.ListName and Scmls.SellName
    My calculations end up with the following as field names in the dynaset.

    ListName WSO, WSOVD, TSO, TSODV, WST, WSTDV
    FIRST TEAM HB WARNER
    COLDWELL BANKER NRT DP
    C-21 AWARD ANA etc. . . .

    with all of the respective calculations to the right of the office name.
    I'm confused.

    Jim Wright said ". . . store each component as a separate external query and then have your SQL combine the output of all of the queries . . . "

    They are stored as separate queries and they work.
    How do I combine the results as one dynaset?


    Please see the attached WST.jpg.

    Notice that FIRST TEAM HB WARNER is NOT in the ListName Column.

    The WST (We Sold Theirs) in this case the WE is FIRST TEAM HB WARNER should be 7.

    This . . .

    Sum(IIf(Scmls.SellName<>Scmls.ListName,1,0)) AS WST

    is showing 5 . . .

    Any ideas?

    Thanks everyone. Rick
    Attached Thumbnails Attached Thumbnails WST.jpg  

  10. #10
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Rick Schreiber,
    Without the SQL from the three separate queries, and the SQL from the UNION query, it is like I was trying to kill one fly at night with a BB gun. In other words, I don't stand a chance, or it is going to take a very, very long time.

    Please post the SQL for all 4 queries. Also, please use the Post Reply button which gives you a bigger area to type into, and gives you some extra controls at the top of the typing area. Then, use the "#" button which will put [ code] and [ /code] (without the spaces) around selected text. This is for inclosing code so it looks better and is easier to read. Do that for each one of the queries, separately. It will help us help you.

  11. #11
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Spent hours on the Internet today. Okay . . . here is the problem.

    Code:
    SELECT Scmls.ListName,
    
    Sum(IIf(Scmls.ListName=Scmls.SellName,1,0))*2 AS WSO, 
    Sum(IIf(Scmls.ListName=Scmls.SellName,Scmls.SalePrice,0))*2 AS WSODV, 
    This section works fine.
    
    Sum(IIf(Scmls.SellName<>Scmls.ListName,1,0)) AS TSO, 
    Sum(IIf(Scmls.ListName<>Scmls.SellName,Scmls.SalePrice,0)) AS TSODV, 
    This section works fine.
    
    Sum(IIf(Scmls.SellName <> Scmls.ListName,1,0)) AS WST, 
    Sum(iif(Scmls.SellName <> Scmls.ListName, Scmls.SalePrice,0)) AS WSTDV,
    
    Because this section starts with Scmls.SellName, and the SELECT statement only has SELECT Scmls.ListName, it does not calculate correctly. 
    
    If I start with SELECT Scmls.ListName, Scmls.SellName then Scmls.SellName shows up in the Dynaset. This then displays records that should not be displayed and thorws the calculations off.
    
    WSO+TSO+WST AS [Total Transactions],
    Sum([Scmls.SalePrice]) AS [Listing Dollar Volume], 
    WSODV+TSODV+WSTDV AS [Listing and Sales Dollar Volume]
    
    FROM Scmls
    GROUP BY Scmls.ListName
    The problem lies in the SELECT Scmls.ListName statement at the top of the SQL query.

    Whew . . . I'm tired!

    IDEAS ? ? ?

    Thanks every one.

  12. #12
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Rick,
    Having a part of the code, means maybe a part of the answer, but who knows.
    Please post the SQL for all 4 queries. Also, please use the Post Reply button which gives you a bigger area to type into, and gives you some extra controls at the top of the typing area. Then, use the "#" button which will put [ code] and [ /code] (without the spaces) around selected text. This is for inclosing code so it looks better and is easier to read. Do that for each one of the queries, separately. It will help us help you.
    Rather than wishing I knew what was going on, it is a lot easier to have the WHOLE THING, All 4 queries, rather than, this part is ok, but...
    Thanks,

  13. #13
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Only Three Queries

    The combined query is in my last post above.

    Here are the separate queries . . .
    Code:
    SELECT Scmls.ListName, Count([Scmls.ListName])*2 AS WSO, 
    Sum([Scmls.SalePrice])*2 AS WSODV
    FROM Scmls
    GROUP BY Scmls.ListName, Scmls.SellName
    HAVING (((Scmls.SellName) Like [ListName]));
    
    Query 2
    SELECT Scmls.ListName, Count([Scmls.ListName]) AS TSO, 
    Sum([Scmls.SalePrice]) AS TSODV
    FROM Scmls
    WHERE (((Scmls.SellName)<>[ListName]))
    GROUP BY Scmls.ListName;
    
    Query 3
    SELECT Scmls.SellName, Count([Scmls.ListName]) AS WST, Sum([Scmls.SalePrice]) AS WSTDV
    FROM Scmls
    WHERE (((Scmls.ListName)<>[SellName]))
    GROUP BY Scmls.SellName;
    These three queries work flawlessly when executed singularly. They were designed in the datasheet design mode.

    It's the third one WST and WSTDV that fails in my combined query above because of the SELECT statement . . .

    What do you think?

    Thanks much . . . Rick

  14. #14
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Rick, I think I'm starting to understand. You have a listing office, a selling office and sometimes they are the same office? So you want a report of all sales (or whatever) for the offices that both listed and sold the property, then a section for the listing office that did not sell the property, and then a section for the selling office that did not list the property. Is that correct?

    If so, one possibility would be to write 3 separate reports, then make each one of them a sub report in a "master" report. This would give you one report, that would show all three portions.

    Or, are you looking to have all these fields from all three separate queries available on one line of the report? That way being able to show the income from the List/Sell pair, the income from the listings, and then the income from the sales?

    It would help to know how you want the report formatted. Is it one of the above two guesses, or something else?

    thanks,

  15. #15
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Guys,
    Just a line of support on this particular query.

    Following discussions it seems like many of us have the same issue, combining multiple queries from one source. I think if we stick with this posting it will answer many of the style coming in.

    This appears to be a "general" business type requirement these days. The multiple criteria reported for the end-user to see it at a glance and analyse.

    Good posting Rick, Vic your doing a grand job on this one, stick with us if you can!

    Yes, as you guessed, I to have a similar issue!

    Gareth

Posting Permissions

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