Complex Database/Query Design

[zyxwvu44]

I will try to explain this as best as possible, please let me know if there's any more info i can provide in order to get a better response. I am making a database that links the following tables:

Product
Fund
Company

The value for each of these combinations will be a simple yes/no, but the problem with the relationship design comes in because of the companies. For each company there are multiple smaller companies, for instance B company is comprised of a1, a2 and a3 entities. The information I am puting into the database is specific to the a1, a2 and a3 entities, not B company as a whole. What i need to query is B company as a whole. For instance, i need to know that B company can sell widgets(product) through 1, 2, 3 and 4 (funds).

I can't think of a way to consolidate the data from the smaller companies into the data for the company as a whole, especially since the smaller companies may sell the same products in the same funds. My current Table relationships looks like this:
http://img338.imageshack.us/img338/5282/tableif8.jpg
but i don't know how to manage the company as a whole(eg. B Company above)


The second problem i have is once i have the data for the company as a whole i need to be able to query multiple funds. I thought this was going to be easy at first by switching "or"s to "and"s but, even after playing with parenthesis any "and"s that i add just make it so it is looking for "1 AND 2 AND 3 AND 4" in one field. what im trying to make it do is query (Product = z, Fund = 1, available? = yes) AND (product = z, fund = 2, available? = yes) etc.. and have it output a list of Companies (eg. B Company)

I realize that I can query like this by querying a crosstab with funds as columns, but the problem with that is funds will change because I have 5 groups of people using the database who want different sets of funds (and it will output entities, not companies).The database needs to be dynamic so when one group of people changes their preference of funds or we add a new fund I dont have to make a brand new query/form/report.

Any comments/quidance is appreciated, please let me know if you need any more information. Thanks!

 

[Guus2005]

I don't see a table "company" in your design and could you show me a few records in the "combine" table?
What are these funds you are talking about? I don't understand the relation between fund and product.

 

[zyxwvu44]

oops, the company is the counterparty. Each product can be traded in each fund, but only certain companies(counterparties) will do so, hence why i am making this database so i can easily look that up.
Right now the combine table looks like this:
Counterparty ID, Product ID, Fund ID, Avaiable?
1,2,1, yes
1,2,5, yes
1,5,20, yes
2,2,1, yes
5,16,20, yes

etc..

 

[KeithG]

You need a table for the main/parent company and then a table for the sub/child companies with a one to many relationship. One main company can have many child companies.

 

[zyxwvu44]

thanks keith, i think i got that working now

 

[zyxwvu44]

Okay now i have another problem with my relationships, i need to add a comments column that is linked to a specific Counterparty(as a whole, not the entities) and a specific product. For example something like this:
Counterparty A, Product A, "not preffered"
Counterparty A, Product B, "Quick transactions"
Counterparty B, Product D, "costs above average"

I have a relationship that can query this fine, but i cant link it to results in my main query. The main query comes up with Counterparty, and i select which product and funds to fitler. I want to have the comments show up with the Counterparty list in the detail section of my form. I tried setting the Counterparty criteria as the counterpart textbox in the detail section of my form but this only turns up the first entry, the rest come us as #name. Here is a link to my relationships, which appear to not be working correctly.

I would simply put the comments in the "Combine" table but then i would have to have many of the same comments because they are counterparty specific, not counterparty entity specific.

Thanks for your help!

 

[KeithG]

What about another table with the counterpartID, ProductID, and comment field.

 

[zyxwvu44]

I have this table in the relationships, but i dont know how/what to link it to? I need it to match up with the details section of a form that comes up with Counterparty, given a product