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!!
0
Utf
11/18/2009 2:12:01 AM
access.queries 6343 articles. 1 followers. Follow

2 Replies
550 Views

Similar Articles

[PageSpeed] 41

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!!
0
John
11/18/2009 12:47:43 PM
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!!
> .
> 
0
Utf
11/18/2009 6:17:02 PM
Reply:

Similar Artilces:

Adding to existing code (Clarification of earlier post)
After rereading my earlier post, I find that I wasn't very clear. I'll try again. My existing code works to give me the sum of all Weeks Service where the Department Name is equal to the Current Department Name and I do want to keep that code. In addition to that code though, I need to add, I'm guessing, an "IF Statement" that will provide me with the sum of ALL Weeks Service of ALL Department Name IF the Current Department Name is equal to "Reserves". Example: Department Name Weeks Service Millwright ...

Pivot Table
I've this problem, when I drag the fields into the "Data" area, it will show as "Count of Q1 Results". But what I actually want is "Sum of Q1 Results". I would have to manually go to field setting and reconfigure from count to sum. It happens for all the fields I drag into the "Data" area. Is there any way around this? Thanks. If there are blank cells, or cells with text, in the column, Excel will default to the Count function when the field is added to the data area. If the column contains only numbers, it should default to Sum. Derrick wr...

Adding VAT fractions
Hello All, I am using a spreadsheet to calculate VAT. Format is set to two decimal places, have tried number, currency and accounting methods. Formula is entered as eg: =A8*7/47 (VAT fraction is 7/47). Answers are displayed correctly to two decimal places but when adding the column, Excel adds answer to formula NOT answers displayed in cells. So, although technically correct, answer is usually a penny or two out. I need it to add what is displayed. Anyone know how to do this? Thanks Kirsty. Scott, When calculating the VAT, round it to 2 dec places, then it should sum correctly, that is ...

How to paste a cells row from Excel to a PowerPoint 2003 Table row?
Hello! How to paste a row of cells from Excel to a PowerPoint 2003 Table row? PowerPoint 2003 pastes all the row cells values in every cell in the row in the PowerPoint table if I select the row in the PP table before the pasting. PP pastes the row as an overlapping column if I place cursor in the first cell of the target row before pasting. Best regards, Dima +7 9163876746 +7 9035093892 ...

Create Pivot Table Reports
I just finished installing Analysis Cube on the server, everything seemed working fine until when I try to create the Pivot Table Report (Tools->Analysis Cubes->Create Pivot Table Reprots). After I called up the pre-defined 'Definition ID' and click on the Excel icon, a message popped up saying " The WHTemplate.XLT file was not found." Anybody have any idea what that is, and how to resolve it?? Thanks. 1.) There are two pieces of software. Did you install both the server piece, and the client piece? 2.) Is Excel installed on the machine, on which you'r...

I would like to export the data from a drop-down list to a table
I have a form with several drop-down lists, I need to have the information in these lists in another document/spread sheet. Is there an easy way to copy the data in these lists to another location? The long way would be to re-type all of it. ...

Importing a table from Access query
What is the quickest and easiest way to do the above? Thanks in advance. Hi The way I usually do this is run the select query, click on the top-right box (which selects all records) and use Ctrl+C to copy and Ctrl+V to paste into my workbook. An alternative is to right-click on the query in the Database Window and left-click on Export. In the Save As dialog box, select ..xls type. -- Andy. "Trish" <Trish@discussions.microsoft.com> wrote in message news:6BF3DE22-6590-4CAD-9EE1-FC978A3BB63B@microsoft.com... > What is the quickest and easiest way to do the above? >...

Write conflict error with ODBC link table
Hi, I have migrate my back end access tables to SQL Server. While editing data (ODBC link) from form, I receive Wirte conflict error 'The record has been changed by other user... Copying the change to the clipboard...'. The error allow me either copy the info to clipboard or drop change. In this case, how can I save my work to the table here? SF � "SF" <xyz@online.com.kh> ������ ��� ������ news:#F7F7OtcIHA.5160@TK2MSFTNGP05.phx.gbl... > Hi, > > I have migrate my back end access tables to SQL Server. While editing data > (ODBC link) from form, I...

adding a zero in front of number
how do you add a zero in front of other numbers, I am using item numbers and most start with zero, just shows whole numbers when I enter. example 095421 when I enter shows 95421. help. Hi When the number must remain numeric data, then format the cell as Custom "00000" (the number of 0's determines to which length is the entry padded). When you want the number to be converted to string, then use the formula (in my example the original number resides in cell A1) =TEXT(A1,"00000") (again, the number of 0's in format string determines the length of padding) Arvi ...

Adding data to a list
I have a list of data that I want to add to or subtract from and I would like to be able to click on a button which would bring up a text box where I could insert the new data then sort the new list. Thanks, Mike Take a look at data>form -- Regards, Peo Sjoblom "Mike" <mike@ehb-docks.fsbusiness.co.uk> wrote in message news:bultp0$8ee$1@news8.svr.pol.co.uk... > I have a list of data that I want to add to or subtract from and I would > like to be able to click on a button which would bring up a text box where I > could insert the new data then sort the ne...

adding a line to an existing chart
Hi, I have a chart with bars and two lines but I want to add another line on top of what I already have...can I do this if so, how? I have tried many things but it is not showing up. Please provide as much detail as possible, as I am fairly new to this. JudyT Select the data for the new line (say F2:F20) Click the Copy tool Click the chart Use menu command Edit | Past Special; specify New Series; click OK Suppose the data is G2:F20 with new x-values in column F Select the data for the new line (say G2:F20) Click the Copy tool Click the chart Use menu command Edit | Past Special; speci...

Updating Internet Information #3
I have Microsoft Money 99 which I use mainly to do my yearly taxes. Until this year, I have been able to update the tax worksheet by downloading (clicking on "Update Internet Information"). When I click this now, I get a dialog box: Money 99 Online Connection Summary, indicating "Product Information. Money was unable to complete the operation. The internet, banking or brokerage server you were trying to contact encountered an error. Please try your call again later. (SMC)" I have tried this several times and received the same message in the last month. Can ...

Need a formula that tags one table based 2 columns in each table
I have two tables on one worksheet, Table A and Table B. Each table contains two column with X and Y coordinates, all values are numerical. The coordinate system is irrelevant. I need to "tag" all XY coordinates in Table B that have a matching XY coordinate in Table A. If Table A coordinates are in columns A and B, and Table B coordinates are in columns C and D, then I want to place the text "hit" in column E next to each coordinate pair from columns C and D that match a coordinate pair in columns A and B. The ranges for the respective tables have been named ACOORD and...

Adding Toolbar to CDialog
Hello, I have a CMenu object that is attached to my dialog at the top and I am trying to add a Toolbar under the menu. This is how I am trying to add a toolbar to my dialog box: int CSvg::OnCreate(LPCREATESTRUCT lpCreateStruct) { if (CDialog::OnCreate(lpCreateStruct) == -1) return -1; //This adds CWebBrowser2 object and works OK if ( ! m_explore.Create( NULL, WS_CHILD | WS_VISIBLE, CRect(), this, IDC_EXPLORER3 ) ) return -1; //this does NOT add toolbar and it does NOT work OK if (!m_toolbar.Create(this, WS_CHILD | WS_VISIBLE | CBRS_SIZE_DYNAMIC | CBRS_TOP | CBRS_TOOLTIPS | CBR...

Fetch user login information using JScript
Hi.. I am new to MS CRM. I am trying add custom HTML pages to the CRM which requires the user login information. I would like to know how to go about this and solve my problem Any help regarding this would be appreciated NN, I am not 100% sure of this but could you not just have the page use integrated authentication? Jeff Loucks 888-474-2237 MVP "NN" <NN@discussions.microsoft.com> wrote in message news:0E6BA8D5-2F2D-492B-8CB8-84284D04FF59@microsoft.com... > Hi.. > > I am new to MS CRM. I am trying add custom HTML pages to the CRM which > requires the user...

Adding multiple tables in one report
I am trying to customize the default report Daily Detailed Sales with Tax. What i need to do is add the Tender Type (Credit card / cash / check) as another column in the report. So far i have found out: i need to add a column i need to import the TenderEntry table for the data I need to find out: How to import another table I have tried to import the table using sql UNION function, but that wont work for me either. any help would be greatly appriciated. Thank You Hi ED, The things make sense to me regarding the adding tables and fields which you can do and customized the .grp file. ...

Pivot table help I think !
Hi All, I have a report I need to create which goes as follows. I have a data list of around 56k records. The rows contain this; category 1, category 2, category 3, call id and Date I've created a pivot table with Cat1, cat2, cat 3 on the row area, Count of Call ID in the data area and date in the column area (this is grouped by Month). I'm looking at the months of Mar, Apr and May. I want to sort it, in descending order, by the difference of 'Count of Call ID' there is between Mar and May. If I cannot perform the calculation in the pivot table, is there a way of ungroupi...

"How do I get rid of old records in an Pivot Table?
The spreadsheet gets copied each month to a new file and the data cleared out, BUT the selecetion in the Pivot Table still holds the data from Previous spreadsheets Debra Dalgleish has some techniques at: http://www.contextures.com/xlPivot04.html AyPee wrote: > > The spreadsheet gets copied each month to a new file and the data cleared > out, BUT the selecetion in the Pivot Table still holds the data from Previous > spreadsheets -- Dave Peterson The web-link was very helpful, thank you. I was about to reprogram and build my pivot tables to get rid of the obsolete item...

Contacts from AD in GAL
Hi, I want to publish the contacts I have inserted independently into my AD via the (or a) GAL. The first I noted, was that there seems to be no way to bypass the filter rule (mailNickname=*) So I simply added a mailNickname to my contacts, only to find out that this fires the recipient policy fires and the e-mail address is replaced by a generated address. This obviously renders the entire contact useless. I can see no way to exclude contacts with a mailNickname from this recipient policy. Any suggestions? Help in advance, Wiebe Cnossen ...

Pivot Table toolbar
Every time I edit a pivot table (Excel 2000)the toolbar launches itself and I have to then re-anchor it manually - this is very irritating! Is there any way to stop this happening? "GeoffS" <crammond-smith@tiscali.co.uk> wrote in message news:39d201c355a8$a7f788b0$a001280a@phx.gbl... > Every time I edit a pivot table (Excel 2000)the toolbar > launches itself and I have to then re-anchor it manually - > this is very irritating! > > Is there any way to stop this happening? I use Excel97 but it may be the same. You can have the toolbar on all the time, positio...

Force data type from Text to Memo in a simple Make Table Query
I'm concatenating fields of various data types that upon completion sometimes reaches around 500 characters. Not huge, but larger than the Text limitation to which is what Access 2007 of course converts this. How can I force the data type to be Memo while I'm in the query so the resulting table displays all the data without any truncation. -- TIA Eric S UPRR I don't believe you can. I think you'll have to create the table first, and then append to it. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "E...

Adding Items Through QSRules
Any advice on using QSrules session object to add item to POS screen? Code snippets would be very much appreciated. Thank you, -- Rob Public Function Process(mySession As Object) As Boolean Dim objAdded As Object Dim id as Long Dim ilc as String objAdded = mySession.Transaction.Entries.Add(id, ilc, 1, 0, False, 0) SendKeys ("{Down}") 'I throw this in there because I haven't figured out how to refresh the transaction screen and also because it puts the focus on the next transaction line. Maybe someone can pipe in with a solution Process = True End...

table of contents
I am trying to build a table of contents from the headings in my employee handbook. I have heard that this is possible, and if I change the content of the handbook, the page numbers, etc. will automatically update. What is the process to accomplish this? I would appreciate any insight given. Thanks! ...

Pivot tabels -- Incorrect sorting of specific value in pivot table
A message was posted yesterday that has not shown up so this is a second attmpt. I have a pivot table that consistently places a value at the top of the sorted llist (bottom if decending) even though that value should be in the middle of the list. A sample of the values are ALC, SNO, CET, ESU, TEL, STR, JUN, NKA, NTL, blank. The JUN value is always at the top. Any value placed in the field that begins with J shows at the top. If the value is changed to any other letter, it sorts correctly. The data has been reentered at the source, the query checked, and the downloaded data chec...

adding number in next cell
Every month I have to change the sales amount in my worksheet. I would like this amount to be added automatically to the total for the year sales amount in the next cell. Is this possible? Of course it's possible! If you would like a suggested formula: =SUM($A$1:A1) Now, if this doesn't work for you, how would I know that, with all the information that you gave us? -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! -----------------------------...