help with insert query

[ttuser4]

I tried to make a query which would insert new records into the table 'tblEmployeeTime' - fields 'EmployeeID', 'Date' (specified in query), 'Type'= OUT
for all employees from table 'tblEmployees' whose 'Active' value is 'YES'

'tblEmployeeTime' fields:
EmployeeID Date Type Hours PhaseCode BreakDownCode Rate Machine MachineDistribution
4354485 07-Nov-09 OUT
142 07-Nov-09 OUT
14 07-Nov-09 OUT
4354608 07-Nov-09 OUT

'tblEmployees' fields:
EmployeeID Employee Number LastName FirstName StartDate Active Status Contractor WorkType Rate BirthDate TermReason

Please help!

 

[jedraw]

I'm not sure how you plan to populate your other fields, but this query (based on some assumptions I've made) will insert records into tblEmployeeTime.

INSERT INTO tblEmployeeTime ( EmployeeId, Date_, Type_ )
SELECT tblEmployee.EmployeeId, Date() AS ToDayDt, "OUT" AS x
FROM tblEmployee
WHERE (((tblEmployee.Active)="YES"));

 

[ttuser4]

Thank you; other fields will get default values.

Here is the query I tried to run:
INSERT INTO tblEmployeeTime ( EmployeeId, Date_, Type_ )
SELECT tblEmployees.EmployeeId, Date() AS ToDayDt, "OUT" AS x
FROM tblEmployees
WHERE ((([tblEmployees].[Active])="YES"));


it gives me an error message "... unknown field name 'Date_" ..."
if I change "Date_" to "Date" I get "... syntax error
if I use "[Date]" I get "Data mismatch" error

 

[randy700]

Date is a reserved word in Access and, as such, should NEVER be used as a field name.

What are the actual field names and types you have in your EmployeeTime table?

Randy

 

[dhookom]

This assumes Active is a text field. If it is a Yes/No field, you must replace "YES" with True.

INSERT INTO tblEmployeeTime ( EmployeeId, [Date], [Type] )
SELECT tblEmployees.EmployeeId, Date(), "OUT"
FROM tblEmployees
WHERE [Active]="YES";

Duane
Hook'D on Access
MS Access MVP

 

[ttuser4]

tblEmployeeTime fields/types:
EmployeeID, Number
Date, Date/Time
Type, Text
Hours, Number
PhaseCode, Text
BreakDownCode, Text
Rate, Number
Machine, Text
MachineDistribution, Text

tblEmployees field 'Active' is Yes/No type

This query works OK:
INSERT INTO tblEmployeeTime ( EmployeeId, [Date], Type )
SELECT tblEmployees.EmployeeId, [Enter date as dd/mm/yy] AS ToDayDt, "OUT" AS x
FROM tblEmployees
WHERE ((([tblEmployees].[Active])=true));

Thank you!