Inserting Records Into Table from two unrelated tables (1 Viewer)

[DB_Queen]

I am attempting to create a routine to do the following:

1) Create a temporary table with records that match user entered criteria from a form
2) Insert records into a table that includes some data from this temporary table and some data from an existing employee table

I am reasonably new to VBA. I was able to figure out how to create the temporary table using a Make Table query, but I don't know how to do the next step. There are no associated fields in these two tables, so I can't use a JOIN query or the like.

Any advice or guidance you can provide is greatly appreciated!

 

[theDBguy]

Hi. Welcome to AWF!

Inserting records into a table usually only needs the use of an APPEND query, no VBA is required. However, can you please explain what it is you're exactly trying to do? Storing duplicate/redundant data into multiple tables is not really recommended.

 

[Ranman256]

run append queries.

 

[DB_Queen]

Hi. Welcome to AWF!

Inserting records into a table usually only needs the use of an APPEND query, no VBA is required. However, can you please explain what it is you're exactly trying to do? Storing duplicate/redundant data into multiple tables is not really recommended.

Thanks for your reply.

Step by step, here's what I'm trying to do.

1) User enters data onto a form to indicate what schedule they want to work.
2) Create a temporary table (we'll call it tblTemp) that pulls dates from a table (tblWeeks) that match the payroll week selected on the form (I've already accomplished this with a make table query).
3) Insert a record into tblLeave for each date in tblTemp. These records must include the employee's ID# and Team, found in tblEmployeeData.

tblTemp and tblEmployeeData do not have any fields in common, so cannot be joined in an append query.

Hope this clarifies things

 

[theDBguy]

Thanks for your reply.

Step by step, here's what I'm trying to do.

1) User enters data onto a form to indicate what schedule they want to work.
2) Create a temporary table (we'll call it tblTemp) that pulls dates from a table (tblWeeks) that match the payroll week selected on the form (I've already accomplished this with a make table query).
3) Insert a record into tblLeave for each date in tblTemp. These records must include the employee's ID# and Team, found in tblEmployeeData.

tblTemp and tblEmployeeData do not have any fields in common, so cannot be joined in an append query.

Hope this clarifies things

Hi. Thanks for the additional information. That probably makes sense to you, because you are familiar with your database. However, I still fail to see what it is you're trying to do. Maybe if you could post some screenshots or sample data, it might help clear things up a bit.

In the procedure steps you outlined, you started out with saying the employee will specify the days they want to work but ended up with a step to populate a table with the days the employee will not be working (leave). It just seemed like those steps are the opposite of each other.

 

[DB_Queen]

Hi. Thanks for the additional information. That probably makes sense to you, because you are familiar with your database. However, I still fail to see what it is you're trying to do. Maybe if you could post some screenshots or sample data, it might help clear things up a bit.

In the procedure steps you outlined, you started out with saying the employee will specify the days they want to work but ended up with a step to populate a table with the days the employee will not be working (leave). It just seemed like those steps are the opposite of each other.

Maybe we can get at this another way.

I've got my temporary table which has all the dates this employee will be out of the office. That part is already done, I don't need help with it.

What I need to figure out is how to take each date in this table and append it to another table along with information about the employee.

What I can do is have the form generate the info about the employee using DLookups in fields on the form. I just am not sure how to get the info from both of these sources into new records in my table of leave records.

Does this make more sense?

 

[theDBguy]

Maybe we can get at this another way.

I've got my temporary table which has all the dates this employee will be out of the office. That part is already done, I don't need help with it.

What I need to figure out is how to take each date in this table and append it to another table along with information about the employee.

What I can do is have the form generate the info about the employee using DLookups in fields on the form. I just am not sure how to get the info from both of these sources into new records in my table of leave records.

Does this make more sense?

Okay, that does present a different view of your request. To append records into a table, you can use an APPEND query. Append queries can use two different syntax: one for inserting single records, and another for inserting multiple records. Sounds like you want to insert multiple records. If so, this is what it might look like:

INSERT INTO TableName (Field1, Field2) SELECT Field1, Field2 FROM SourceTableName

Now, let's say Field2 is the only one that is really coming from the source table, and Field1 is the employee info you want to get from a form. Then, the above syntax could become like this.

INSERT INTO TableName (Field1, Field2) SELECT Forms!FormName.ControlName, Field2 FROM SourceTableName

However, that begs the question if you're storing redundant data in multiple tables, which is against normalization rules.

 

[DB_Queen]

Okay, that does present a different view of your request. To append records into a table, you can use an APPEND query. Append queries can use two different syntax: one for inserting single records, and another for inserting multiple records. Sounds like you want to insert multiple records. If so, this is what it might look like:

INSERT INTO TableName (Field1, Field2) SELECT Field1, Field2 FROM SourceTableName

Now, let's say Field2 is the only one that is really coming from the source table, and Field1 is the employee info you want to get from a form. Then, the above syntax could become like this.

INSERT INTO TableName (Field1, Field2) SELECT Forms!FormName.ControlName, Field2 FROM SourceTableName

However, that begs the question if you're storing redundant data in multiple tables, which is against normalization rules.

That works well up to one point. Some screen shots attached. Snip 1 is the user form. Snip2 is the records that are pulled in the append query.

For some reason, the "Team Number" info from the user form is not making it into my query results. Nothing jumps out at me that might be causing this. Any ideas?

 

[theDBguy]

That works well up to one point. Some screen shots attached. Snip 1 is the user form. Snip2 is the records that are pulled in the append query.

For some reason, the "Team Number" info from the user form is not making it into my query results. Nothing jumps out at me that might be causing this. Any ideas?

You'll have to show us the SQL statement you used in your query to be able to say why that is happening. Can you please post it? Thanks.

 

[DB_Queen]

You'll have to show us the SQL statement you used in your query to be able to say why that is happening. Can you please post it? Thanks.

Sure, here it is.

INSERT INTO tblLeaveChart ( LeaveDate, EmployeeNumber, [Team Number], Division, TypeofLeave, Hours, SubmittedBy, LeaveForm )
SELECT tblCWSRecordsTEMP.LeaveDate, [Forms]![frmEnterCompressedFullYear]![comboEmpNo] AS EmployeeNumber, [Forms]![frmEnterCompressedFullYear]![txtTeamNo] AS TeamNumber, [Forms]![frmEnterCompressedFullYear]![txtDivName] AS Division, "CMPR" AS TypeofLeave, 0 AS Hours, [Forms]![frmEnterCompressedFullYear]![SubmittedBy] AS SubmittedBy, True AS LeaveForm
FROM tblCWSRecordsTEMP;

 

[theDBguy]

Sure, here it is.

INSERT INTO tblLeaveChart ( LeaveDate, EmployeeNumber, [Team Number], Division, TypeofLeave, Hours, SubmittedBy, LeaveForm )
SELECT tblCWSRecordsTEMP.LeaveDate, [Forms]![frmEnterCompressedFullYear]![comboEmpNo] AS EmployeeNumber, [Forms]![frmEnterCompressedFullYear]![txtTeamNo] AS TeamNumber, [Forms]![frmEnterCompressedFullYear]![txtDivName] AS Division, "CMPR" AS TypeofLeave, 0 AS Hours, [Forms]![frmEnterCompressedFullYear]![SubmittedBy] AS SubmittedBy, True AS LeaveForm
FROM tblCWSRecordsTEMP;

Thanks. Okay, looking back at your form, it looks like Team Number is a dropdown. Can we see its Row Source next?

 

[DB_Queen]

Thanks. Okay, looking back at your form, it looks like Team Number is a dropdown. Can we see its Row Source next?

I actually changed it to be a text box, trying to solve this issue. It doesn't seem to have resolved the problem. There is a DLookup behind the scenes that populates this text box when the employee# is chosen from the dropdown.

The data type on the field is Number, so I set the format of this text box on the form to Number also, but that doesn't seem to help either.

I am flummoxed

 

[theDBguy]

I actually changed it to be a text box, trying to solve this issue. It doesn't seem to have resolved the problem. There is a DLookup behind the scenes that populates this text box when the employee# is chosen from the dropdown.

The data type on the field is Number, so I set the format of this text box on the form to Number also, but that doesn't seem to help either.

I am flummoxed

Just for troubleshooting, remove the DLookup() part and simply enter the number manually in team number just to see if it shows up in the table.

 

[DB_Queen]

Just for troubleshooting, remove the DLookup() part and simply enter the number manually in team number just to see if it shows up in the table.

Still nothing. Even if I manually enter the data in this field, it does not pull it into the query results.

 

[theDBguy]

query results

Query results? What do you mean by that? Look directly in the table, are they not there?

 

[DB_Queen]

Just for troubleshooting, remove the DLookup() part and simply enter the number manually in team number just to see if it shows up in the table.

Query results? What do you mean by that? Look directly in the table, are they not there?

FOUND IT! This is what I put in: TeamNumber: CInt([Forms]![frmEnterCompressedFullYear]![txtTeamNo]) and now it shows up. Google for the win

 

[theDBguy]

FOUND IT! This is what I put in: TeamNumber: CInt([Forms]![frmEnterCompressedFullYear]![txtTeamNo]) and now it shows up. Google for the win

Congratulations! Glad to hear you got it sorted out. Good luck with the rest of your project.