Visual Basic 4 / 5 / 6 RSS Visual Basic 4 / 5 / 6 RSS

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

Reply

•
•
Join Date: Jul 2007
Posts: 113
Reputation: tgifgemini is an unknown quantity at this point 
Solved Threads: 0
tgifgemini tgifgemini is offline Offline
Junior Poster

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

 
0
  #1
Jan 28th, 2008


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?

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. INSERT INTO TblProgrammersHours ( RequestID, ProgrammerInitials, CurrProgHours, PrevProgHours, TotalProgHours, Positions )
  2. SELECT TblRequests.RequestID, TblPersonnel.Initials, TblRequests.Prog1Hrs, TblRequests.PrevProg1Hrs, TblRequests.TotalProg1Hrs, 1 AS Positions
  3. FROM TblPersonnel INNER JOIN TblRequests ON TblPersonnel.Name = TblRequests.Personnel1;
Thanks,
tgif.
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2007
Posts: 140
Reputation: jonifen is an unknown quantity at this point 
Solved Threads: 15
jonifen jonifen is offline Offline
Junior Poster

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

 
0
  #2
Jan 28th, 2008
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).
Last edited by jonifen; Jan 28th, 2008 at 6:10 pm. Reason: Just updating my post...
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2007
Posts: 113
Reputation: tgifgemini is an unknown quantity at this point 
Solved Threads: 0
tgifgemini tgifgemini is offline Offline
Junior Poster

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

 
0
  #3
Jan 28th, 2008
Hi,
No output was produced with this code:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. SELECT TblRequests.RequestID, TblPersonnel.Initials, TblRequests.Prog1Hrs, TblRequests.PrevProg1Hrs, TblRequests.TotalProg1Hrs, 1 AS Positions
  2. FROM TblPersonnel INNER JOIN TblRequests ON TblPersonnel.Name = TblRequests.Personnel1;

But I got an output with this code:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. SELECT TblRequests.RequestID, TblPersonnel.Initials, TblRequests.Prog1Hrs, TblRequests.PrevProg1Hrs, TblRequests.TotalProg1Hrs, 1 AS Positions
  2. 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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2007
Posts: 140
Reputation: jonifen is an unknown quantity at this point 
Solved Threads: 15
jonifen jonifen is offline Offline
Junior Poster

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

 
0
  #4
Jan 28th, 2008
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!)
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2007
Posts: 113
Reputation: tgifgemini is an unknown quantity at this point 
Solved Threads: 0
tgifgemini tgifgemini is offline Offline
Junior Poster

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

 
0
  #5
Jan 29th, 2008
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.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Other Threads in the Visual Basic 4 / 5 / 6 Forum
Thread Tools Search this Thread



* 6 429 2007 access activex add age application basic beginner birth bmp calculator cd cells.find click client code college component connection connectionproblemusingvb6usingoledb copy creat ctrl+f data database datareport date delete dissertations dissertationthesis dissertationtopic edit error excel excelmacro file filename form hardware header iamthwee image inboxinvb internetfiledownload keypress label listbox listview liveperson login looping machine microsoft movingranges number objectinsert open oracle password prime program prompt range-objects readfile reading record refresh remotesqlserverdatabase report save search sendbyte sites sort sql sql2008 sqlserver subroutine tags textbox time urldownloadtofile vb vb6 vb6.0 vba visual visualbasic visualbasic6 web window windows
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC