Help with "WHERE" statement from different fields in multiple tabl

  • Thread starter Thread starter Hugh self taught
  • Start date Start date
H

Hugh self taught

Hi All,

I have tables "Types" & "Candidates". Table Types has fields pk &
Designation. Table Candidates has a field "Type" which is the fpk from Types
& a field "accresp".

My Candidates form has a combo on accresp which looks up the value of type
where type = "Employer" (I don't want to use the pk as ref in case the type
table gets changed)

My SQL is
SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)="Employer"))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

What I need help with now is adding to the WHERE when type on my form =
"Employer"

I have an idea but can't seem to get the syntax right.
 
It sounds like you're asking how to use your form control (text box, etc.)
in the Where clause of your query. If that 's correct ,then it would look
like;

SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)=[Forms]![YourForm]![YourTextBox]))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

Using your actual form and control names of course.

Then whatever you enter in the text box on your form will be used in
the Where clause
 
Hi Beetle,

From my combo box (accresp) I need to only see records where the type is
"Employer" but I also only need to see any records "Employer" when the combo
box for "Type" has "Employer" selected. In other words for the current record
if the type="Employer" & I click the "accresp" combo drop down, I only want
to see records where type="Employer". Hope that's not as clear as mud & you
can actually fathom out what I'm trying to do.

Cheers
Hugh

Beetle said:
It sounds like you're asking how to use your form control (text box, etc.)
in the Where clause of your query. If that 's correct ,then it would look
like;

SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)=[Forms]![YourForm]![YourTextBox]))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

Using your actual form and control names of course.

Then whatever you enter in the text box on your form will be used in
the Where clause

--
_________

Sean Bailey


Hugh self taught said:
Hi All,

I have tables "Types" & "Candidates". Table Types has fields pk &
Designation. Table Candidates has a field "Type" which is the fpk from Types
& a field "accresp".

My Candidates form has a combo on accresp which looks up the value of type
where type = "Employer" (I don't want to use the pk as ref in case the type
table gets changed)

My SQL is
SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)="Employer"))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

What I need help with now is adding to the WHERE when type on my form =
"Employer"

I have an idea but can't seem to get the syntax right.
Click to expand...
Click to expand...
 
OK, so assuming the following;

1) Your Type combo box is named cboTypes

2) The Designation field that holds "Employer", etc. is the bound
column of cboTypes

3) cboTypes exists on a form named frmMain

Then the SQL for your accresp combo might look like;

SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)=[Forms]![frmMain]![cboTypes]))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

Then in the After Update event of cboTypes you would requery the
accresp combo box

Private Sub cboTypes_AfterUpdate ()

Me![accresp].Requery

End Sub

--
_________

Sean Bailey


Hugh self taught said:
Hi Beetle,

From my combo box (accresp) I need to only see records where the type is
"Employer" but I also only need to see any records "Employer" when the combo
box for "Type" has "Employer" selected. In other words for the current record
if the type="Employer" & I click the "accresp" combo drop down, I only want
to see records where type="Employer". Hope that's not as clear as mud & you
can actually fathom out what I'm trying to do.

Cheers
Hugh

Beetle said:
It sounds like you're asking how to use your form control (text box, etc.)
in the Where clause of your query. If that 's correct ,then it would look
like;

SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)=[Forms]![YourForm]![YourTextBox]))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

Using your actual form and control names of course.

Then whatever you enter in the text box on your form will be used in
the Where clause

--
_________

Sean Bailey


Hugh self taught said:
Hi All,

I have tables "Types" & "Candidates". Table Types has fields pk &
Designation. Table Candidates has a field "Type" which is the fpk from Types
& a field "accresp".

My Candidates form has a combo on accresp which looks up the value of type
where type = "Employer" (I don't want to use the pk as ref in case the type
table gets changed)

My SQL is
SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)="Employer"))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

What I need help with now is adding to the WHERE when type on my form =
"Employer"

I have an idea but can't seem to get the syntax right.
Click to expand...
Click to expand...
Click to expand...
 
Point 2 in your reply; Therein lies the rub.! The table "Types" has Type_Idx
as the bound pk & Designation as the data. In my Candidates table the
"accresp" field is number linked to Tables pk. Hence my dilema in trying to
formulate the necessary syntax. Any idea on getting around this?

Beetle said:
OK, so assuming the following;

1) Your Type combo box is named cboTypes

2) The Designation field that holds "Employer", etc. is the bound
column of cboTypes

3) cboTypes exists on a form named frmMain

Then the SQL for your accresp combo might look like;

SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)=[Forms]![frmMain]![cboTypes]))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

Then in the After Update event of cboTypes you would requery the
accresp combo box

Private Sub cboTypes_AfterUpdate ()

Me![accresp].Requery

End Sub

--
_________

Sean Bailey


Hugh self taught said:
Hi Beetle,

From my combo box (accresp) I need to only see records where the type is
"Employer" but I also only need to see any records "Employer" when the combo
box for "Type" has "Employer" selected. In other words for the current record
if the type="Employer" & I click the "accresp" combo drop down, I only want
to see records where type="Employer". Hope that's not as clear as mud & you
can actually fathom out what I'm trying to do.

Cheers
Hugh

Beetle said:
It sounds like you're asking how to use your form control (text box, etc.)
in the Where clause of your query. If that 's correct ,then it would look
like;

SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)=[Forms]![YourForm]![YourTextBox]))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

Using your actual form and control names of course.

Then whatever you enter in the text box on your form will be used in
the Where clause

--
_________

Sean Bailey


:

Hi All,

I have tables "Types" & "Candidates". Table Types has fields pk &
Designation. Table Candidates has a field "Type" which is the fpk from Types
& a field "accresp".

My Candidates form has a combo on accresp which looks up the value of type
where type = "Employer" (I don't want to use the pk as ref in case the type
table gets changed)

My SQL is
SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)="Employer"))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

What I need help with now is adding to the WHERE when type on my form =
"Employer"

I have an idea but can't seem to get the syntax right.
Click to expand...
Click to expand...
Click to expand...
 
I've tried changing my sql statement as follows but without any luck.

SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)="Employer") AND
(([Forms]![frmCandidates]![Type]![column(1)])="Employer"))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;


Hugh self taught said:
Point 2 in your reply; Therein lies the rub.! The table "Types" has Type_Idx
as the bound pk & Designation as the data. In my Candidates table the
"accresp" field is number linked to Tables pk. Hence my dilema in trying to
formulate the necessary syntax. Any idea on getting around this?

Beetle said:
OK, so assuming the following;

1) Your Type combo box is named cboTypes

2) The Designation field that holds "Employer", etc. is the bound
column of cboTypes

3) cboTypes exists on a form named frmMain

Then the SQL for your accresp combo might look like;

SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)=[Forms]![frmMain]![cboTypes]))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

Then in the After Update event of cboTypes you would requery the
accresp combo box

Private Sub cboTypes_AfterUpdate ()

Me![accresp].Requery

End Sub

--
_________

Sean Bailey


Hugh self taught said:
Hi Beetle,

From my combo box (accresp) I need to only see records where the type is
"Employer" but I also only need to see any records "Employer" when the combo
box for "Type" has "Employer" selected. In other words for the current record
if the type="Employer" & I click the "accresp" combo drop down, I only want
to see records where type="Employer". Hope that's not as clear as mud & you
can actually fathom out what I'm trying to do.

Cheers
Hugh

:

It sounds like you're asking how to use your form control (text box, etc.)
in the Where clause of your query. If that 's correct ,then it would look
like;

SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)=[Forms]![YourForm]![YourTextBox]))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

Using your actual form and control names of course.

Then whatever you enter in the text box on your form will be used in
the Where clause

--
_________

Sean Bailey


:

Hi All,

I have tables "Types" & "Candidates". Table Types has fields pk &
Designation. Table Candidates has a field "Type" which is the fpk from Types
& a field "accresp".

My Candidates form has a combo on accresp which looks up the value of type
where type = "Employer" (I don't want to use the pk as ref in case the type
table gets changed)

My SQL is
SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)="Employer"))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

What I need help with now is adding to the WHERE when type on my form =
"Employer"

I have an idea but can't seem to get the syntax right.
Click to expand...
Click to expand...
Click to expand...
 
Then just eliminate Designation from your accresp Row Source
altogether, and compare on Types_Idx instead;


SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Types_Idx
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Types_Idx)=[Forms]![frmMain]![cboTypes]))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

--
_________

Sean Bailey


Hugh self taught said:
Point 2 in your reply; Therein lies the rub.! The table "Types" has Type_Idx
as the bound pk & Designation as the data. In my Candidates table the
"accresp" field is number linked to Tables pk. Hence my dilema in trying to
formulate the necessary syntax. Any idea on getting around this?

Beetle said:
OK, so assuming the following;

1) Your Type combo box is named cboTypes

2) The Designation field that holds "Employer", etc. is the bound
column of cboTypes

3) cboTypes exists on a form named frmMain

Then the SQL for your accresp combo might look like;

SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)=[Forms]![frmMain]![cboTypes]))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

Then in the After Update event of cboTypes you would requery the
accresp combo box

Private Sub cboTypes_AfterUpdate ()

Me![accresp].Requery

End Sub

--
_________

Sean Bailey


Hugh self taught said:
Hi Beetle,

From my combo box (accresp) I need to only see records where the type is
"Employer" but I also only need to see any records "Employer" when the combo
box for "Type" has "Employer" selected. In other words for the current record
if the type="Employer" & I click the "accresp" combo drop down, I only want
to see records where type="Employer". Hope that's not as clear as mud & you
can actually fathom out what I'm trying to do.

Cheers
Hugh

:

It sounds like you're asking how to use your form control (text box, etc.)
in the Where clause of your query. If that 's correct ,then it would look
like;

SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)=[Forms]![YourForm]![YourTextBox]))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

Using your actual form and control names of course.

Then whatever you enter in the text box on your form will be used in
the Where clause

--
_________

Sean Bailey


:

Hi All,

I have tables "Types" & "Candidates". Table Types has fields pk &
Designation. Table Candidates has a field "Type" which is the fpk from Types
& a field "accresp".

My Candidates form has a combo on accresp which looks up the value of type
where type = "Employer" (I don't want to use the pk as ref in case the type
table gets changed)

My SQL is
SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)="Employer"))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

What I need help with now is adding to the WHERE when type on my form =
"Employer"

I have an idea but can't seem to get the syntax right.
Click to expand...
Click to expand...
Click to expand...
 
Hi Beetle,

In the interim I've moved the goals a bit.

I need to achieve the following

When cboType = "Employer" then show in cboAccResp only records from Type
that = "ACResp" BUT if cboType = "A/C Responsible" then show in cboAccResp
only records from Type that = "Employer". I'm not sure if that can be done or
not. Programatically maybe?

Beetle said:
Then just eliminate Designation from your accresp Row Source
altogether, and compare on Types_Idx instead;


SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Types_Idx
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Types_Idx)=[Forms]![frmMain]![cboTypes]))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

--
_________

Sean Bailey


Hugh self taught said:
Point 2 in your reply; Therein lies the rub.! The table "Types" has Type_Idx
as the bound pk & Designation as the data. In my Candidates table the
"accresp" field is number linked to Tables pk. Hence my dilema in trying to
formulate the necessary syntax. Any idea on getting around this?

Beetle said:
OK, so assuming the following;

1) Your Type combo box is named cboTypes

2) The Designation field that holds "Employer", etc. is the bound
column of cboTypes

3) cboTypes exists on a form named frmMain

Then the SQL for your accresp combo might look like;

SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)=[Forms]![frmMain]![cboTypes]))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

Then in the After Update event of cboTypes you would requery the
accresp combo box

Private Sub cboTypes_AfterUpdate ()

Me![accresp].Requery

End Sub

--
_________

Sean Bailey


:

Hi Beetle,

From my combo box (accresp) I need to only see records where the type is
"Employer" but I also only need to see any records "Employer" when the combo
box for "Type" has "Employer" selected. In other words for the current record
if the type="Employer" & I click the "accresp" combo drop down, I only want
to see records where type="Employer". Hope that's not as clear as mud & you
can actually fathom out what I'm trying to do.

Cheers
Hugh

:

It sounds like you're asking how to use your form control (text box, etc.)
in the Where clause of your query. If that 's correct ,then it would look
like;

SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)=[Forms]![YourForm]![YourTextBox]))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

Using your actual form and control names of course.

Then whatever you enter in the text box on your form will be used in
the Where clause

--
_________

Sean Bailey


:

Hi All,

I have tables "Types" & "Candidates". Table Types has fields pk &
Designation. Table Candidates has a field "Type" which is the fpk from Types
& a field "accresp".

My Candidates form has a combo on accresp which looks up the value of type
where type = "Employer" (I don't want to use the pk as ref in case the type
table gets changed)

My SQL is
SELECT Candidates.Candidates_Idx, Candidates.FirstName & " " &
Candidates.Surname AS Name, Types.Designation
FROM Candidates INNER JOIN Types ON Candidates.Type = Types.Types_Idx
WHERE (((Types.Designation)="Employer"))
ORDER BY Candidates.FirstName & " " & Candidates.Surname;

What I need help with now is adding to the WHERE when type on my form =
"Employer"

I have an idea but can't seem to get the syntax right.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top