reading from one table to populate a column in another table

Table A has customer code and customer name fields
Table B has customer name and phone number fields

I want to read through table B, look up the customer name in table A,
if it is there, copy customer code to a customer code column in Table
B that I will create.  I could use a select statement to do this but
sometimes the entire name may not match but the first 15 or 20
characters will.

by the way, I have bought lots of Access books and have yet to see
code similar to this request where you read through one table and
possibly populate another.  I have programmed in Visual Foxpro and
this was fairly easy to do.

Any recommendations for advanced access/vba books will be much
appreciated.

Thanks in advance

Tom

0
TomC
4/12/2007 11:41:37 AM
access 16762 articles. 2 followers. Follow

3 Replies
824 Views

Similar Articles

[PageSpeed] 3

Tom,
   Your customer table should have a unique key field value for each record, that 
identifies just that one customer.  For ex. a CustID (ex. an autonumber)
   Your Phone table should also have a a CustID field too (numeric, long) used to relate 
One Customer to Many Phones
   When you establish a One to Many relationship between the two tables, via the CustID, 
the CustID updating in the Phone table is automatically handled by Access.

    Say you have a Main form, based on Customers, with all the "One" Customer info, and a 
continuous subform on that same form, based on Phones with the "Many" info.
    The link between the two forms (Parent/Child) would be CustID.
    Whenever a phone number is added to say... Bob Smith's phone list, Access 
automagically applies his CustID to each and every Phone record.  That's how Smith and his 
phone numbers are stay "connected"

    You do not need to handle the integrity between the One table vs. the Many table... 
Access does that.
-- 
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

"TomC" <thomascaylor@yahoo.com> wrote in message 
news:1176378097.464433.307310@y5g2000hsa.googlegroups.com...
> Table A has customer code and customer name fields
> Table B has customer name and phone number fields
>
> I want to read through table B, look up the customer name in table A,
> if it is there, copy customer code to a customer code column in Table
> B that I will create.  I could use a select statement to do this but
> sometimes the entire name may not match but the first 15 or 20
> characters will.
>
> by the way, I have bought lots of Access books and have yet to see
> code similar to this request where you read through one table and
> possibly populate another.  I have programmed in Visual Foxpro and
> this was fairly easy to do.
>
> Any recommendations for advanced access/vba books will be much
> appreciated.
>
> Thanks in advance
>
> Tom
> 


0
Al
4/12/2007 12:57:31 PM
On Apr 12, 7:20 am, Ken Sheridan
<KenSheri...@discussions.microsoft.com> wrote:
> Lets assume that you can guarantee a match on the first 15 characters, or the
> whole name if the name is shorter, and that this will uniquely identify each
> name.  You can execute an 'update query', e.g.
>
> UPDATE TableB INNER JOIN TableA
> ON LEFT(TableB.CustomerName,15) = LEFT(TableA.CustomerName,15)
> SET TableB.CustomerCode = TableA.CustomerCode;
>
> Remember that table or column names which contain spaces or other special
> characters must be wrapped in brackets [like this].
>
> Set operations like the above are better than iterating through the table in
> code, but as far as books on advanced VBA programming in Access is concerned
> then take a look at The Access Developer's Handbook by Litwin, Getz and
> Gunderloy (published by Sybex).  I think the last edition was for Access
> 2002, but that's no real drawback.
>
> Ken Sheridan
> Stafford, England
>
>
>
> "TomC" wrote:
> > Table A has customer code and customer name fields
> > Table B has customer name and phone number fields
>
> > I want to read through table B, look up the customer name in table A,
> > if it is there, copy customer code to a customer code column in Table
> > B that I will create.  I could use a select statement to do this but
> > sometimes the entire name may not match but the first 15 or 20
> > characters will.
>
> > by the way, I have bought lots of Access books and have yet to see
> > code similar to this request where you read through one table and
> > possibly populate another.  I have programmed in Visual Foxpro and
> > this was fairly easy to do.
>
> > Any recommendations for advanced access/vba books will be much
> > appreciated.
>
> > Thanks in advance
>
> > Tom- Hide quoted text -
>
> - Show quoted text -

Your suggestion worked perfectly.

Thank you!

0
TomC
4/12/2007 8:55:33 PM
"TomC" <thomascaylor@yahoo.com> wrote in message 
news:1176378097.464433.307310@y5g2000hsa.googlegroups.com...
> Table A has customer code and customer name fields
> Table B has customer name and phone number fields
>
> I want to read through table B, look up the customer name in table A,
> if it is there, copy customer code to a customer code column in Table
> B that I will create.  I could use a select statement to do this but
> sometimes the entire name may not match but the first 15 or 20
> characters will.
>
> by the way, I have bought lots of Access books and have yet to see
> code similar to this request where you read through one table and
> possibly populate another.  I have programmed in Visual Foxpro and
> this was fairly easy to do.
>
> Any recommendations for advanced access/vba books will be much
> appreciated.
>
> Thanks in advance
>
> Tom
> 

0
i_takeuti
4/12/2007 9:49:23 PM
Reply:

Similar Artilces:

Reading .wks file
Greetings...according to the Excel "help" file, as well as the file extension listing, I *should* be able to read an *.wks file, but Excel insists that it cannot. I am pretty sure the file was created in Microsoft Works. Is there a converter somewhere that I can download/install? Cheers - S2 Excel can read Works 2.0, not later. You have to save them in Works as excel files or Works 2.0 or get a commercial converter. -- Regards, Peo Sjoblom "Skip Stocks" <anonymous@discussions.microsoft.com> wrote in message news:AFC110E0-641D-4D87-9464-B930CC41CF02@microsoft....

Exch 5.5
I have set up the IMC's Message Filtering to disable a previous employee from sending mail from his home email address in to our server. I am now being asked to block internal users with accounts on our server from sending out to this users home email address. Is there a way to block outbound email to one email address? We are running Sybari Antigen on our server but I can't see of a way to do this with that application either. ...

Weekly report and multiple column counts
I need to generate a weekly of every Friday and give counts for multiple fields. So it will look like: Week # of Open # of Closed # Pending 7/7/07 9 2 5 7/13/07 17 0 1 7/20/07 12 3 6 7/27/07 20 0 1 How would I set this up? Originally I was going to have a temporary table with all the values and have the report's recordsource point to the temp table but what if multiple users run the report? Wouldn't the da...

VBA to create formula from a list in one sheet.
I am looking for a script that will create a formula that adds up cells from multiple sheets and places the formula on a totals sheet. The script needs to grab the name of the sheets from a range on a seperate sheet in the workbook. I hope this make sense. Range("A1").Formula = "=SUMPRODUCT(SUMIF(INDIRECT(""'""&C1:C3&""'!C8""),""<>""))" where C1:C3 is a range housing the relevant sheetnames in separate cells, and C* is the cell to add. -- HTH Bob (there's no email, no snail mai...

Table and query question
I would like to create a table that will relate an account to its parent(s). 1 account can have multiple parents, and 1 parent can have multiple accounts. And, a parent can have a master parent. For ex: Account 1 can have Parent 1. Account 2 can have Parent 1 and Parent 2. Parent 1 can have Parent 2. And, the queries that I will perform will request these data: 1. give me all parents of Account 1. The query should return Parent 1 and Parent 2 (because Parent 2 is the parent of Parent 1) 2. give me all parents of Account 2. The query should return Parent 1 and Parent 2 3. give...

File won't open as read only
I have a file that is in use, but another person opens it and it doeasn't display the "file is in use" message. Is there a setting or fix? Hi have you shared this file? -- Regards Frank Kabel Frankfurt, Germany John wrote: > I have a file that is in use, but another person opens it > and it doeasn't display the "file is in use" message. Is > there a setting or fix? The file is on a network share. The share has all the appropriate permissions. >-----Original Message----- >Hi >have you shared this file? > >-- >Regards >Frank Ka...

Tables/ queries question
I have a table for recording college attendances, some of the courses are at Campus 1 and some are held at Campus 2. I've got a field for course code, course anme and a check box to show if the student is at campus 1. When I enter the student ID number into the form, at the moment the name of the course comes up automatically in the text book, as I think it's reading the information from another database on the system. At the moment I've got a check box on the Form to enter manually into the table whether the student is at Campus 1 or Campus 2. What I wondered is if...

Pivot Table Size
I have a pivot table that changes size because of the table that it refers to is dependent on information inputed on another table. explanation: sheet 1 has 3 options you can input (but one column that is empty rests between the 2nd and 3rd column, that's why I can't use a pivot table directly with this table) regular hours, other hours, and department number. sheet two summarizes that table to three columns (essentially eliminating the extra column) Now on sheet one, when no information is inputed in a row, the results are zeros, and the pivot table is empty. But once you enter in ...

How to give other users read-only access to Calendar
I want to allow the group Everyone to have read-only access to a calendar in a certain mailbox. I can do this by granting the permission 'Full mailbox access' (under 'Mailbox rights', under 'Exchange Advanced', for the particular user). However this also allows people to to do everything (ie: they become read-write users). I notice that every mailbox in the system has 'Read permissions' granted to group Everyone. This does not allow other people to open items in the mailbox, but as I understand it, permits Exchange Server and Outlook to do shared meetin...

How to calculate compound interest given table of dates/interest rate
Hi, There must be a standard way of doing this... I have a table of bank base rates and the day on which they came into effect - about 50 entries in two columns (date/rate%). I want to calculate the compound interest on one of my customer's outstanding invoices from the due date of the invoice. My knowledge of Excel is basic, but even I understand there must be a standard way of doing this ;-) The interest rate table goes back several years, as does the outstanding amount. Any help would be gratefully received. Thanks -- Mike As you can tell by the dearth of replies, this is not ...

Ungroup a pivot table in XL 2000
I have a pivot table with dates grouped by month. In XL2002, if I want to ungroup them, I can use the PivotTable toolbar. I also have to do this in XL2000, but I don't see the same tool bar. How do you ungroup a pivot table in XL2000? -- Thanks, Fred Please reply to newsgroup, not e-mail You can right-click on the field button in the pivot table, and choose 'Group and Outline'. OR, select the cell that contains the field button On the Standard toolbar, choose Data>Group and Outline Fred Smith wrote: > I have a pivot table with dates grouped by month. > > In ...

Why the hidden column can't be shown on charts?
It is strange that when the data is quoted from a hidden column, the data can'be showed on charts. is it one default excel charts properties? I'm not farmilia with excel chart. does anyone can help me let this data shown on charts? Thanks and regards, Viesta Hi, It is an option. in xl2003 select the chart and then use the menus Tools > Options > Chart uncheck Plot visible cells only Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "ViestaWu" <viesta.wu@konecranes.com> wrote in message news:B9B878F3-2CD0-402E-97CE-4D2C8C391719@mi...

Read mail arn't marked as read anymore
After an SP upgrade of my Office 2000 the priviewed mail doesnt get marked as read anymore. I have tried to change the time (Tools->Options->Priview pane) from 2 -> 3 -> 4 seconds but nothing works. The only way to mark a mail as read is either to open it or right click it and chose Mark as read. Since I only use the priview pane this is very anoying for me. Is this a bug or has some setting changed with the SP? ...

Creating a stacked column chart with 2 axes.
I am working within Excel 97. I need help creating a stacked column chart with 2 axes. An example of the source data that I'm using appears on a worksheet as follows: GROUP SERV_TYPE YEAR_QTR #CLAIMS CONTROL AMB 2001Q1 96 .... CONTROL HOSP 2001Q1 68 .... CONTROL DRUG 2001Q1 32 .... EXPERIMENTAL AMB 2001Q1 99 .... EXPERIMENTAL HOSP 2001Q1 27 .... EXPERIMENTAL DRUG 2001Q1 68 .... My goal is to use the values for "YEAR_QTR" as the category (x) axis lab...

look one cell below
Does anyone know how reference one cell below another cell. Example In cell B10 I want to return the value in the cell below cell A5, but I can't type A6 directly. I want to type in something like =A(5+1) but excel doesn't take that. Any suggestions? Thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Hi, try =OFFSET(A5,1,0) Gromit ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post u...

reading confirmation
Good day, I have a problem with outlook. When they send a message to me that demands the shipment of a reading confirmation, even if I accept, the reading confirmation does not come received from the sender. Someone knows from what depends and in which way I can resolve the problem? Thanks for the eventual answers. Niki In news:eht7fo$251$1@fata.cs.interbusiness.it, Niki <nicola.pantaleo@yahoo.it> typed: > Good day, > > I have a problem with outlook. When they send a message to me that > demands the shipment of a reading confirmation, even if I accept, the > read...

Deleting employees
We loaded employees into Great Plains with e-connect 8.0.2.1 using taCreateEmployee and taCreateEmployeeAddress. I need to wipe this data out and re-load. I know that with the new version of e-connect there is a taDeleteEmployee but for now can someone tell me the tables I need to truncate out in order to re-run the taCreateEmployee and taCreateEmployeeAddress? thanks -- AHIhelp AHIhelp: The tables are listed below - these are the ones handled by the Employee Delete procedure so I would assume these are the ones you would need to manually handle now as well UPR00100 UPR00102 UPR00...

"Unblock" feature should be optional when reading e-mail in CRM
When viewing e-mail messages in CRM, a line appears saying "Unblock" to allow the full message content to be read. Can this be made a configurable server or security setting? We are trying to reduce "clicks" as much as possible. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agre...

rpc over https one server
Hello, I would like to configure rpc over https on an exch2003 server (win2003). This server is not a DC. I do not have FE/BE. I found a lot of documentation for FE/BE but I am not clear on how to configure RPC over HTTPS in my case. Could somebody please point me to as article? Thanks Pierre Pierre- What part aren't you clear on? Mark the server as an rpc/https backend, and then configure the registry key referenced in the single server section of this article and reboot http://www.amset.info/exchange/rpc-http-server.asp -- Thanks, Brian Desmond Windows Server MVP - Directory ...

Table Manners
In Outlook how can I implant a table in two or more colums, to obviate the necessity to scroll down if it is a lengthy table? ...

The memory could not be "read".
I'm at work yesterday afternoon responding to e-mail on my desktop, and Adobe pops up from the toolbar with a notice that there is an update available for Acrobat. I accept the download, keep plugging away at my e-mail, and install it when it's done. It runs through the install, tells me that I should restart, but I ignore it and keep working. It's near the end of the day and I'm going to be going home soon anyways. I forgot to turn it off when I left. Came in this morning, restarted it through the Start menu, and rebooted. Upon reaching the "Ctrl+Alt+Del" ...

how to transfer an A/R balance from one account to another
We've been operating with one set of A/R accounts. Now our company has 4 profit centres and the-powers-that-be want an A/R report for each profit centre. Fair enough. So we set up Parent-Child accounts. The problem is that there are many accounts in the 'old' A/R with balances. Is there a nice simple way of transferring - or balance forwarding - amounts from the 'old A/R to one or more of the 4 profit centres? I see via "HELP" that I can balance forward for the new accounts, but that still leaves the 'old' A/R accounts with balances that need to b...

Trying to get row data across columns
Hi, I'm not sure what I'm trying to do counts as a full transpose. Basically, I've got some data which simplifies to the following: ID# Topic Serial There can be more than one record for each ID in this table, each with one attached serial number. The table I want will have one record per ID, but will have all serial numbers for a given ID number across the table, thus: ID# Topic Serial1 Serial2 Serial3 Serial 4 I have established that there may be as many as four SN#, as few as 2 per ID#. How would I go about creating this table? On Tue, 12 Feb 2008 14:18:00 ...

how can i copy a document to a CD without making it read only?
HOW CAN I COPY A DOCUMENT TO A CD WITHOUT MAKING IN READ ONLY? You can't. It is not the file, but the media, that is read only. Even CD-RW media does not allow editing a file on the CD. Copy te file from CD to HD, mak edits and if a CD-RW you should be able to burn the edited file back to the CD. hth "DON" wrote: > HOW CAN I COPY A DOCUMENT TO A CD WITHOUT MAKING IN READ ONLY? ...

How to change the page orientation on one page in publication
I'm working on a publication and I need to change the page orientation to landscape on several pages, but not the entire document. How would I do that? Thanks, You can't in the way you're thinking. However............you can start a new document in landscape, layout the page you need, select all, group it, copy it, and then paste it into the portrait oriented document. Rotate it 90�. (You can ungroup if you need to move anything around individually after it's been inserted into the target document.) -- JoAnn Paules MVP Microsoft [Publisher] "JuneJ" <J...