Add records to Join Table

B

bymarce

Hi,
I have two tables called Data and tblStandards. Each has an autonumber
primary key called ID. They are joined through a table called
tblDataStandardsJoin with fields DataID and StandardID. My users enter
experimental data for samples in to the data table. Samples in the data
table that match for Property, Method, and DateAssigned fields would also
have the same standards. I have a button for the user to click that I want
to add reords to tblDataStandardsJoin to associate all the standardIDs of the
current DataID with other DataIDs for matching Property, Method, and
DateAssigned. Here is the code I have so far. Can this be done with an
update query or do I need to write a couple nested loops to get all
combinations of DataID and StandardID? The sql statement here returns all
the records with matching Property, Method, and DateAssigned and shows
StandardID and DataID from the join table if it exsists. I hope this is
clear. Thanks for the help!!
Marcie

Dim IDqd As QueryDef
Dim strSQL As String
Dim strQ As String
strQ = """"

Set IDqd = CurrentDb.QueryDefs("qryApplyStandards")
strSQL = "SELECT Data.[ID], Data.TestAssignedDate, Data.[Property],
Data.[TestMethod], " & _
"tblDataStandardsJoin.DataID, tblDataStandardsJoin.StandardID "
& _
"FROM Data LEFT JOIN tblDataStandardsJoin ON Data.ID =
tblDataStandardsJoin.DataID " & _
"WHERE (((Data.TestAssignedDate)=#" & Me.Parent.DateAssigned &
"#) AND ((Data.[Property]) Like " & strQ & Me.Parent.cboProperty & strQ & ")
AND " & _
"((Data.[TestMethod]) Like " & strQ & Me.Parent.cboMethod &
strQ & "));"
IDqd.sql = strSQL
IDqd.Close
DoCmd.OpenQuery "qryApplyStandards"
 
P

Piet Linden

Hi,
  I have two tables called Data and tblStandards.  Each has an autonumber
primary key called ID.  They are joined through a table called
tblDataStandardsJoin with fields DataID and StandardID.  My users enter
experimental data for samples in to the data table.  Samples in the data
table that match for Property, Method, and DateAssigned fields would also
have the same standards.  I have a button for the user to click that I want
to add reords to tblDataStandardsJoin to associate all the standardIDs ofthe
current DataID with other DataIDs for matching Property, Method, and
DateAssigned.  Here is the code I have so far.  Can this be done withan
update query or do I need to write a couple nested loops to get all
combinations of DataID and StandardID?  The sql statement here returns all
the records with matching Property, Method, and DateAssigned and shows
StandardID and DataID from the join table if it exsists.  I hope this is
clear.  Thanks for the help!!
   Marcie

    Dim IDqd As QueryDef
    Dim strSQL As String
    Dim strQ As String
    strQ = """"

    Set IDqd = CurrentDb.QueryDefs("qryApplyStandards")
    strSQL = "SELECT Data.[ID], Data.TestAssignedDate, Data.[Property],
Data.[TestMethod], " & _
             "tblDataStandardsJoin.DataID, tblDataStandardsJoin.StandardID "
& _
             "FROM Data LEFT JOIN tblDataStandardsJoin ON Data.ID =
tblDataStandardsJoin.DataID " & _
             "WHERE (((Data.TestAssignedDate)=#" & Me.Parent.DateAssigned &
"#) AND ((Data.[Property]) Like " & strQ & Me.Parent.cboProperty & strQ &")
AND " & _
             "((Data.[TestMethod]) Like " & strQ & Me.Parent.cboMethod &
strQ & "));"
    IDqd.sql = strSQL
    IDqd.Close
    DoCmd.OpenQuery "qryApplyStandards"
Click to expand...

If all you are trying to do is to populate the join table with values
you would use an append query based on a filtered cartesian product.
IOW, create a select query and add tblData and tblStandards. Do not
join them. Then add the filters you need. Then turn that query into
an append query and insert the data into your join table.
 
B

bymarce

Thanks!! I knew there had to be a more efficient way to do this than what I
had thought of.
Marcie

Piet Linden said:
Hi,
I have two tables called Data and tblStandards. Each has an autonumber
primary key called ID. They are joined through a table called
tblDataStandardsJoin with fields DataID and StandardID. My users enter
experimental data for samples in to the data table. Samples in the data
table that match for Property, Method, and DateAssigned fields would also
have the same standards. I have a button for the user to click that I want
to add reords to tblDataStandardsJoin to associate all the standardIDs of the
current DataID with other DataIDs for matching Property, Method, and
DateAssigned. Here is the code I have so far. Can this be done with an
update query or do I need to write a couple nested loops to get all
combinations of DataID and StandardID? The sql statement here returns all
the records with matching Property, Method, and DateAssigned and shows
StandardID and DataID from the join table if it exsists. I hope this is
clear. Thanks for the help!!
Marcie

Dim IDqd As QueryDef
Dim strSQL As String
Dim strQ As String
strQ = """"

Set IDqd = CurrentDb.QueryDefs("qryApplyStandards")
strSQL = "SELECT Data.[ID], Data.TestAssignedDate, Data.[Property],
Data.[TestMethod], " & _
"tblDataStandardsJoin.DataID, tblDataStandardsJoin.StandardID "
& _
"FROM Data LEFT JOIN tblDataStandardsJoin ON Data.ID =
tblDataStandardsJoin.DataID " & _
"WHERE (((Data.TestAssignedDate)=#" & Me.Parent.DateAssigned &
"#) AND ((Data.[Property]) Like " & strQ & Me.Parent.cboProperty & strQ & ")
AND " & _
"((Data.[TestMethod]) Like " & strQ & Me.Parent.cboMethod &
strQ & "));"
IDqd.sql = strSQL
IDqd.Close
DoCmd.OpenQuery "qryApplyStandards"
Click to expand...

If all you are trying to do is to populate the join table with values
you would use an append query based on a filtered cartesian product.
IOW, create a select query and add tblData and tblStandards. Do not
join them. Then add the filters you need. Then turn that query into
an append query and insert the data into your join table.
Click to expand...
 
B

bymarce

Also this method duplicated the records for the original record so I added a
delete duplicates query (based on Allen Browne's Example) to run after this
one.
Marcie

bymarce said:
Thanks!! I knew there had to be a more efficient way to do this than what I
had thought of.
Marcie

Piet Linden said:
Hi,
I have two tables called Data and tblStandards. Each has an autonumber
primary key called ID. They are joined through a table called
tblDataStandardsJoin with fields DataID and StandardID. My users enter
experimental data for samples in to the data table. Samples in the data
table that match for Property, Method, and DateAssigned fields would also
have the same standards. I have a button for the user to click that I want
to add reords to tblDataStandardsJoin to associate all the standardIDs of the
current DataID with other DataIDs for matching Property, Method, and
DateAssigned. Here is the code I have so far. Can this be done with an
update query or do I need to write a couple nested loops to get all
combinations of DataID and StandardID? The sql statement here returns all
the records with matching Property, Method, and DateAssigned and shows
StandardID and DataID from the join table if it exsists. I hope this is
clear. Thanks for the help!!
Marcie

Dim IDqd As QueryDef
Dim strSQL As String
Dim strQ As String
strQ = """"

Set IDqd = CurrentDb.QueryDefs("qryApplyStandards")
strSQL = "SELECT Data.[ID], Data.TestAssignedDate, Data.[Property],
Data.[TestMethod], " & _
"tblDataStandardsJoin.DataID, tblDataStandardsJoin.StandardID "
& _
"FROM Data LEFT JOIN tblDataStandardsJoin ON Data.ID =
tblDataStandardsJoin.DataID " & _
"WHERE (((Data.TestAssignedDate)=#" & Me.Parent.DateAssigned &
"#) AND ((Data.[Property]) Like " & strQ & Me.Parent.cboProperty & strQ & ")
AND " & _
"((Data.[TestMethod]) Like " & strQ & Me.Parent.cboMethod &
strQ & "));"
IDqd.sql = strSQL
IDqd.Close
DoCmd.OpenQuery "qryApplyStandards"
Click to expand...

If all you are trying to do is to populate the join table with values
you would use an append query based on a filtered cartesian product.
IOW, create a select query and add tblData and tblStandards. Do not
join them. Then add the filters you need. Then turn that query into
an append query and insert the data into your join table.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Not Responding when VBA Code Run 3
Not Responding when VBA code is Run 2
Posting records to a join table 3
Add Records to a Table 2
Add new records to table 3
Unable to add new records in sub-form 2
Add data to specific fields within existing records in a table from another table. 5
how to add a total to a pivot table 7

Top