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

  • Thread starter Thread starter Paul D Goldstein
  • Start date Start date
P

Paul D Goldstein

Guest
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 DGVs).

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 cant get it built correctly under VS2010.

I need to pass the SignupDate to the 1st query (which I cant do). Ive 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 doesnt).

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

Ive read up a little about GetDataBy queries, but I cant 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.

Continue reading...
 
Back
Top