If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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!! |
#2
|
|||
|
|||
Adding Information to a Table
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!! |
#3
|
|||
|
|||
Adding Information to a Table
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!! . |
Thread Tools | |
Display Modes | |
|
|