How to Write an SQL query to

[R Tanner]

Hi,

I have a table, Tickets, with a field, Manager, and about 4000 rows.
I have another table, called Managers, that has 20 rows, listing every
possible manager in Tickets. I want to write an SQL statement to
select my managers in Tickets and then create an additional field that
is the primary key from Managers. How would I do this? This is the
little bit I have - As you can see, I am not that good with SQL yet.

SELECT [2007 Tickets].Manager
FROM [2007 Tickets]
WHERE [2007 Tickets].Manager = Managers.Manager
ORDER BY Managers.ID;

 

[John W. Vinson]

Hi,

I have a table, Tickets, with a field, Manager, and about 4000 rows.
I have another table, called Managers, that has 20 rows, listing every
possible manager in Tickets. I want to write an SQL statement to
select my managers in Tickets and then create an additional field that
is the primary key from Managers. How would I do this? This is the
little bit I have - As you can see, I am not that good with SQL yet.

SELECT [2007 Tickets].Manager
FROM [2007 Tickets]
WHERE [2007 Tickets].Manager = Managers.Manager
ORDER BY Managers.ID;

You'll need to create a unique Index on the [Manager] field from Managers in
order for the query to be updateable. You could then add an ID field named
ManagerID (Long Integer datatype if Managers.ID is an Autonumber) and run the
following Update query:

UPDATE [2007 Tickets] INNER JOIN Managers
ON [2007 Tickets].[Manager] = [Managers].[Manager]
SET [2007 Tickets].[ManagerID] = [Managers].[ID];

This query joins each record in [2007 Tickets] to the corresponding manager
name in Managers, picks up the ID field, and updates it.

Once you have this done (and all the very likely misspellings and missed
managers fixed), you should then delete the Manager field from [2007 Tickets];
all you need is the ManagerID as a linking field.

One other concern that you may have already heard: storing data such as a year
in a tablename is A Very Bad Idea. You should instead have one Tickets table
with a number field TicketYear (don't use the reserved word Year). You can
easily create queries to select out the tickets for 2007, 2008 or 2021.

 

[Lord Kelvan]

first create an autonum fiel in the managers table and call it
managerid

next create a number field in 2007 tickets and call it managerid

next run the sql

update [2007 tickets],managers
set [2007 tickets].[managerid] = managers.managerid
where [2007 tickets].manager = managers.manager

AS A NOTE dont have a different table for each time period have 1
table and add a date field which will capture the time period

ie
tblticket
ticketid
managerid
ticketdate
otherrelevantticketfields

Regards
Kelvan

 

[R Tanner]

first create an autonum fiel in the managers table and call it
managerid

next create a number field in 2007 tickets and call it managerid

next run the sql

update [2007 tickets],managers
set [2007 tickets].[managerid] = managers.managerid
where [2007 tickets].manager = managers.manager

AS A NOTE dont have a different table for each time period have 1
table and add a date field which will capture the time period

ie
tblticket
ticketid
managerid
ticketdate
otherrelevantticketfields

Regards
Kelvan

Perfect. I love you guys. You are awesome! It worked perfectly.
Now if I can just learn exactly what that SQL statement is doing...

 

[Lord Kelvan]

what it is doing is updating the tickets table managerid field with
the managers managerid field where the names of the managers are the
same in each table

AS A NOTE THE ABOVE WOULD FAIL WITH TWO MANAGERS WITH THE SAME NAME

Regards
Kelvan

 

[R Tanner]

what it is doing is updating the tickets table managerid field with
the managers managerid field where the names of the managers are the
same in each table

AS A NOTE THE ABOVE WOULD FAIL WITH TWO MANAGERS WITH THE SAME NAME

Regards
Kelvan

The managers are not actually managers...They are server alias numbers
and could never be the same...