473,288 Members | 1,693 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

Table Relationship Problem

Hello,
I'm new at Access and ran into a problem. I have a table with a
field for customer's names. I want to make a lookup field out of the
names by linking it to a table that holds all the customer's names,
adresses, phone numbers, ect. I made a form with a name field and
would like to be able to lookup the name, click it and the have that
person's info come up intantly in a subform. I have the layout all set
up, but I've run into a problem if the customers have the same name.
Even if I have the lookup field account for the name and a personal ID
#, when I select it, the corresponding info. for both customers of the
same name appears as two separate records in the subform and I cannot
choose between them. Can someone tell me how to link tables/feilds so
that the names in my lookup field corresponds to only one set of data
even if the names happen to be identical. Thank you!
-Senna

Jul 10 '06 #1
6 1586
Senna_Rettop wrote:
Hello,
I'm new at Access and ran into a problem. I have a table with a
field for customer's names. I want to make a lookup field out of the
names by linking it to a table that holds all the customer's names,
adresses, phone numbers, ect. I made a form with a name field and
would like to be able to lookup the name, click it and the have that
person's info come up intantly in a subform. I have the layout all set
up, but I've run into a problem if the customers have the same name.
Even if I have the lookup field account for the name and a personal ID
#, when I select it, the corresponding info. for both customers of the
same name appears as two separate records in the subform and I cannot
choose between them. Can someone tell me how to link tables/feilds so
that the names in my lookup field corresponds to only one set of data
even if the names happen to be identical. Thank you!
-Senna
just a question - the drop down list - does it also have the ID # as
well as the name? You will need the ID # as well (you can hide it from
view as well)

Hope that helps
Colin Possamai
Jul 10 '06 #2

Colin Possamai wrote:
Senna_Rettop wrote:
Hello,
I'm new at Access and ran into a problem. I have a table with a
field for customer's names. I want to make a lookup field out of the
names by linking it to a table that holds all the customer's names,
adresses, phone numbers, ect. I made a form with a name field and
would like to be able to lookup the name, click it and the have that
person's info come up intantly in a subform. I have the layout all set
up, but I've run into a problem if the customers have the same name.
Even if I have the lookup field account for the name and a personal ID
#, when I select it, the corresponding info. for both customers of the
same name appears as two separate records in the subform and I cannot
choose between them. Can someone tell me how to link tables/feilds so
that the names in my lookup field corresponds to only one set of data
even if the names happen to be identical. Thank you!
-Senna
just a question - the drop down list - does it also have the ID # as
well as the name? You will need the ID # as well (you can hide it from
view as well)

Hope that helps
Colin Possamai
New to Access here as well, but I am throwing my two cents around to
see if I can help. Colin seems to have touched directly on the point.
If you have repeating fields it is never a good idea to use a lookup
field which seems to be your problem. Colin stated that you need the
ID# as well, but I will go one step further to say that the ID# is
actually the very thing you should be looking up in the first place.
You can add a column to the drop down box with the individual's name on
it, sure but as I understand it you should only be using lookup fields
to pull records according to the primary key. Of course it goes
without saying that you need to set up a relationship between the ID#
and the other tables in the form if there are any. Have you set the
ID# as the primary key? If it is, in the Data tab of the Properties
box for your lookup field, is the Bound Column property set to the
ID#?

I hope I havent given wrong information and I hope that helps.

---Tim Dunne

Jul 11 '06 #3

Senna_Rettop wrote:
Hello,
I'm new at Access and ran into a problem. I have a table with a
field for customer's names. I want to make a lookup field out of the
names by linking it to a table that holds all the customer's names,
adresses, phone numbers, ect. I made a form with a name field and
would like to be able to lookup the name, click it and the have that
person's info come up intantly in a subform. I have the layout all set
up, but I've run into a problem if the customers have the same name.
Even if I have the lookup field account for the name and a personal ID
#, when I select it, the corresponding info. for both customers of the
same name appears as two separate records in the subform and I cannot
choose between them. Can someone tell me how to link tables/feilds so
that the names in my lookup field corresponds to only one set of data
even if the names happen to be identical. Thank you!
-Senna
Rule #1: don't use lookups. They may look pretty now, but they'll
screw you over later.
Create an autonumber field on your Customer table, and make the
combination of fields that uniquely identifies a record as the primary
key. (So the autonumber acts as a surrogate). Then use that
autonumber in your relationships.

The combobox. ... If you want, you can set the rowsource for the
combobox to a query..

SELECT CompanyID, CompanyName, ...
FROM MyTable...
WHERE...

and then in your combobox, you can hide all the columns you want access
to but don't need the user to see. Then you can set the controlsource
for your text fields to something like

=Me.controls("cboCompany").Columns(1)
=Me.controls("cboCompany").Columns(2)
etc.

The first column is the zeroeth column... just to confuse things. so
for example,

Me.controls("cboCompany").Columns(0) would be the CompanyID,
Columns(1) would be CompanyName...

What's up with the subform? Can one company or whatever have multiple
contacts?

Maybe you should back up a step and figure out the tables first... once
you have that down, the rest is easy. Could you describe how the
people and the company(?) information is related? I know I'm being a
pain, but honestly, the first step in desiging a solid database is
getting out a pen and paper and describing to yourself what it's going
to do, and what you need in it. Then you can build tables etc from
that.

If you don't start right, there's little chance of ending right. so do
the hard part first, then the rest will come a LOT easier.

HTH... and post with further questions if you need to.

Pieter

Jul 11 '06 #4
This is not the first time I have heard someone saying lookup fields
should not be used. Is there a specific problem associated with lookup
fields or does Access just have a tendency to misbehave with them after
a while?

Jul 11 '06 #5
"Tim Dunne" <ti********@gmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
This is not the first time I have heard someone saying lookup fields
should not be used. Is there a specific problem associated with lookup
fields or does Access just have a tendency to misbehave with them after
a while?
Check out the following link

http://www.mvps.org/access/lookupfields.htm
Jul 11 '06 #6
Thank you everyone. I don't know if I explained my question very well
but with the information y'alll gave me I was able to solve my problem
using a combo box plus some other stuff. Thank you very much!!!!

Jul 11 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Fred | last post by:
Hi. How do I import while mapping an excel table to an access table please??? I've searched around and all I can find is a software product or code that does the same thing as the access...
2
by: Larry R Harrison Jr | last post by:
I have an Access 97 database with 2 tables that have a one-many relationship. I have a SQL statement in the "one" table which I want to execute and insert 7 records into the "many" table, and I...
4
by: Terry | last post by:
I need some help refining an MS 2000 relational databse. I have created a simple relational database using two tables, 'Student Details', 'Exam Details' and two forms, 'Input/Edit Exam Details',...
5
by: Sami | last post by:
Please bear with me, and if you answer this question, please do it step by step. I am new at Access, not at all sophisticated. I am using Office XP. This will need to be read in Access for...
8
by: Bri | last post by:
Greetings, I'm having a very strange problem in an AC97 MDB with ODBC Linked tables to SQL Server 7. The table has an Identity field and a Timestamp field. The problem is that when a new record...
5
by: PAUL | last post by:
Hello, I have 2 tables with a relationship set up in the dataset with vb ..net. I add a new record to the parent table then edit an existing child record to have the new parent ID. However when I...
117
by: phil-news-nospam | last post by:
Is there really any advantage to using DIV elements with float style properies, vs. the old method of TABLE and TR and TD? I'm finding that by using DIV, it still involves the same number of...
2
by: uarana | last post by:
Hi All, I've been plugging away at this problem for a while now and was wondering if anyone had an idea they could share. I'm working on a document management database that tracks revisions of...
76
MMcCarthy
by: MMcCarthy | last post by:
Normalisation is the term used to describe how you break a file down into tables to create a database. There are 3 or 4 major steps involved known as 1NF (First Normal Form), 2NF (Second Normal...
17
by: Stubert | last post by:
I have a training module db that stores information about employees and what training they have carried our or need to carry out. One table in this database stores what training needs to be carried...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.