MS Access 2000 - Inserting records in a new table by joining input from two tables

[tgifgemini]

Ladies and gentlemen,
I have an issue inserting records into my new table
Output table is: "TblProgrammersHours". In this table, RequestID and ProgrammerInitials are combined to form my Primary key - so each has unique value.

input tables are: "TblRequests" and "TblPersonnel"

Note: I was not able to attached my database for clarification purposes because it exceeds the allowable volume of data.

However, you can copy my code into your query design window.
I copied the query below exactly from my query design window and pasted it here. I got this code after I ran an "append" query (Insert query)

After running the append/Insert query, I got no output.
Can someone see if they can pinpoint my mistakes?

INSERT INTO TblProgrammersHours ( RequestID, ProgrammerInitials, CurrProgHours, PrevProgHours, TotalProgHours, Positions )
SELECT TblRequests.RequestID, TblPersonnel.Initials, TblRequests.Prog1Hrs, TblRequests.PrevProg1Hrs, TblRequests.TotalProg1Hrs, 1 AS Positions
FROM TblPersonnel INNER JOIN TblRequests ON TblPersonnel.Name = TblRequests.Personnel1;

Thanks,
tgif.

[jonc]

If you drop the INSERT line and leave it so it is just like this:

SELECT TblRequests.RequestID, TblPersonnel.Initials, TblRequests.Prog1Hrs, TblRequests.PrevProg1Hrs, TblRequests.TotalProg1Hrs, 1 AS Positions
FROM TblPersonnel INNER JOIN TblRequests ON TblPersonnel.Name = TblRequests.Personnel1;

Do you get information out of the database then?

Update: I have just created some tables using the names referred to in the SQL statement and then I entered your SQL into an Access 2007 database (I don't have 2000 installed for direct comparison) and it seems to work fine for me. It updates the tblProgrammersHours without a problem.

Only thing I changed though is the TblPersonnel.Name field to TblPersonnel.Name1 as Access2007 wasn't pleased with me using a "reserved" name (Name is a command if I remember right).

[tgifgemini]

Hi,
No output was produced with this code:

SELECT TblRequests.RequestID, TblPersonnel.Initials, TblRequests.Prog1Hrs, TblRequests.PrevProg1Hrs, TblRequests.TotalProg1Hrs, 1 AS Positions
FROM TblPersonnel INNER JOIN TblRequests ON TblPersonnel.Name = TblRequests.Personnel1;

But I got an output with this code:

SELECT TblRequests.RequestID, TblPersonnel.Initials, TblRequests.Prog1Hrs, TblRequests.PrevProg1Hrs, TblRequests.TotalProg1Hrs, 1 AS Positions
FROM TblPersonnel, TblRequests;

Thanks
tgif.

The above code produced multiple/duplicate records which is not my objective.
In my table TblProgrammersHours, there are two columns that make up the primary key: RequestID and ProgrammerInitials.

[jonc]

TblPersonnel.Name and TblRequests.Personnel1 are both text fields of the same length aswell I presume?

The only thing I can think of to test is to copy the tables into a new database (so you don't mess up the original :)) and rename the field [Name] in TblPersonnel to [Name1] or [ProgrammerName] and try the SQL statement again in that database instead...
Unfortunately I don't have Office 2000 to test my theory out, but Access2007 threw a benny when I tried it as [Name] before.

The reason I think it could be this is because you have omitted the criteria check against the Name field and it now gets data, yet including the field check brings out nothing.

Hope that helps (sorry for making more work for you!)

[tgifgemini]

Okay, I'll do just that and give you guys a response. Creating another database and tables ain't no big thing, so it's not trouble at all.
Thanks.
tgif.