Help assigning deals to investors in a form

[Trixdawabbit]

Hello,

I need help pulling investment deal names and displaying them along with an investor's other information. It can best be explained with an excel file I'm linking to. I can't share the database file, because it contains sensitive information. Please check out the excel file and let me know if you have any suggestions. Link: https://dl.dropboxusercontent.com/u/...0Database.xlsx (also attached)Help Me Investor Access Database.zipHelp Me Investor Access Database.zip

Thank you,



Nick

[June7]

Do these tables have 1-to-1 relationship? If so, why not just 1 table.

Multiple similar name fields indicates a non-normalized table structure. Investors are limited to 5 deals. You offer only 5 deals? If you ever add deals, will have to restructure tables, queries, forms, reports.

[Trixdawabbit]

Do these tables have 1-to-1 relationship? If so, why not just 1 table.

Multiple similar name fields indicates a non-normalized table structure. Investors are limited to 5 deals. You offer only 5 deals? If you ever add deals, will have to restructure tables, queries, forms, reports.

Thanks for responding so quickly! We have way more than five deals, I just stopped at five for illustrative purposes. I assume that when a deal is added, I can just add a new column onto the table.

The tables have the same investor names, and they are tied together by those names. Do you have any idea how I could pull the column title (the name of the deal) into a form based on whether an investor has a non-zero value in that column (columns are deals)?

Thank you

[June7]

As I said, add a new deal then need to modify tables, queries, forms, reports, and maybe even code.

Fields cannot be retrieved 'conditionally'. They are either in the query or they aren't.

Because the Deals table is not normalized and tables are 1-to-1, would simplify if you just combined the tables.

Otherwise, do a query that includes the Deals table with join type "Include all records from Clients and only those from Deals that match".

Or do a subform or listbox to retrieve the related Deals record.

[orange]

Further to June7's suggestion, and perhaps a bit more direct - you don't keep adding columns to tables. You design your tables and relationships to meet your requirements. But to appreciate the design process of a database you need to do a little research or work through a tutorial or two. Here is a tutorial that leads you through the basics to a sound design.

Good luck.