Help in Database Design - MS Access

Hi folks,
This may be simple for most of you.
I need to store the phone numbers of each customer. A customer can have more than one phone, as well as diff types of phone (mobile.Landline Residence, Landline office). Can someone show me how best to capture this info in the tables.
There is no restriction on the number of phones a person can have in each category.

There is an additional problem. I need to display the info collected in a form without wasting much space in the form.
Thanks in advance

Yes we can go for a drink if the problem is solved...:D

U need the following tables

phonetype - typeid,description
Phonenumbers-custid,phonetypeid, phonenumber

When displaying you could it on a frame and a simple text box.

Thanks for the resp.

Quote:

---

When displaying you could it on a frame and a simple text box.

---

I am talking of reporting and not data capture. I like to use reporting tool say Crystal report.
I did try your method of making tables but I went into problems in the report.
my report comes out like this
custId Phone Residence Phone Office Mobile
1-----------------------------9874
------8878
-------------6966
2------8796
------------------------------9456
-------------6879
etc
Instead of

custId Phone Residence Phone Office Mobile
1------8878--------------6966-------9874
2------8796--------------6879-------9456
etc

Points to note

1. Iam using formula fields to retrieve the phone numbers
2. I set the fields to suppress if they are blank
3. I even tried placing the fields in multiple sections of Details and setting them to Underlay (section expert). Still no solution.
4. I also found the gaps are caused by the empty fields which are there though they are suppressed :confused:

Any ideas

Your original post actually did include a request for data capture guidance, specifically:

"Can someone show me how best to capture this info in the tables" (sic)

... which you mentioned before the "additional problem" you then outlined, concerning "display" aspects (to which you later refer as "reporting.")

Anyway, we can probably assist with the query you need, assuming you provide the table structure. A few questions to further assist us in helping you:

1. Did you use the table structure my colleague suggested?

2. If not, what is the structure?

3. What query are you currently using?

Just trying to understand the scenario enough to offer assistance.

We appreciate your help!

Bill

Thanksfor the reponse

Quote:

---

1. Did you use the table structure my colleague suggested?

---

Affirmative. Infact I started with it and got into problems in reporting the data, so I presumed there might be something wrong in my design of db, now I believe I was on right track as for as db is concerned.
sorry if I was not clear :(

clearing the confusion, hopefully....
I am trying to report the following field data in CR9 viz. custID, Residence phone no(s), Office Phone no(s), Mobile(s). As stated earlier #1 there may be more than one phone (or none) for each cust in each category.
All I want is just to generate a report as shown in the #3 using CR9.

Quote:

---

What query are you currently using?

---

At present it is a simple SELECT query to retrieve all the records

Hi:

Did you mean to attach files? You make a reference:

"All I want is just to generate a report as shown in the #3 using CR9."

... but I can't see an attached file. Such a "picture" might make it much easier for me to offer guidance.

You could also send it to me at my e-mail address below, of course ...

Thanks.

Bill

Quote:

---

but I can't see an attached file.

---

Thanks for the response. Its b'cos there is no pic in the first place. I had simply showed the reqd layout in post #3. Iam repeating it below
custId Phone Residence Phone Office Mobile
1------8878--------------6966-------9874
2------8796--------------6879-------9456
etc

Quote:

---

Such a "picture" might make it much easier for me to offer guidance

---

Sure... but that's what I'm trying to build!