|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 5th, 2010, 01:27 AM
posted to microsoft.public.access.queries
|
|||
|
|||
Query Records in Linked table
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 
|
|
#2
March 5th, 2010, 02:22 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Query Records in Linked table
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 wrote: 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
|
|
#3
March 5th, 2010, 11:55 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Query Records in Linked table
I keep getting Runtime error 3075. Any Idea?
On Fri, 05 Mar 2010 09:22:33 -0500, John Spencer wrote: 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 wrote: 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
|
|
#4
March 6th, 2010, 01:04 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Query Records in Linked table
I got it to works Thank you this is great stuff. On Fri, 05 Mar 2010 18:55:55 -0500, Little Penny wrote: I keep getting Runtime error 3075. Any Idea? On Fri, 05 Mar 2010 09:22:33 -0500, John Spencer wrote: 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 wrote: 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
|
|
#5
March 6th, 2010, 02:15 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Query Records in Linked table
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 Little Penny wrote: I keep getting Runtime error 3075. Any Idea? On Fri, 05 Mar 2010 09:22:33 -0500, John Spencer wrote: 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
