473,325 Members | 2,870 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,325 software developers and data experts.

Access customer, name and phone tables

I have two tasbles. One called suctomer and has custID, and custname. The other table is cusphone. I need only one realtionship between the two tables. I need to search for an id number, and a phone number. how could join these two tables without having the custID get duplicated? thank you
Dec 31 '10 #1
5 1807
Stewart Ross
2,545 Expert Mod 2GB
Hi. You need to reconsider your table design, as you have incorrectly separated out components of the customer table such as the customer phone into separate tables. These are simply attributes of the customer entity, and should remain as part of your customer table.

There is no need at all to add the complexity of maintaining separate customer-attribute tables joined by customer ID when a single table is all that is required.

When you come across 1-to-1 relationships such as those involved in your separated customer / customer phone tables, it is normal to combine these into the one table as long as all fields of the sub-tables are dependent on nothing other than the key of the master table - customer ID in your case.

I recognise that customers can have more than one phone number. This is dealt with by recording as separate attributes a landline number, mobile number, and fax number. If customers may be business clients you may also wish to record the business phone number in addition to their landline (home) phone number.

We have a helpful article on database normalisation and table structures which you may find useful.

-Stewart
Dec 31 '10 #2
code green
1,726 Expert 1GB
I agree with Stewart, as this is standard industry practice.
The only justification for a seperate telno table would be to monitor calls made, along with a timestamp, id and maybe reason.
Dec 31 '10 #3
Stewart Ross
2,545 Expert Mod 2GB
Further to my reply above, if you wish to keep your current two-table approach - which I do not recommend - then the customer ID must be a component of the customer phone table. You cannot avoid this as there would be no other way to identify which customer's phone number belonged to which customer.

If I had to implement a search on your two tables I'd simply join them on the customer ID field in a SELECT query and search the join results. But, as mentioned in my previous post, I think your current two-table design unnecessarily complicates things for you and I would strongly urge you to reconsider your approach.

-Stewart
Dec 31 '10 #4
OldBirdman
675 512MB
Is the data normalized with the one table approach? It seems to me that it is not! With:
Expand|Select|Wrap|Line Numbers
  1. Land Phone
  2. Cell Phone
  3. Fax
  4. Land Office Phone
  5. Cell Office Phone
  6. Office Fax
  7. Office Secretary (Leave message)
  8. Pager (field left from when such existed)
  9. Land SummerCabin Phone
  10. Land Temp Phone while on assignment out-of-town
All queries on the phone number become quite awkward. There is no provision for adding numbers with new technology, and old technology leaves fields behind.
I agree with Stewart's Post #4 paragraph 1, there must be a field to join the two tables. Stewart references an insight on Normalization, and for First Normal Form (1NF) there is a statement "Do not use multiple fields in a single table to store similar data ". I believe that this applies here, and one table is not the way to go.
OldBirdman
Dec 31 '10 #5
code green
1,726 Expert 1GB
That is quite a good point OldBirdman.
But in keeping with this line of thought this could also be extrapolated to addresses.
invoice address, delivery address, registered business address, home address, log cabin address.
Most databases I work with do have all telephone/fax numbers in one table normally a customer table, sometimes delivery addresses in a seperate table to invoice address.
From experience there is not more than say two telephone entries per customer , the rest being empty (NULL).
I am quite thankful for this as queries extracting customer information are kept short, as opposed to multiple JOINs to tables named Land_Phone,Cell_Phone,Fax,Land_Office etc

One table may not be the way to go but it is convenient
Jan 4 '11 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Karen | last post by:
Anyone that may be able to help. I have MS Access 2000 installed. I need to convert some MS Access 2002 database files to MS Access 2000 needed for a course. Does anyone know how to convert? ...
5
by: Jozef | last post by:
Hello, I have an Access 2000 database that's somehow gotten corrupt. It's on a stand alone machine. I started to get an error something to the effect of "Error Accessing the Network"...
11
by: TheBurgerMan | last post by:
Hi all. I am using W2K3, .NET2 on a machine running AD and Exchange. I started getting the message below last week. I googled the error and not much was returned, but I did find this;...
9
by: shades234 | last post by:
When i go to open my Access database from certain computers, i get multiple popup messages stating the same thing. They all say "You do not have exclusive access to the database at this time. If...
1
by: PW | last post by:
Hi, All of a sudden one of our clients can't use her application. She gets "You don't have exclusive access to your database at this time" when trying to open our application. The only thing...
1
by: Nokukhanya1 | last post by:
I would like to know if is it possible to open a Ms Access Database from another PC that does not have Ms Access? How do l export only the current data from Ms Access Database to a text file?
2
by: Zippy | last post by:
I recently updated one of my clients from Access 97 front-end to Access 2003. (Database still in 97 format). They have about 5 networked PCs. On one of these PCs only, the user gets a warning...
1
by: pradnya | last post by:
Can i have more access to post data in a file other than mentioned in 'action' param ? if yes , how to achive this ? I am working with php/mapscript and using frames.. i display my...
3
sujithkrishnan
by: sujithkrishnan | last post by:
hi all... please help me in resolving this error.. i created a web service and i am not able to consume it in my app. When i browse that page am gettin an ERROR as follows.. Exception...
1
by: btreddy | last post by:
Hii all , When i was trying to access the webpages from the server(localhost only ) I got the message "The current identity (SYSNAME\ASPNET) does not have write access to...
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
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
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: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.