Help filling in form from 2 different tables

I have two tables, CustomerData and EmployeeData.  CustomerData has all the
address, phn, name, empid# of last employee to help em and custid (primary
key) and employeedata has name addr phn and empid (primarykey) I have a combo
box that pulls up custdata (by Custid sorting by Lastname & ", " & Firstname)
I wanna do an Afterupdate event to fill out the following textboxes (based on
the Custid it fills out the Lastname, Firstname, Address, Phnnumber) and then
based on the Empid of the last Employee that helped em it will fill out the
textboxes witht he first and last name of the employee from EmployeeData
table.

I linked the two tables together with customerdata on-to-many employeedata

I'm new to access and for the life of me i cant seem to change the value of
the textboxes to apporiate information from the tables. Any help would be
appreciated.

0
Tadbit
3/24/2010 11:57:21 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
810 Views

Similar Articles

[PageSpeed] 44

On Wed, 24 Mar 2010 23:57:24 GMT, "Tadbit" <u58962@uwe> wrote:

>I have two tables, CustomerData and EmployeeData.  CustomerData has all the
>address, phn, name, empid# of last employee to help em and custid (primary
>key) and employeedata has name addr phn and empid (primarykey) I have a combo
>box that pulls up custdata (by Custid sorting by Lastname & ", " & Firstname)
>I wanna do an Afterupdate event to fill out the following textboxes (based on
>the Custid it fills out the Lastname, Firstname, Address, Phnnumber) and then
>based on the Empid of the last Employee that helped em it will fill out the
>textboxes witht he first and last name of the employee from EmployeeData
>table.
>
>I linked the two tables together with customerdata on-to-many employeedata
>
>I'm new to access and for the life of me i cant seem to change the value of
>the textboxes to apporiate information from the tables. Any help would be
>appreciated.

STOP.

If you're trying to *copy* data from one table to another table... don't!

In addition, it would seem that you are making a mistake by storing the
employee's personal information in the customer table. Normally you would use
*three* tables: a table of CustomerData (custID as its primary key), a table
of Employees (emplID as its primary key), and a table of customer
interactions, with fields for CustID (who was being helped), EmplID (who
helped them), and fields for the date of the interaction and any other
information you want to record about the interaction. This would let you keep
a history of interactions, from which the most recent interaction could be
readily obtained.

So... why are you trying to store employee data redundantly in the customer
table? And do you really want to keep overwriting data? The identity of the
employee is *not* a characteristic attribute of a customer!
-- 

             John W. Vinson [MVP]
0
John
3/25/2010 2:39:49 AM
Here is the scenario: I have a Table Customer data (stores all about the cust)
and another table with all current and past employees (checkbox states
weither they are still employeed for purposes of keeping employees in the
data since they are going be linked with the customers they helped) and i
have 2 forms to add new cust and add new employees.

 Im working on a form that will be used to as a workorder type that employees
will type in remarks of what they helped the customers with and autostamp
date & time and which employee did the helping.  My 4th form (currently what
the question was intended for) i want to choose the customer from a Combobox
and it will fill in name, address phn number of the cust as well as a subform
on it that upon loading the customer info will display a list of service
dates sorted decending by date but will also show the employee that helped
them.

I have a semi working way of doing it but i was looking for an easier way
since all i really need is the custid and empid and just load the data from
their respectable tables.  Right now im loading up the cust info via Combobox
and then using VB to me.address = me.combo.column(X) and so forth to load the
cust into the data.  This is resulting in saving the cust info and employee
info into the workorder table resulting in redunant data.  I know there is an
eaiser less "spacious" area to achive this but i cant figure it out.

John W. Vinson wrote:
>>I have two tables, CustomerData and EmployeeData.  CustomerData has all the
>>address, phn, name, empid# of last employee to help em and custid (primary
>[quoted text clipped - 11 lines]
>>the textboxes to apporiate information from the tables. Any help would be
>>appreciated.
>
>STOP.
>
>If you're trying to *copy* data from one table to another table... don't!
>
>In addition, it would seem that you are making a mistake by storing the
>employee's personal information in the customer table. Normally you would use
>*three* tables: a table of CustomerData (custID as its primary key), a table
>of Employees (emplID as its primary key), and a table of customer
>interactions, with fields for CustID (who was being helped), EmplID (who
>helped them), and fields for the date of the interaction and any other
>information you want to record about the interaction. This would let you keep
>a history of interactions, from which the most recent interaction could be
>readily obtained.
>
>So... why are you trying to store employee data redundantly in the customer
>table? And do you really want to keep overwriting data? The identity of the
>employee is *not* a characteristic attribute of a customer!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1

0
Tadbit
3/25/2010 5:29:57 AM
On Thu, 25 Mar 2010 05:29:57 GMT, "Tadbit via AccessMonster.com" <u58962@uwe>
wrote:

> Right now im loading up the cust info via Combobox
>and then using VB to me.address = me.combo.column(X) and so forth to load the
>cust into the data.  This is resulting in saving the cust info and employee
>info into the workorder table resulting in redunant data.  I know there is an
>eaiser less "spacious" area to achive this but i cant figure it out.

Just store the CustID and the WorkerID.

That's ALL YOU NEED in the workorder table. You don't need the names! You are
using a relational database - use it relationally! If you want to see the
names in conjunction with the data in the workorder table, you can *create a
query joining the workorder table* to the customer table by CustID, and
joining it to the employee table by EmplID; you will then have all the fields
available.

If you just want to see them on a form, rather than having code in the
afterupdate to "push" data from Column(x), you can use textboxes on the form
with a control source 

=combobox.Column(x)

to "pull" it into view. You don't need any code nor a table field in the
workorder table in order to do so.
-- 

             John W. Vinson [MVP]
0
John
3/25/2010 6:49:18 AM
Reply:

Similar Artilces:

Convert Excel Tables to Pivot Table Lists
Excel Tables to Pivot Lists Hello, I'm trying to convert excel tables into pivot table lists and I am looking for a method to do this besides cutting and pasting. The table has 6 columns (see below) with count of product for each year e.g. xxx1 prod1 100 in Yr1, 200 in Yr2, 300 in Yr3 etc. I want to end up with a 4 column list like, (see "Get into pivot table list in this form) Thanks Home....Prod....Yr1....Yr2....Yr3....Yr4 xxx1.....prod1....100....200....300....400 xxx2.....prod2....110....210....310....410 xxx1.....prod3....120....220....320....420 xxx2.....prod4....130....2...

Rollup 2 and Office 2007
I already have Roll Up 1 and the Office 2007 compatibility patch installed. If I now install roll up 2 will I have to reinstall the office 2007 compatibility? yes -- Regards, MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "LLoyd" wrote: > I already have Roll Up 1 and the Office 2007 compatibility patch installed. > If I now install roll up 2 will I have to reinstall the office 2007 > compatibility? ...

Microsoft POS #2
I have a group of customers that I am working on setting up A bundled POS system for. I have looked at RMS and Microsoft POS. The customer's are small bulk food stores. They package all of the bulk food in various containers and weigh it with a Hobart scale producing a label with an system 2 price enbedded barcode. These are UPC A formats. They are for the most part simple single store installs and donot require the complex RMS system. I have setup the Microsoft POS version 2.0 and found it will work perfect for these customers. The only problem when you programed the system you l...

Outlook 2003 keeps crashing #2
I get this message when I look in System information. Outlook keeps crashing quite often. I cannot for example delete two messages without outlook crashing. My colleagues do not have this problem. We are using the Microsoft mail exhange server. Faulting application outlook.exe, version 11.0.5510.0, stamp 3f1380f0, faulting module mso.dll, version 11.0.5606.0, stamp 3f334cce, debug? 0, fault address 0x003b6e16. Does anyone has a solution?=20 Best regards/ Bj=F6rn 1) I would definitely apply Service Pack 2for Office 2003 + applicable critical security updates. 2) Have you tried starting O...

Lookup based on criteria in 2 columns
Hi, I am trying to use a vlookup or other function to return the value in the amount column based on the location and date. Here is a sample of my data: Location Date Amount 101 9/15/8 10 101 9/16/8 20 101 9/17/8 15 102 9/15/8 50 102 9/16/8 75 102 9/17/8 67 For example if I wanted to return the amount for location 102 on 9/15/8, what formula would I use? I tried using variations of vlookups but had no luck. Thanks, =SUMPRODUCT(--(A2:A50=102),--(B2:B50=DATE(2008,9,15)),C2:C50) -- Regards, Peo Sjo...

Standard MFC controls behave different when showing polish characters
Hello all, I have a rather simple to describe but difficult to solve problem. Our customers want to use our ANSI MFC program to show different languages. For example Polish. When I set the font for the different controls they behave different. For example the CEdit shows the polish characters just right but the CButton with checkbox style shows glyphs instead of the special characters. Every control that is owner draw and uses for example the DrawText() function of the device context shows the characters right. I've experimented with _UNICODE for the display part and everything I could ...

Inserting form values into a table
We have a form with values taken from an sql query that comes from two different tables. We would like to enter the information into a third table. Can some one direct me to code that will do the following: 1. Provide the Insert sql that shows us how to add the form values to the table 2. Show us how to loop while inserting the information into the table (there could be several lines on the form, each must be inserted one at a time). I have worked with Access before and have never had a problem inserting information. However, I cannot quite figure out how to insert informtion through an ...

Tell the difference between upper and lower case
I have a column that has letters in it and I am using =COUNTIF(D13:D27,"M") in one cell and =COUNTIF(D13:D27,"m") in another. to keep track of what letters are put in the "D" column. But the both count the upper AND lowercase letters. Is there any way to mak excel tell the difference between upper and lowercase letters? Thanks Ro -- Message posted from http://www.ExcelForum.com RogerDaShrubber wrote... ... >=COUNTIF(D13:D27,"M") in one cell > >and > >=COUNTIF(D13:D27,"m") in another. > >to keep track of what lette...

Input Excel 'Password to Open' through control in access form
Hi All, We know,Excel has prompt password to open it files. Is it possible to create a code that can supplies the excel prompt password?.So that when we open the excel file through our access control in a form, the excel files can be opened automatically.But when the excel files opened from its default icon,it will prompt a password first. ...

Time differances
1 have 4 boxes start time end time lunch hours worked 09:00 17:00 1.00 7:00 09:00 17:00 0.30 7:70 (should be 7:50) (B3-A3)*24-C3 How do I make it 9.5 hours by changing forumla Thanks Trever, =(B3-A3)*24-C3*24 Format for General, Number, or anything except Date/Time. Format - Cells - Number. You'll get 7.5. If you got 7:50, as you requested, it would look like hours and minutes. -- Earl Kiosterud www.smokeylake.com "Trever B" <TreverB@discussions.microsoft.c...

Find prev record on other requeried form
Using access 2003. I have a main form for name and address info. A subform shows limited info on client placement. Click a command button on the main form and user opens another form for full screen entry of placement data. That form opens showing the client selected on the initial main form. When the full screen placement form closes, it requeries the original main form with the placement subform to show the added placement. It goes to the first record in the table though. It does not show the original client whose placement was just added. How do I set the mai...

how do I connect an object from one page to a different page?
I am trying to connect an object from one page to a seperate object on a different page. The instructions say to hold down your mouse button then click on the point you want it connected too. My problem is that is doesn't allow you to select the starting point of the connector, then select a different page, then click on your end point of the connector. Please help! try using a hyperlink. al "Pepper" <Pepper@discussions.microsoft.com> wrote in message news:3F5657F4-48EA-4681-A21E-D33FC7030401@microsoft.com... >I am trying to connect an object from one page to...

HELP! Need to export hourly sales data on POS (NOT RMS)
How can I export hourly sales data across a date range? For instance, I want to show hourly sales for the month of October so I can graph it and post it in our break room. If I can't export hourly data, can I export daily sales? The built-in reports don't address this data format. This is a multi-part message in MIME format. ------=_NextPart_000_008E_01C826DC.CBC512D0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 7bit Mark, This should work for you. Keep in mind it takes up to 5-10 minutes to load...

Need help with Auto Filter
I have a spreedsheet that is filtered in multiple columns. I am running a "Subtotal" function to count certian rows when I filter the column. My question is this. Is there a way to save or freeze the data that the subtotal function counts in a different cell. In other words I want subtotal to count everything in a particular column but I want to be able to save that number somewhere so that when I filter again with another variable I am able to still view the first subtotal to compare the two. I hope this makes sense and thanks in advance for any assistance you can provi...

Help with income/expenses
Running 2003 in home page I setup income and expenses I prefer to see one whole month. Money some how shows it as 1/22/2006 through 2/20/2006 any way to fix this? Thanks Jeff ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Farms - Total Privacy via Encryption =---- ...

Percentage difference
Hi guys, Not exactly great at formulas, so any help would be appreciated. Excel 2207 In cell k2 and below are buying prices. In cell j2 and below are selling prices. I just need to know the 'mark-up' percentage. Also, would be a great advantage for the formula to work the other way around....showing 'loss' as well !!!!!!!!!!!! Have tried: =2*ABS(J2-K2) / (J2 + K2) and =(J2-K2)/J2 and several other variations, but to no avail. Not sure whether I am needing 'relative' or 'absolute' either!! Thanks in advance Bob Hi Bob, Am Sun, 30 Oct 2011 1...

help with simple maths in excel
I want to do a simple arithmatic excercise in excel for my grand daughter. It is stuff like 2+2 =4 Smart me has hit a problem at the first hurdle... I need to put 2 in one cell the + in another cell and then 2 in another = in another and then she puts the answer in the next one. So the above would have 4 cells completed and she would put the answer in the 5th one. When I use the + or = sign in a cell of its own it (excel) thinks I am doing an equation, is there a way around this? I will work on the answer like if she gets it correct or wrong how I will do that...a sound or som...

Need Query Help
I have two tables, Table A & Table B, in my database that have the same fields (Name, SSN, etc). Most of the records in both tables are identical, but each table has some unique records. I would like to run a query that will select the unique records in Table A by comparing SSNs, and then do the same for Table B. I am a database novice, and have tried all the wizards and expression builders with no luck. Any help at all would be appreciated. Thank you!!! Scott Casa Grande, AZ Hi Scott, The "Find Unmatched Query Wizard" is what you need. If you only want to check for SSNs ...

Excel Problem
I have a 23.8 meg excel 2000 spreadsheet set for manual calculation saved to my local hard drive. Every time I try to open it, it takes forver and sometimes never opens but I do not get any error messages, let me just tell you that I am running a P4, 1 GB memory, Office 2K with SP3, and nothing else running when I try to open it. As I said it is set for manual calculation, and it is cleared to not auto calculate when opening or closing. Any idea's as to why this is happening? -- Todd I don't know why you're having this problem but I would like to point something out for w...

2 register ruinning together on one z report
Is this possible, to have two registers running on one z report ? We have a small store and would like to setup a second register as a backup, temporary addition ( during rush hours) Since we are a small show i do not need to have a separate register reports. Can two registers ring up all sales together as one ? How can i set this up ? No, you can't do that. You might be able to use the Register Analysis report available in SO Manager Utilities/Crystal Reports to get something close to a single Z though. Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.c...

Setting MDI Parent Size To Fit Around A Child Form
Hi, How do you set the client size of an MDI parent to fit around a child form? Breakpoints in the constructor and load event handler show the child size to be already modified to fit into the parent; the child's size does not match what shows in the Designer. I suppose the real question might be how do you determine the size of the child window or keep the parent from changing it first? Thanks, Gary What you're asking for is a little backwards, but I'm sure you have your reasons. At any rate, these are the properties you will want to investigate: ...

outlook 98 corperate forms
In a corperate environment, Outlook 98 forms not active for using on some of the PCs. Why it happens? How can I solve this problem. ...

Please ignore #2
Testing a posting problem -- Jim Bunton ...

Linking information form one worksheet to another
Hi, i have a big problem i want to create a link between some columns in two seperate worksheets, so that when i type a name on the master sheet it would give me the required information.Dont know if i explained this right, lets say on the master sheet i have columns : Name,Sex and Height on columns A,D and BH respectively i want that if i type a person's name in worksheet 2 it should give me the results on columns A,C,F in that worksheet. Please help i need it asap for my director One way .. Assuming your "master" sheet is named: Master, with data in row1 down In Shee...

Can anyone help ?
I have created a holiday planner for staff with in are company and i need a formula that gives us only 10% of the total number of staff are off on holiday. would be greatful if anyone could help. Hello - If you have a total somewhere (I would suggest inserting a column on your spreadsheet titled Total and then entering a "1" if the person is going to be out, then total the column of "1"s by entering "=SUM(x:y)" where x=first cell in the range, and y=last cell in the range), in a different cell, enter "=.1*z" where z equals the total of people out...