Jump to content
  • Advertisement
Sign in to follow this  
rpg_code_master

SQL Trouble

This topic is 4873 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I am having trouble getting an SQL statment to work correctly. Here is what I have at the moment:-

INSERT INTO tblPayedMember ( TrainerID )
SELECT 
tblTrainer.TrainerID
FROM
tblTrainer
WHERE tblImportedExcelData.X = tblTrainer.ID;



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

Share this post


Link to post
Share on other sites
Advertisement
I am using Access.

This is the new SQL statment that I am using:-



INSERT INTO tblPayedMember ( TrainerID )
SELECT tblTrainer.TrainerID
FROM tblTrainer, tblImportedExcelData
WHERE 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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
Here is the statment that I use to fill tblPayedMember with the needed data from tblImportedExcelData:-



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.Gender
FROM tblImportedExcelData
WHERE (((tblImportedExcelData.y)="CURRENT"));




How would I go about merging these two statments?

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!