Multiple Table Search

[l2010]

Hi,

I am having trouble conducting a search in multiple tables. Not sure if the tables should be relational or if I should use a script. Pretty much, I am trying to have the user enter in three fields to find a certain investor(first name, last name, partner name) and FM will scan through all of the tables of the different groups and will show which groups in which the investor is a member. There are going to be several dozen tables of investor groups. I tried making the tables relational, but I would only get the one result within the Current table. What I tried to do next was build a script using Show Custom Dialog and an If statement, but I'm not strong enough of a programmer to do that. Any tips?

[Ender]

Why are the different Investor Groups separate tables? Why not have one Group table (that defines each Group) and one Investor Group join table that identifies those Investors in each Group.

Then through the relationships, you can simply have portal of Investor Group records, showing the Groups that each Investor is in (on an Investor layout), or the Investors that are in each Group (on a Group layout).

[l2010]

Thank you. It seems my employer prefers to have everything on one table.

[Genx]

... Well I prefer chocolate to.. not chocolate, but doesn't mean i'm not going to not eat it... hmm, i think i lost myself.

Anyway, here's the point, unless this is structured properly as Ender suggests, there is no easy way to integrate this search function (though you could loop through all the tables, perform finds, duplicate records etc, or create some other sort of convoluted script).. kind of pointless really when the normalization would probably take less than a day.

If you normalized the solutions you could likely also generate much more meaningful reports and interact with the data easier.

Anyway, up to your employer if they want to sit in a flat file system I guess (if you have the choice, i'd recommend you recommend that they rethink their structure)... unless someone can come up with an easier way to do this.

[Ender]

Thank you. It seems my employer prefers to have everything on one table.

It's the job of the developer to use the best structure for each circumstance. The client generally doesn't know (or need to know) the underlying structure, they just want it to work.

Different structures sometimes have benefits and trade-offs that need to be weighed, and if you get to this point, you can ask the client which benefits and trade-offs they would prefer.

But first you should understand those benefits and trade-offs yourself. If it helps for you and the client to see what the differences are, make example files (complete with a simple interface) that illustrate each approach.

As Genx says, using multiple tables to "normalize" has numerous benefits. I'd wager if an example was properly constructed, it would be clear to you and your employer that this approach is better.

[Fenton]

How can you say "It seems my employer prefers to have everything on one table.", than say, "There are going to be several dozen tables of investor groups."?

These are contradictory statements. I can see no reason why you'd have multiple tables for groups. It's poor database design (to say the least). It is no wonder you're having trouble finding things.

Of course, you'd still have some work to do to find "people" in "groups". But that is a "normal" (pun) operation, and we'd be able to help with that.

One of the lessons to learn in database design is when to just say "No, that's not the way to do it," to a client/boss. Some of most confusing trouble I've had with databases is ones where I did not say that.

[Genx]

Lol, yeah i make it a rule, when i go into repair clients database -- especially ones that they've built themselves, If they won't listen to me, i turn it down, no matter the fee - I think it's just dishonest taking money from people if you're not gonna give them anything beneficial, and I tell them that

They then start listening to me, and also seem to think I have morals hehe.

P.s. Love the pun.

[l2010]

Ok,

I wasn't initially clear what I was trying to do with the database. This is a database that will organize the investors of plays. Each play, for tax purposes, is given an entity name (ie Death of a Salesman LLC). What my employer wants to do is be able to enter a name and get results that list the number of plays where that name has invested money. I tried to divide each play into its own table and link them but could not get a find script to work or even through defining relationships between the tables. So, that's why all the plays and their investors are in one table. I agree that structurally it isn't the best way to do it.

[Genx]

Do you want to just post a sample file of your current structure and we'll attempt to give you a hand with the (and i use the term loosely) "normalization".

[comment]

So, that's why all the plays and their investors are in one table. I agree that structurally it isn't the best way to do it.

It's actually quite simple: you need a table of Plays, a table of Investors, and a join table of Investments. A portal to Investments on a layout of Plays will tell you who invested how much in this play; a portal to Investments on a layout of Investors will tell you which plays has this person invested in.

[l2010]

That might not work because my employer wants to see all of that information in one view. These are the fields that are in the table now:

Entity Title

Partner Number

Partner Name

Partner Street Address

Partner City

Partner State

Partner Zip

Partner SSN

US Resident

Beginning Profit Percentage

Beginning Loss Percentage

Beginning Capital Precentage

Ending Capital Percentage

There are going to be some other financial fields inserted later, but these are some examples. One separate table for all of the investors would be difficult because there are some who are only invested in one play. My employer wants to have all of this information available for him to see in a printout when I do the searches. If there are divided, would the different information have to be combined? There are going to be dozens of plays entered, wouldn't that make separate tables confusing?

[l2010]

I wasn't sure if the post went through. My employer wants to see all of the information in one view. Here are some of the fields I have in the table:

Entity Name (Drag down)

Partner Number

Partner Name

Partner Street Address

Partner City

Partner State

Partner Zip

US Resident (Radio Button)

Entity Code (Check-list)

Forms Mailed

Forms Returned

Beginning Capital Percentage

Ending Capital Percentage

There are other fields that pertain to more of the investment in the beginning and end. A lot of the data is coming from a tax program. He wants to perform the find that will list the records with the same name occurring. Would it be best just divide all the information and have a portal that combines the data? The biggest problem is that, if each play is a table, there are going to dozens of tables.

Edited June 21, 2007 by Guest

[comment]

One separate table for all of the investors would be difficult because there are some who are only invested in one play.

I don't see a difficulty. If someone invested in one play only, then he/she will have only one related record in Investments. What you describe is a classic many-to-many relationship. See the demo I have posted here. The table names are slightly different, but the situation is the same. Note especially the two reports produced. If you add the a field of Sum to the Investments table you will be able to produce reports like:

Report of Investments by Play:

Death of a Salesman

• John Smith - $200.-

• Mary Jones - $150.-

Sub-total for Death of a Salesman: $350.-

Rebirth of a Nation

• John Smith - $100.-

• Joe Brown - $175.-

Sub-total for Rebirth of a Nation: $275.-

Grand Total: $625

Report of Investments by Investor:

John Smith

• Death of a Salesman - $200.-

• Rebirth of a Nation - $100.-

Sub-total for John Smith: $ 300.-

Mary Jones

• Death of a Salesman - $150.-

Sub-total for Mary Jones: $ 150.-

Joe Brown

• Rebirth of a Nation - $175.-

Sub-total for Joe Brown: $ 175.-

Grand Total: $625

---

P.S. The word "entity" has a special meaning in the database jargon, so it would be less confusing if you picked another term to describe whatever you meant by it - a production, maybe?

[l2010]

I have tried to replicate the example but it would not work. I have three tables:

Table 1: Show ID (Serial Number), Show Name

Table 2: Investor Information (Like you suggested)

Table 3: Amount of investor (I couldn't follow what you suggested here because everything is determined in percentages.)

When I linked the two, I tried importing from the tax program that has all of the information. Unfortunately, only one record showed up in Table 1 portal and not the whole list of investors, which I'm trying to do. Will I have to manually type in all of the information from the tax program because some investor names are written in reverse order if they are companies?

[comment]

If you have information about investments, you should import it into the Investments table (the one you called Amount of investor, but is at the same time also Amount of Show - it all depends from where you look at it).

I don't think I can say much more without seeing exactly what you're trying to import. But if all you have is percentages, then a vital piece of information is missing, namely percentages of what? Percentages should be CALCULATED from the individual sums invested.

[l2010]

I got it to work. Thank you. Where I diverged from your template was to have a link with the investors' social security numbers.