Table Confused :(

[ICTkirsten]

Hello,

I really need help and im so stressed out i cant understand.

Ok so im doing a project and i made a requirement spec of which has this:

must store appointment ID, customer ID, forename, surname, contact number, dentist ID, forename, surname, room, appointment date and time...


what this means is when a new appointment is made which asks for the details above then these details will be stored... in the appointments table..

BUT my issue is my appointments table does not have forename, surname ( for both the customer and dentist) or the contact number....

So i dont know how i will justify that those listed will be stored from the new appontment because all that is stored is appointment ID, customer ID, dentist ID, room, appointment date and time...

because if i cant then my issue is why did we ask for those details in the new appointment (i needed them to identify the customer if the ID was forgotten)

IM SO STUCK


please if anyone understand could you help me out

THank you

 

[Gasman]

The details for the customer, and dentist will be held in other tables, and linked by their ID keys, CustomerID and DentistID in the Appointment table.

 

[ICTkirsten]

thank you so much for your help but im still confused

for the other details these are entered into the system from other forms ie new registration... so yup they will be in the other tables

but i dont understand how when i explain that new appointments will be stored in the appointments table how the others ( forename etc) arent actually in that same table but in other tables ... im pretty sure it makes sense to everyone else but i am confused out of my minds...

Does this mean when i stay new appointments are stored in appoiontments table i exclude the customer forename etc? if thats the case then i dont understand why i would ask for it in the first place ( which i NEED to ask btw)

 

[Gasman]

I'm not sure how you are approaching this, but a simple way would be....

You have a form for appointments. On that form you have a the data for the appointment.

You would likely then have a combox for Dentist and another for Customer.
In the source of both you might have the ID, Surname, Forename of each.

You then select the relevant Customer and Dentist from each combobox when making the appointment and in doing so the relevant ID of each record in those lists will be put in the field in the appointment table when you save the record.

The wizard will do all that for you when you add the combobox.

HTH.

 

[ICTkirsten]

yes i have combo box for dentist not customers because there are too many records...


the customer ID and dentist ID saves in the appointments table because they are in the table as foreign keys..

its the other details like contact number that im confused about

 

[Gasman]

Well you would only be able to show the correct contact number (and any other customer related data) when you know which customer you have selected? So how are you selecting a customer. Can you post an empty database.?

I only have Access 2003, so it would have to be a mdb file.

Hopefully someone more experienced can jump in, if you can explain a little better exactly what problem you are having.

I must admit I am having some difficulty trying to work it out.

 

[jdraw]

ICTKirsten,

Here is a free generic data model for doctor and appointments. You can adjust as necessary to meet your needs. It's generic, so not all tables may be needed in your situation. It should give you a starting point, or ideas from which to construct your data model (tables and relationships).

Here is a tutorial on table design concepts that you should work through.

Good luck.

 

[ICTkirsten]

oh actually they do automatically enter then but i mean
if the contact number appears on the form then how do i explain its been stored in the appointments table when it hasnt..

 

[jdraw]

cross posted
http://www.accessforums.net/forms/table-help-51395.html#post269686

 

[CJ_London]

this thread has been answered to the OP's satisfaction on the other forum - hopefully he will have the courtesy to advise here