Adding Information to a Table

  • Thread starter Thread starter Toria
  • Start date Start date
T

Toria

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!!
 
Toria said:
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!!
Click to expand...

SCT and MQ are Tables, right? Not databases.
If you imported all the fields - account numbers, account types and account type descriptions into MQ, why do you have to join them? If all the info is in MQ, why join with SCT?

If all the info is now in MQ, wouldn't renaming SCT to SCT_Old, and renaming MQ to SCT give you what you're looking for?

Just a few thoughts based on the supplied info.
 
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
 
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 said:
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
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!!
Click to expand...
.
Click to expand...
 
Back
Top