Simple Update Query 1

[armstrong722]

Hi,

I have two tables one is team-assignment it consists of
teamid playerid year. This table was started when I originally setup the database. I have another table called Index_player. The index-player has been getting updated as I receive new registrations. Everything is ok so far. Now I needed a list of who needs to tryout this is done comparing the index-player table (the league they signed up for) and the team assignment table (the team and league they were in last year) My forms don't update the team assignment table. I need to add the playerid from the Index-player table to the team assignment table and assign fill in the number 57 for teamid and year which mean
new player in the teams table.

I can't figure out how to build an update query for the team-assignment table. I can't figure out the syntax for comparing the playerid's that don't appear in the team-assignment table but do appear in the player-index table.
I then want to add these playerid numbers to the team-assignment table and 57 in the other two fields.

I'm under the gun here. Volunteer for a little league baseball organization and first time user. Try and be specific because I'm the idiot who came along when they idiot proofed the last gizmo.

thanks
armstrong

 

[mdthornton]

If I understand you correctly you need an insert rather than an update. I've guessed at the column names below but you need something like this:

INSERT INTO team-assignment ( id, team_id, season )
SELECT index-player.id, 57 AS team_id, 2002 as season
FROM Table1 LEFT JOIN team-assignment ON index-player.id = team-assignment.id
WHERE (((team-assignment.id) Is Null));
Best Regards,
Mike

 

[armstrong722]

Mike
thanks for taking the time

here's the statement

INSERT INTO team-assignment (playerid, teamid, year)
SELECT index-player.playerid, 57 AS team_id, 2001 as year
FROM Table1 LEFT JOIN team-assignment ON index-player.playerid = team-assignment.playerid
WHERE (((team-assignment.playerid) Is Null));


I started a new query changed it to an append query and then
stuck the code into the sql editor.

I get a syntax error in INSERT INTO statement.
Do i have to precede this statement with a SELECT statement?

regards

 

[mdthornton]

When I replied earlier I copied the SQL from my test DB
and missed one of the tables when I edited the text. Try this:

INSERT INTO team-assignment (playerid, teamid, year)
SELECT index-player.playerid, 57 AS team_id, 2001 as year
FROM index-player LEFT JOIN team-assignment ON index-player.playerid = team-assignment.playerid
WHERE (((team-assignment.playerid) Is Null));

Just as a tip, leave off the INSERT INTO line initially. This will show you what will be inserted before you actually do it. Alternatively, leave it as it is and select datasheet view - for append queries this shows you the same thing without actually doing the insert.

Best Regards,
Mike

 

[armstrong722]

Sorry,

INSERT INTO [team-assignment] ( playerid, teamid, [year] )
SELECT [index-player].playerid, 57 AS teamid, 2001 AS [year]
FROM [index-player] LEFT JOIN [team-assignment] ON [index-player].playerid = [team-assignment].playerid
WHERE ((([team-assignment].playerid) Is Null));

This is a little better syntax but I am getting no fields to insert with this query

 

[mdthornton]

Your original post stated that you needed to add the playerid to the team-assignment table. If the query is returning no rows then all playerid's must exist in that table already. If this is true and your teamid and year fields are blank then we do need to do an update.... Best Regards,
Mike

 

[armstrong722]

INSERT INTO [team-assignment] ( playerid, teamid, playyear )
SELECT [index-player].[playerid], 57 AS teamid, 2001 AS playyear
FROM [index-player] LEFT JOIN [team-assignment] ON [index-player].[playerid]=[team-assignment].[playerid]
WHERE ((([team-assignment].[playerid]) Is Null));

THAT WORKS!!!
I've been screwing around so much that I forgot that I went to excel and added the dang records and reimported to the team assignment table.

Thank you very much for your excellent assistance!!!!!