Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 16th, 2007, 10:24 AM
Authorized User
Join Date: Jan 2005
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Creating an INSERT INTO SQL statement for 2 tables

I have an Employee database, that tracks Employee information and training. I have a form set up, that captures all the Employee Information, with the Employee No being the primary key. In this form, I have a subform, that lists all the training for that Employee (rows of training can be added). This subform information is housed in a MasterMatrix, which includes the Employee No (parent/child link), and the training class, and status of the training.

What I need to do is, create a button in the form that when a new employee is added to the database, it will take the Employee No from the record, and add that and 4 training classes (which are standard classes for new employees), and add them to the MatrixMaster.

I know how to do the SQL statement by just adding taking info from 1 table to the other, but how do I get it from 2 tables? I know that I need a JOIN, but I'm not sure what type I need.

Any help would be great.

1st table - tblEmployee (need EmployeeNo from here) - This is also main form
2nd table - tblTrainingClass (need Training Class, Training Status, WHERE NewEmployeeTraining = yes)

3rd table - tblTrainingMatrixMaster (where info is going to - this is also a subform, with parent/child link to EmployeeNo): - EmployeeNo, Training Class, Training Status

Old April 18th, 2007, 08:52 AM
Friend of Wrox
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts

Hi Sandra,

This looks to be a little tricky...

Looks like what you are looking for, is to click the button, and then have the MatrixMaster automatically populated with 4 separate records.

Since there is not really anything common between Training Class and Employee (Nor should there be!) a join won't cut it in this case.

I'm not sure how familiar you are with VBA, but let me share what my approach would be... There is likely a better solution out there, so if anyone would like to add to this, please feel free...

Public Sub cmdNewEmployeeButton_Click()
Dim rs as ADODB.recordset
Dim cn as ADODB.connection
Dim strSQL as String
Dim empID as String

set cn = Application.CurrentProject.Connection
set rs = New ADODB.recordset

empID = Me.txtEmpID 'This should reference the field that holds your employee ID Number
strSQL = "SELECT [Training Class], [Training Status] FROM tblTrainingClass WHERE [NewEmployeeTraining]='yes'"
rs.Open strSQL

If not(rs.EOF and rs.BOF) Then
    While rs.EOF=False
     strSQL = "INSERT INTO tblTrainingMatrixMaster ([EmployeeNo], [Training Class], [Training Status]) VALUES ("
     strSQL = strSQL & "'" & empID & "', "
     strSQL = strSQL & "'" & rs.fields("Training Class") & "', "
     strSQL = strSQL & "'" & rs.fields("Training Status") & "')"

     DoCmd.RunSQL strSQL

    MsgBox "No New Hire Classes were found"
End If


set rs=Nothing
set cn=Nothing
End Sub

Hope that helps - If not let me know and we can try a different approach.



Similar Threads
Thread Thread Starter Forum Replies Last Post
T-SQL statement causing error with insert/update saf SQL Language 0 September 7th, 2007 04:48 PM
using two-way sql adapter to insert into 2 tables? shazza Biztalk 0 May 14th, 2007 01:18 PM
Help with SQL Insert statement in VBA Code ausmoran Access VBA 1 December 28th, 2006 06:16 PM
Efficient SQL Statement for Large Tables ritag SQL Server 2000 3 April 19th, 2004 11:03 AM
SQL Insert statement with apostrophe xgbnow SQL Language 11 June 18th, 2003 08:08 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.