INSERT INTO tblPayedMember ( TrainerID )
SELECT
tblTrainer.TrainerID
FROM
tblTrainer
WHERE tblImportedExcelData.X = tblTrainer.ID;
SQL Trouble
Author
I am having trouble getting an SQL statment to work correctly.
Here is what I have at the moment:-
This is what I am trying to accomplish:-
I have tblPayedMember, tblImportedExcelData and tblTrainer. tblImportedExcelData contains the trainers ID (tblImportedExcelData.X). tblTrainer contains the trainers ID and the trainers AutoNumber. I need this AutoNumber into tblPayedMember.TrainerID.
The above SQL statment, when run, asks me to enter a value for tblImportedExcelData.X. This is supposed to be known as the data is present in tblImportedExcelData.
Thank you
Author
I am using Access.
This is the new SQL statment that I am using:-
This works fine, as it gets me the required trainer ID. The problem with it though, is that this appends the trainer IDs, and does not modify the current member's trainer IDs.
This is the new SQL statment that I am using:-
INSERT INTO tblPayedMember ( TrainerID )SELECT tblTrainer.TrainerIDFROM tblTrainer, tblImportedExcelDataWHERE tblImportedExcelData.X=tblTrainer.ID;This works fine, as it gets me the required trainer ID. The problem with it though, is that this appends the trainer IDs, and does not modify the current member's trainer IDs.
You can't modify existing rows with an INSERT statement. You would have to use an UPDATE statement, instead. Unfortunately, this means you are going to have to re-think what you are trying to accomplish.
Please listen to this!
Remeber to ALWAYS use your brackets, sometimes Access will work fine without them however once you begin to work with joined tables brackets are vital. After working with some applications using access I ran into many problems forgetting this. So here is an example:
SELECT *
FROM [TABLE]
WHERE [TABLE_ID] = [USER_ID]
this is a suggestion I recommend you practice all the time. This is also most likely your problem!
Remeber to ALWAYS use your brackets, sometimes Access will work fine without them however once you begin to work with joined tables brackets are vital. After working with some applications using access I ran into many problems forgetting this. So here is an example:
SELECT *
FROM [TABLE]
WHERE [TABLE_ID] = [USER_ID]
this is a suggestion I recommend you practice all the time. This is also most likely your problem!
Quote:Original post by Dave Hunt
You can't modify existing rows with an INSERT statement. You would have to use an UPDATE statement, instead. Unfortunately, this means you are going to have to re-think what you are trying to accomplish.
Also this, he is right. When you INSERT you are filling in a table that has no data. When you UPDATE you are replacing your value! Normally the only time you will see INSERT is in blocked or auto scripts generated for protability. UPDATE will be a function DBA's will use quite often, especially in applications.
here is an example:
UPDATE [table_name]
SET [column_name] = [new_value]
WHERE [column_name] = [some_value]
Author
Here is the statment that I use to fill tblPayedMember with the needed data from tblImportedExcelData:-
How would I go about merging these two statments?
INSERT INTO tblPayedMember ( FirstName, LastName, Address, City, Tel, DOB, MembershipType, Gender )SELECT tblImportedExcelData.FirstName, tblImportedExcelData.LastName, tblImportedExcelData.Address, tblImportedExcelData.City, tblImportedExcelData.Tel, tblImportedExcelData.DOB, tblImportedExcelData.MembershipType, tblImportedExcelData.GenderFROM tblImportedExcelDataWHERE (((tblImportedExcelData.y)="CURRENT"));How would I go about merging these two statments?
Before I even dive into this: Do you know you can import Excel into Access? Just making sure, also is this so you can learn SQL? If you want to learn SQL Access honestly is not a good database to use, funny..they use Microsoft SQL and makes it a pain when trying to import data outside the MS Family. Anyways let us know!
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
