2

I'm designing a database for my boss in which he wants a list of every dental surgery within a certain area, with every dentist at said surgery listed. What would be the best way to go about this?
I'm leaning towards having a seperate table that contains only dentist names, since having them all in one table would get overcrowded/confusing.
The only problem with this is that he wants to be able to look at the screen and see everything laid out in this format:

Dental Practice_________Dentist name__________ Address________Phone number_______etc
____________________________Dentist name
____________________________Dentist name
_________________________Dentist name

Can this be achieved in table view or would I have to create two separate tables and link them together on a report to show him?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user1687049
  • 21
  • 2

2 Answers2

2

Store the addresses in one table, store the names in another table, create a junction table to show which dentist belongs with which address. create a form to show the information. You will need the junction table, because I think you will find that some dentists operate from more than one address.

Address table
AddressID
etc

Dentists
DentistID
etc

DentistAddresses
AddressID
DentistID

As long as your tables are properly indexed, you can create a query that will form the basis of the form to show the data. You should also consider subforms, the layout you illustrate is a form/subform set-up in MS Access. The address would be in the main form and the dentist list in the subform, which would be based on the junction table.

Just be very careful about changing addresses. The user cannot edit address, they must create a new address.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Thanks, that's really helpful :) How do i create a Junction Table though? Without manually inputting the info again? Bearing in mind there are thousands of dentists that i'll be looking at. – user1687049 Sep 20 '12 at 20:21
  • Where are the dentists and addresses at the moment? – Fionnuala Sep 20 '12 at 20:22
  • I haven't started collating them yet. Want to get the database set up so i can input them as i find them. – user1687049 Sep 20 '12 at 21:12
  • In that case the junction table will be created by the addition of the records. Put in an address in the main form, make sure your link fields are good, add a dentist to the subform using a small popout form or a careful set-up. If a dentist is in more than one address, you can just select them. Look at some of the sample databases. – Fionnuala Sep 20 '12 at 21:25
  • See also: http://stackoverflow.com/questions/12131211/create-form-to-add-records-in-multiple-tables – Fionnuala Sep 20 '12 at 21:27
0

Well, what you will need is a single table that lists areas.

Table:

Areas
- AreaID
- AreaName

and a table for the Dentists which references the Areas Table by FOREIGN KEY

Dentists:
- DetistID
- DentistName
- DetistContactNumber
- AreaID

This will allow you to write a query something like

SELECT  *
FROM    Dentists d INNER JOIN
        Areas a ON d.AreaID = a.AreaID

to retrieve the results you are looking for.

Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
  • Actually, it's only one area that i'm looking at, i'm more looking to find how to list all the dentists at each surgery, without inputting the surgery address and phone number for every single dentist. For example, A single surgery may have 5 dentists and i need to associate all 5 dentists with a signle surgery without evtering the surgery information more than once. – user1687049 Sep 20 '12 at 20:23