Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Proper Way to Setup Sub Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    (Edited by HansV to break extremely long line in <!t>[pre]<!/t> ... <!t>[/pre]<!/t> part.)

    I have the following query that works OK based on tblSumServe

    I would like to add an additional column at the end of the query called SS2 that would be populated with Yes or No as follows:

    Yes, if tblSumServe.strBatchNo appears in tblSumServe2.strBatchNo
    No, if tblSumServe.strBatchNo does not appear in tblSumServe2.strBatchNo

    tblSumServe2 is an additional table similar in format to tblSumServe

    Thanks, John

    <pre>SELECT tblSumServe.strBatchNo AS [Batch No], Min(tblSumServe.dteSUMDATE) AS Start,
    Max(tblSumServe.dteSUMDATE) AS [End], First(tblSumServe.strREF) AS Reference,
    First(tblSumServe.dteSUMDAY) AS [Check], Min(tblSumServe.strCHECKNO) AS [Start Ck#],
    Max(tblSumServe.strCHECKNO) AS [End Ck#], Count(tblSumServe.idsKeyOfSumServe) AS Claims
    FROM tblSumServe
    WHERE (((tblSumServe.strREF) Not In ("JUNK")) AND ((tblSumServe.strBatchNo) Is Not Null))
    GROUP BY tblSumServe.strBatchNo
    ORDER BY tblSumServe.strBatchNo DESC;
    </pre>


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

    Re: Proper Way to Setup Sub Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    You can use DLookup or DCount for this, for example add this in design view:

    SS2: DCount("*", "tblSumServe2", "strBatchNo = " & Chr(34) & [strBatchNo] & Chr(34)) > 0

    I have assumed that strBatchNo is a text field, since it has prefix str.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Proper Way to Setup Sub Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    If you really want to use a subquery instead of a dlookup, you could do it with the Exists keyword like this:

    <pre>SELECT tblSumServe.strBatchNo AS [Batch No], Min(tblSumServe.dteSUMDATE) AS Start,
    Max(tblSumServe.dteSUMDATE) AS [End], First(tblSumServe.strREF) AS Reference,
    First(tblSumServe.dteSUMDAY) AS [Check], Min(tblSumServe.strCHECKNO) AS [Start Ck#],
    Max(tblSumServe.strCHECKNO) AS [End Ck#], Count(tblSumServe.idsKeyOfSumServe) AS Claims,
    IIf(Exists(SELECT strBatchNo FROM tblSumServe2) = True, "Yes", "No") AS SS2
    FROM tblSumServe
    WHERE (((tblSumServe.strREF) Not In ("JUNK")) AND ((tblSumServe.strBatchNo) Is Not Null))
    GROUP BY tblSumServe.strBatchNo
    ORDER BY tblSumServe.strBatchNo DESC;</pre>

    Charlotte

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proper Way to Setup Sub Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Sweet!

    Ck Prt OK: IIf(DCount("*","tblSumServe2","strBatchNo = " & Chr(34) & [strBatchNo] & Chr(34))>0=0,"No","Yes")

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

    Re: Proper Way to Setup Sub Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    You could, in fact, use the expression

    DCount("*", "tblSumServe2", "strBatchNo = " & Chr(34) & [strBatchNo] & Chr(34)) > 0

    straight, without IIf, and set the Format property to Yes/No. You probably won't be able to select Yes/No from the dropdown list, but you can enter it manually.

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proper Way to Setup Sub Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hi Charlotte

    I ran code as suggested and it returned Yes for all rows.

    Any thoughts?

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Proper Way to Setup Sub Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Sorry, that was aircode. I should have used In, not Exists.

    <pre>SELECT tblSumServe.strBatchNo AS [Batch No], Min(tblSumServe.dteSUMDATE) AS Start,
    Max(tblSumServe.dteSUMDATE) AS [End], First(tblSumServe.strREF) AS Reference,
    First(tblSumServe.dteSUMDAY) AS [Check], Min(tblSumServe.strCHECKNO) AS [Start Ck#],
    Max(tblSumServe.strCHECKNO) AS [End Ck#], Count(tblSumServe.idsKeyOfSumServe) AS Claims,
    IIf([strBatchNo] In (SELECT strBatchNo FROM tblSumServe2), "Yes", "No") AS SS2
    FROM tblSumServeWHERE (((tblSumServe.strREF) Not In ("JUNK")) AND ((tblSumServe.strBatchNo) Is Not Null))
    GROUP BY tblSumServe.strBatchNo
    ORDER BY tblSumServe.strBatchNo DESC;</pre>

    Charlotte

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proper Way to Setup Sub Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hi Charlotte

    Point of interest.

    The DCount method took 1 second to open 596 rows and 1 second to Ctrl+End to end of list

    The In method took 4 seconds to open 596 rows and 20 seconds to Ctrl+End to end of list

    Thanks, John

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Proper Way to Setup Sub Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I didn't promise it was faster! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> You'll find that subqueries are NOT necessarily the fastest way because of the order of processing in the query engine, although on my machine working with nearly 900 records in XP, I didn't see that delay, so mileage may vary. It's often faster to use a virtual table like this:

    <pre>SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate,
    IIf(IsNull([V].[OrderID]),"No","Yes") AS Temp
    FROM Orders LEFT JOIN
    (SELECT DISTINCT OrderID FROM [Order Details]) AS V
    ON Orders.OrderID = V.OrderID;</pre>


    SQL has a lot of different approaches and they aren't created equal. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proper Way to Setup Sub Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hi Charlotte

    Much better.

    I was able to come up with the following in Query SQL View based on your example to build the SQL and it works fine.

    How would I use Query Design View to build virtual tables?

    Thanks, John

    <pre>SELECT tblSumServe.strBatchNo AS [Batch No], Min(tblSumServe.dteSUMDATE) AS Start,
    Max(tblSumServe.dteSUMDATE) AS [End], First(tblSumServe.strREF) AS Reference,
    First(tblSumServe.dteSUMDAY) AS [Check], Min(tblSumServe.strCHECKNO) AS [Start Ck#],
    Max(tblSumServe.strCHECKNO) AS [End Ck#], Count(tblSumServe.idsKeyOfSumServe) AS Claims,
    IIf(IsNull([V].[strBatchNo]),"No","Yes") AS [Ck Prt OK]
    FROM tblSumServe LEFT JOIN [SELECT DISTINCT strBatchNo FROM [tblSumServe2]]. AS
    V ON tblSumServe.strBatchNo = V.strBatchNo
    WHERE (((tblSumServe.strREF) Not In ("JUNK")) AND ((tblSumServe.strBatchNo) Is Not Null))
    GROUP BY tblSumServe.strBatchNo, IIf(IsNull([V].[strBatchNo]),"No","Yes")
    ORDER BY tblSumServe.strBatchNo DESC;
    </pre>


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

    Re: Proper Way to Setup Sub Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    You must type the SQL statement in SQL view. If you then switch to design view, you'll only see the alias V for the virtual table. Query Design View does not support things such as

    - Union queries
    - Virtual tables
    - Non-standard joins

Posting Permissions

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