2 Replies Latest reply on Mar 27, 2009 9:14 AM by paul.durso

    Newbie | Building Report with Many Fields



      Newbie | Building Report with Many Fields


      I have built a database with around 500 fields or which 400 or them are similar fields. Meaning, I have (Acc Owner 1, Acc Owner 2, Acc Owner 3, etc.) I want to build a report that will only list the field that have data in them. To make this more complicated, I have ten rows with 10 fields across. The rows are a group of data fields that go together. So, if I have data in one row, then I want the entire row to be shown in the report. I don't want empty rows in my report though. Is this possible? Do I need to set up my data fields differently? Any help would be greatly appreciated as I have completely hit the wall.

        • 1. Re: Newbie | Building Report with Many Fields

          You want to avoid having empty cells or fields. That's the ideal in a database. Sure if you don't know someone's weight, then for their record, it'll have to be left empty, but in general, the plan is to fill it as soon as you know or can, never plan on having reams of fields that will blank long-term or forever. 


          I'll assume acc is account.  Then you want to have a table Account, and to have another table Owner. You create unique field numbers that are auto-entered in each table. If its simply relating the Account table to the Owner table (so there no intermediate join table) then you'll need a foreignID in the Owner table where you'll store echo's of the unique ID number that is resident to the Account table.


          You'll end up with a layout, based on the Account table, where a portal is placed that is based on the related Owner table -- for this to be possible, you must first go into the relationship graph, and create a relationship between Account table occurrence's UniqueID and Owner table occurrence's foreignID. Click on the equal sign that appears on the graph after forming the relationship, then allow records to be created on the Owner side. 


          Now you'll be able to have as little or as many Owner's as you like, with no need or reason to have blank owners. If one account record has three owners, then there will be three associated owner records created in the owner table, if two then two, if one then one. Every account record will be related to as many owner records as necessary, and you won't be creating empty records in the owner table because there will be no need to do so. The way you're doing it now, is no better than just using spread sheet, and you're missing your real opportunity to go beyond that. 

          • 2. Re: Newbie | Building Report with Many Fields

            obeechi, Thank you for your response.

            I am confident that you know exactly what you are talking about. I have a few questions regarding your response.


            I have created an Account table, an owerner table and eight other tables to complete my rows.




            Account Table (Acc table = acc 1, acc 2, etc. Through acc 10)

            Owner Table (owner table = own 1, own 2, etc. Through own 10).

            I have eight other tables that I have created the same way.


            You said to have "unique field numbers that are auto-entered", How do I do that? I also need to create a foreignID, how do I do that and how many of the tables do I do that in? I would like everything based of off the "Owner Table" though.


            I am not sure what terms (words) to use so Let me re-explain what I am trying to accomplish. 

            I have a row of the following fields seperated by a period for this example


            owner1 .  acc1 . invst1 . opendate1 . orgvalue1 . curtvalue1 . closevalue1 . withdr1


            I have 10 rows that look like that, but with 2, 3, etc. behind the name. When I run a report I just want the data in the rows that have values in them. If a row has value it will always have to start with an owner so everything can be based on that value, but the owner name might not always be the same name.


            I believe from reading you response you understand my question, I just wanted to be clear.


            Again, thanks for all of your help!