Data Structure

I'm battling to get my head around my data structure and would appreciate some help.

I have two main tables - Customers and Groups. Each Customer belongs to a Group. That is the easy part. Fields within Groups include: "Host" and "Possible Hosts". Within the Customer table are two fields: "Able to Host" and "Venue Approved". If both fields are "Yes", I would like to add the Customer to the list of "Possible Hosts" for the "Group" he belongs to. Ideally this "Possible Hosts" field is a combination of the Customer's Name and Address. Then finally the "Host" for the "Group" needs to be selected out of the list of "Possible Hosts" associated with that Group.

I am not to sure how to go about setting this up. So I have a series of connections between Customer and Groups, or do I need to set up additional tables? And then how do I get a dropdown to show just the "Possible Hosts" for a particular group?

If you can make sense of the above, I would really appreciate some help.


You can do this through the use of conditional flags and SUM fields. It would be a long winded explanation but if you want to contact me by email I will try and walk you through it

Basically you can set the value of a field based on values of others being set to YES. The Customer_Flag is a number field. Once created you use conditional rules to set it to 1 if both values are true.

In the groups database you use a SUM field and call it Customer_Flag, as long as the two databases are connected you can choose the Customer_Flag from the drop down list and it will copy the value into your Groups database. Now you know in this database when the conditions you require are true in the Customer Database.

The second part of your request is tricker as it requires the use of parent child relationships between databases and I would have to spend some time capturing screens on how to do this.

* Should I have a series of connections....