Many to Many With Privacy/Security (without a combo box)

[PugnaxX]

Hi All!

I'm building a database that a customer would be updating via a form. This is for a pet sitting service, so I may have 1 or many co-owners of a pet, therefore one or many individuals for each Customer Account. Also, each individual may have 1 or many customer accounts.

I have the following tables
Customer Table which holds CustID (PK), Address, City, etc...
Individuals Table which has specific information about each individual: IndID (PK), MobilePhone, TextPreferences, etc
CustomerIndJunction Table (Junction table because Customer has many to many relationship with Individuals): CustID (PK/FK), IndID(PK/FK)

I am familiar with the concept that I need a master form (Customer) and a sub-form (CustomerIndJunction) to be able to tie Customer to Individuals through the Junction Table Relationship, however the most common way it seems this is done is to have a combo box or list box tied to the Individuals Table (IndID) to allow choice of one of those to be assigned as the value of IndID located in the Junction table.

The problem this presents, is I don't want each customer to be able to pull down a combo box and be able to view all the individuals contained in the individual table (all the individuals that are also customers) as this is non of their business.

I've searched diligently but haven't really been able to come up with a good idea on how to handle this and I'm really stumped.

Additional requirements that throw a kink in possible solutions is that I hope to web-enable the database, integrate it with a website, and build forms on the ipad and android to be able to interface with the database, so anything I do, I'm trying to figure out if it's compatible with these plans as well.

Thanks in advance for your time and any thoughts you may have!

 

[PugnaxX]

My apologies, it looks like I double posted this thread by accident

 

[dhookom]

Why are customers viewing a form that has the individual information? Do they need to edit their information?

I expect you could add a filter/criteria to the combo box that would display only individuals from the specific customer.

Duane
Hook'D on Access
MS Access MVP

 

[PugnaxX]

This particular form will be used to add new customers to the database. So the flow will be:

1. 1st Individual enters his/her information in individual table.
2. Subsequent individuals enter his/her information in individual table.
3. A "master" account is created for the "set" of individuals defined in 1 and 2.
4. The "master" account is next associated with all the individuals from 1 and 2 via junction table.

Does this clarify? Thanks for your input!!!

 

[dhookom]

Who performs step 3?

Why would individuals not just have a form for entering their information only?

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya PugnaxX . . .

I disagree with the logic of your relationships as well as the sequence of events that prescribes the creation of an account. I do tentatively see the following:

[ol][li]An owner and a pet prescribe an account ... period. And an owner can have many pets ... aka accounts.[/li]
[li]Individuals are simply other people who can [blue]escourt[/blue] the pet to and from the service and this is their only role. This saids an owner can have many escourts.[/li]
[li]An individual has to be approved and presented by an owner as well as any information about the individual.[/li][/ol]

All in all ... the sitting service appears to parallel a typical [blue]DayCare Center[/blue].

[blue]Your Thoughts? . . .[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!