Need advice on setting up tables and relationships

[burnished_dragon]

I am a new user and not a programer or anything of the like. I have taken a
user course on Access. What I want to be able to do is have a contact
database for our company. I work for two accountants that are partners. I
figure on one database per partner and one for joint clients. My main
problem is that one accountant has condominiums as clients. Most of the
condos have property mangagement companies. So you can have one property
managment company that have more than one of our clients as their clients.
The condo associations can change management companies.
What I want to be able to do is if the condo changes managment companies I
want to edit that also if the managment company changes address or name I
want to only have to edit it once and all the information for the condos
associated with that management company is updated.


basically
our client (condo) - one mangement company
one management company - many of our clients
also
Client (not condo) - many companies.

 

[NetworkTrade]

I would not recommend a database per partner. I believe one database is
better for them.

Your Client Table should have
Client ID (an arbitrary number you assign)
Client Name
Client Address
Client Tel and other fields of contacts etc.
ManagerID (this is for the condo manager)
Partner

You will have a Manager Table of the condo management companies with an
assigned ID so the ManagerID field is common to both tables.

You can then have the liberty to sort, report info by partner as you
wish...and either partner can see whichever info they will need to....

 

[burnished_dragon via AccessMonster.com]

I had not thought of have a manger table I had gone the way of a separate
address table as a lot of the non condo clients have more than one company
but still have one address for them.
I tried importing what orginaly was a spreadsheet in excel that someone had
made with client numbers and names, contacts and address. When I ran the
wizard for anylizing the data it wanted to split the address into a separte
table because of address that were the same. But then I had trouble with the
form as I only wanted to have to input information on one form. I actually
had a form and subform but it didn't work quite the way I wanted it to. When
I input the condo information the address sub form would come up but wouldn't
let me add or edit it. It also wasn't in the format I wanted it to be
Hope that made sense

I would not recommend a database per partner. I believe one database is
better for them.

Your Client Table should have
Client ID (an arbitrary number you assign)
Client Name
Client Address
Client Tel and other fields of contacts etc.
ManagerID (this is for the condo manager)
Partner

You will have a Manager Table of the condo management companies with an
assigned ID so the ManagerID field is common to both tables.

You can then have the liberty to sort, report info by partner as you
wish...and either partner can see whichever info they will need to....

I am a new user and not a programer or anything of the like. I have taken a
user course on Access. What I want to be able to do is have a contact

[quoted text clipped - 14 lines]

also
Client (not condo) - many companies.

 

[John Vinson]

I actually
had a form and subform but it didn't work quite the way I wanted it to.

Well... then fix the subform to work correctly.

I absolutely agree that going to multiple databases is A VERY BAD
IDEA.

Start with a properly normalized set of tables; *then* start designing
forms to work with those tables.

See the hints at:

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson[MVP]