Query Records in Linked table

[Little Penny]

I have a Left Join query to find all the record in the tbl_LogJobData
that do not have link records in the tbl_Check. Tables tbl_LogJobData
and tbl_Check are linked by a primary and foreign (one to Many). How
can I change my query to show me one the records in the tbl_LogJobData
that have only one linked record in the tbl_Check table.

Thanks


Little Penny


My Code


Dim strSQL As String
Dim strWhere As String
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

DoCmd.OpenForm "frmSearchSignBy", , , , , acDialog


If IsLoaded("frmSearchSignBy") Then

strWhere = strWhere & "([tbl_Check.OpLogJobDataID] is Null) AND "
strWhere = strWhere & "([tbl_LogJobData.InsertDate] >= " &
Format([Forms]![frmSearchSignBy]![CboBeginningDate], conJetDate) & ")
AND "
strWhere = strWhere & "([tbl_LogJobData.InsertDate] <= " &
Format([Forms]![frmSearchSignBy]![CboEndingDate], conJetDate) & ") "


strSQL = "SELECT tbl_LogJobData.* FROM tbl_LogJobData " & _
"LEFT JOIN tbl_Check ON " & _
"tbl_LogJobData.OpLogJobDataID = tbl_Check.OpLogJobDataID " &
_
"WHERE " & strWhere




Form_frm_JobDataViewSQL.RecordSource = strSQL




MsgBox "Results have been filtered."

End If
DoCmd.Close acForm, "frmSearchSignBy"
End Sub

 

[John Spencer]

I think I would try a query that looked like the following.

SELECT *
FROM tbl_LogJobData
WHERE OpLogJobDataID in (
SELECT OpLogJobDataID
FROM tbl_Check
GROUP BY OpLogJobDataID
HAVING Count(OplogjobdataId)=1)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Little Penny]

I keep getting Runtime error 3075. Any Idea?

I think I would try a query that looked like the following.

SELECT *
FROM tbl_LogJobData
WHERE OpLogJobDataID in (
SELECT OpLogJobDataID
FROM tbl_Check
GROUP BY OpLogJobDataID
HAVING Count(OplogjobdataId)=1)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I have a Left Join query to find all the record in the tbl_LogJobData
that do not have link records in the tbl_Check. Tables tbl_LogJobData
and tbl_Check are linked by a primary and foreign (one to Many). How
can I change my query to show me one the records in the tbl_LogJobData
that have only one linked record in the tbl_Check table.

Thanks


Little Penny


My Code


Dim strSQL As String
Dim strWhere As String
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

DoCmd.OpenForm "frmSearchSignBy", , , , , acDialog


If IsLoaded("frmSearchSignBy") Then

strWhere = strWhere & "([tbl_Check.OpLogJobDataID] is Null) AND "
strWhere = strWhere & "([tbl_LogJobData.InsertDate] >= " &
Format([Forms]![frmSearchSignBy]![CboBeginningDate], conJetDate) & ")
AND "
strWhere = strWhere & "([tbl_LogJobData.InsertDate] <= " &
Format([Forms]![frmSearchSignBy]![CboEndingDate], conJetDate) & ") "


strSQL = "SELECT tbl_LogJobData.* FROM tbl_LogJobData " & _
"LEFT JOIN tbl_Check ON " & _
"tbl_LogJobData.OpLogJobDataID = tbl_Check.OpLogJobDataID " &
_
"WHERE " & strWhere




Form_frm_JobDataViewSQL.RecordSource = strSQL




MsgBox "Results have been filtered."

End If
DoCmd.Close acForm, "frmSearchSignBy"
End Sub

 

[Little Penny]

I got it to works


Thank you this is great stuff.

I keep getting Runtime error 3075. Any Idea?

I think I would try a query that looked like the following.

SELECT *
FROM tbl_LogJobData
WHERE OpLogJobDataID in (
SELECT OpLogJobDataID
FROM tbl_Check
GROUP BY OpLogJobDataID
HAVING Count(OplogjobdataId)=1)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I have a Left Join query to find all the record in the tbl_LogJobData
that do not have link records in the tbl_Check. Tables tbl_LogJobData
and tbl_Check are linked by a primary and foreign (one to Many). How
can I change my query to show me one the records in the tbl_LogJobData
that have only one linked record in the tbl_Check table.

Thanks


Little Penny


My Code


Dim strSQL As String
Dim strWhere As String
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

DoCmd.OpenForm "frmSearchSignBy", , , , , acDialog


If IsLoaded("frmSearchSignBy") Then

strWhere = strWhere & "([tbl_Check.OpLogJobDataID] is Null) AND "
strWhere = strWhere & "([tbl_LogJobData.InsertDate] >= " &
Format([Forms]![frmSearchSignBy]![CboBeginningDate], conJetDate) & ")
AND "
strWhere = strWhere & "([tbl_LogJobData.InsertDate] <= " &
Format([Forms]![frmSearchSignBy]![CboEndingDate], conJetDate) & ") "


strSQL = "SELECT tbl_LogJobData.* FROM tbl_LogJobData " & _
"LEFT JOIN tbl_Check ON " & _
"tbl_LogJobData.OpLogJobDataID = tbl_Check.OpLogJobDataID " &
_
"WHERE " & strWhere




Form_frm_JobDataViewSQL.RecordSource = strSQL




MsgBox "Results have been filtered."

End If
DoCmd.Close acForm, "frmSearchSignBy"
End Sub

 

[John Spencer]

Not unless you choose to post the code you are using to build the query
string. You can see what you are doing, we cannot.



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County