none
Query using a RIGHT OUTER JOIN to a parameter query (was running in Access)

    Question

  • I am converting a form that runs under Access 2007.

    The form uses a Query to fill a subform (now using a DataGridView).

    In the original query for the form, I have a table of entries for volunteers. The volunteers signup for 1-10 slots on a particular date. In order to see all ten slots in the DGV (or the old subform), I have a table for the numbers 1-10 (tblNumbers: RecordKey, NumberLit), and the table of signups (tblSignups: RecordKey, Number, Volunteer, SignupDate).

    I want the following data to appear on the screen:

    DATE: 06/01/2013                06/08/2013                06/15/2013

    01  Volunteer34                   01                            01
    02                                     02                            02  Volunteer15
    03  Volunteer14                   03  Volunteer34          03  Volunteer16
    04                                     04  Volunteer15          04  Volunteer34

    etc...(down to row 10)

    The form actually contains 10 DataGridViews (2 rows of 5 DGV's).

    In the Access Database, I had a query for each Date on the form (txtSignupDate01, txtSignupDate02, etc), so that qrySignupByDate01 was: SELECT * FROM tblSignups WHERE SignupDate=forms!frmSignups!txtSignupDate01.

    Then I had another query which Joined to this as:

    SELECT tblNumbers.RecordKey, tblNumbers.NumberLit, qrySignupByDate01.SignupDate, qrySignupByDate01.Volunteer
    FROM (qrySignupByDate01 RIGHT OUTER JOIN tblNumbers ON qrySignupByDate01.Number=tblNumbers.RecordKey).

    This works great under Access, but I can't get it built correctly under VS2010.

    I need to pass the SignupDate to the 1st query (which I can't do). I've tried building the 1st Query in the Designer, but when I create the 2nd Query in the Designer, it thinks that Jet knows about the 1st query...which it doesn't).

    I know that I can populate the DGV's manually, but if I could at least link them to the correct 'JOINed' query, it would help me out considerably.

    I've read up a little about GetDataBy queries, but I can't seem to find any examples of using them in VB...and even if I did, would this GetDataBy then be able to be used in a 'RIGHT OUTER JOIN' statement?

    Thanks in advance for your time.


    Paul D. Goldstein Forceware Systems, Inc.

    Friday, July 05, 2013 2:18 AM

Answers

  • Hi Paul,

    Access supports also nested select statements. Try:

    SELECT tblNumbers.RecordKey, 
        tblNumbers.NumberLit, 
        SignupByDate.SignupDate, 
        SignupByDate.Volunteer
    FROM (SELECT Number,
            SignupDate,
            Volunteer
        FROM tblSignups 
        WHERE SignupDate=@SignupDate) AS SignupByDate
    RIGHT OUTER JOIN tblNumbers ON SignupByDate.Number=tblNumbers.RecordKey
    

    The former form variable is now @SignupDate and can be passed as OleDbParameter, see Commands and Parameters.

    Regards, Elmar

    Friday, July 05, 2013 4:51 AM

All replies

  • Hi Paul,

    Access supports also nested select statements. Try:

    SELECT tblNumbers.RecordKey, 
        tblNumbers.NumberLit, 
        SignupByDate.SignupDate, 
        SignupByDate.Volunteer
    FROM (SELECT Number,
            SignupDate,
            Volunteer
        FROM tblSignups 
        WHERE SignupDate=@SignupDate) AS SignupByDate
    RIGHT OUTER JOIN tblNumbers ON SignupByDate.Number=tblNumbers.RecordKey
    

    The former form variable is now @SignupDate and can be passed as OleDbParameter, see Commands and Parameters.

    Regards, Elmar

    Friday, July 05, 2013 4:51 AM
  • Hi Elmar,

    That's exactly what I needed to be able to do.

    I always say, "If I don't learn something new everyday, I feel like I didn't accomplish anything."

    Thanks for your help.

    Paul


    Paul D. Goldstein Forceware Systems, Inc.

    Friday, July 05, 2013 1:51 PM