Adding Information to a Table

Here's my scenario:
I have a table called SCT. It has about 150,00 records with many fields. I
took all the account numbers from this table and looked up their "account
types" and "account type descriptions" in another system. I imported the
account numbers, account types and account type descriptions back in Access
in a table called MQ. I can do a query joining the two tables on account
number but I really need those fields (account type and account type
description) in the main database, SCT. I'm not sure how I would do this.
Any help is much appreciated! I hope I explained this clearly enough! Thank
you!!

Add two fields to SCT to contain the new data.
Use an update query to populate the new fields that looks like the following

UPDATE SCT INNER JOIN MQ
ON SCT.[AccountNumber] = [MQ].[AccountNumber]
SET SCT.[AccountType] = [MQ].[AccountType]
, SCT.[AccountDescription] = [MQ].[AccountDescription]

If AccountDescription is always the same for an AccountType then you would be
better off just storing AccountType in the SCT table and adding a table with
the unique values for AccountType and AccountDescription that you use (in a
join) when you need the AccountDescription.

If you can only build queries in query design view
== Create a new query
== Add both tables
== Join Account number to Account number (Drag from field to field)
== Add SCT AccountType and AccountDescription fields to the list of fields
== Select Query: Update from the menu
== Enter the following under AccountType in the update to box
[MQ].[AccountType]
== Enter the following under AccountDescription in the update to box
[MQ].[AccountDescription]

Obviously you need to use the names of your fields and tables

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Toria wrote:
Here's my scenario:
I have a table called SCT. It has about 150,00 records with many fields. I
took all the account numbers from this table and looked up their "account
types" and "account type descriptions" in another system. I imported the
account numbers, account types and account type descriptions back in Access
in a table called MQ. I can do a query joining the two tables on account
number but I really need those fields (account type and account type
description) in the main database, SCT. I'm not sure how I would do this.
Any help is much appreciated! I hope I explained this clearly enough! Thank
you!!

Thanks, John!! This did the trick. I've never known how to do update
queries, so I've add this to my ongoing notes.

"John Spencer" wrote:

Add two fields to SCT to contain the new data.
Use an update query to populate the new fields that looks like the following

UPDATE SCT INNER JOIN MQ
ON SCT.[AccountNumber] = [MQ].[AccountNumber]
SET SCT.[AccountType] = [MQ].[AccountType]
, SCT.[AccountDescription] = [MQ].[AccountDescription]

If AccountDescription is always the same for an AccountType then you would be
better off just storing AccountType in the SCT table and adding a table with
the unique values for AccountType and AccountDescription that you use (in a
join) when you need the AccountDescription.

If you can only build queries in query design view
== Create a new query
== Add both tables
== Join Account number to Account number (Drag from field to field)
== Add SCT AccountType and AccountDescription fields to the list of fields
== Select Query: Update from the menu
== Enter the following under AccountType in the update to box
[MQ].[AccountType]
== Enter the following under AccountDescription in the update to box
[MQ].[AccountDescription]

Obviously you need to use the names of your fields and tables

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Toria wrote:
Here's my scenario:
I have a table called SCT. It has about 150,00 records with many fields. I
took all the account numbers from this table and looked up their "account
types" and "account type descriptions" in another system. I imported the
account numbers, account types and account type descriptions back in Access
in a table called MQ. I can do a query joining the two tables on account
number but I really need those fields (account type and account type
description) in the main database, SCT. I'm not sure how I would do this.
Any help is much appreciated! I hope I explained this clearly enough! Thank
you!!
.