Access vs. Filemaker

[anoob]

Hello all,
I have decided to make the leap to Access and I am in the process of creating an entirely new database that will help me track my contacts, clients, jobs, invoices, etc. Unfortunately I am clueless on how to use Access and I do not think that I understand relationships as they are meant to be used. I have done a lot of reading but I've hit a wall.

My main concern is this - I would like to keep all of my contacts and their information in one table. I would like to keep all of my clients (which will refer specifically to a business) in another. However, when a contact is selected from a drop down menu on the clients table, I would like the data to fill in automatically. For Example:


==================
tblContacts
==================
-Contact ID - PK
-First Name
-Last Name
-Email
-Mobile Phone
-Personal Notes
==================


==================
tblClients
==================
-Client ID - PK
-Business Name
-Address
-City
-State
-Zip
-Website
-Fax
**-Contact ID - Drop Down that displays full contact name
*-First Name - Populated based on drop down selection
*-Last Name - Populated based on drop down selection
*-Email - Populated based on drop down selection
*-Mobile Phone - Populated based on drop down selection
==================


tblContacts relates to individuals
tblClients relates to businesses and locations

*I have read that this may be an incorrect way to look at the data, since it would be redundant - perhaps I should only use a form to combine all the data in one place?*

My problem is getting the corresponding fields to populate automatically in the clients table (or form) once I select a contact. The reason I don't combine this all into one table is because my contact could move to another job, and I would rather be able to re-select a new contact for a client record instead of entering an entirely new record.

Is this what a relationship is used for?
Is what I am trying to do even possible?
(I had a working version of this in Filemaker)

I am open to any suggestions if I can avoid some stupid mistakes here...

Please advise.


Many thanks!

[NoellaG]

Hi,

* the way you have set the relationship up, one contact can work for many clients, so there is a 1 to many relationship from contact to client.
In this case you can create a main form showing the contact with a subform showing all clients they work for.

* If one client can have many contacts, you remove the field contact ID from tblClients, and put a field clientID in the table tblContacts. Then you can create a main form showing the client with a subform showing all contacts for that client.

* If there is a many to many relationship: one client can have many contacts and one contact can work for many clients, you need a third table like:

tblClientContacts
----------------
ClientId
ContactID
Function
....

greetings
Noëlla

[anoob]

Ah, thank you so much!
So you can use a subform without "running" a query to bring data together on a form then, and style it so it LOOKS like one document?

(I use quotes around "running" because I am not sure - again - exactly when a query must be used. Right now I am more worried about the basics and understanding how all these things need to relate before I worry about reports and such.)

Is there a way then to do a one-to-many relationship where 1 CLIENT can have 1 CONTACT, and this contact is selected from a drop down list? I have tried to set this drop down box to display the first and last names of the contacts, but when I select an option I get a data error because the name is not a number - and I had to set the FK to a number type so that I could set the link between the AutoNumber PK and this to begin with.

I hope that makes sense. Thanks again!