Results 1 to 6 of 6
  1. #1
    Kingbenny is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Dec 2012
    Posts
    4

    Pulling data from multiple tables into one form or report

    Might have built my DB backwards but here is my problem:



    I need to pull data from multiple tables in order to show a "financial summary"

    Currently I have: Company; BalanceSheet; Debt; Liabilities; Income

    All tables have a lot of information (which is why I built them in multiple tables).

    I need to build a form where I can use a combo box to select a company from a list.
    Once selected - I need to the form to pull selected information from each of the above tables. (As well as perform some math functions)

    I've been struggling with the relationships (They don't seem to make a difference) and I believe I am above and beyond what the wizards will accomplish.

    I have read thread after thread but cannot seem to find a specific answer on how to accomplish this.

    To make matters more complex - Once finished I want to be able to select multiple companies and create a report from the fields mentioned above (IE: pick company A, B, and C and have all of there "current Assets" add up on one report)

    This task was originally achieved using an Excel spreadsheet but it has become to confusing for users and difficult to save information for future use.

    I believe all of my fields are constructed correctly - Now I just need to learn how to compile the data from multiple tables into one form/report

    Also - first time with a "Forum" in my life so please be patient.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Welcome. And I'm sure you realize that spreadsheet <> database.
    Do you have relationships between tables at this time? If so, can you create a jpg of it and post it.

    Could you also give us a description of your database and tables and what exactly you are trying to do?
    We only know what you tell us, so to help us help you we need to understand your situation.

    It is very possible to create a list of Companies to use in a report.

  3. #3
    Kingbenny is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Dec 2012
    Posts
    4
    Thank you for the reply. I'm very aware that SS <> DB. My weakness is starting with proper design in DBs On a scale of 1-10 I would consider my skillset a 9 on Excel but only a 3 with Access. (I'm great at making things shiny and easy to use - But I cannot write code and do not understand relationships...)

    I will gladly attach my DB as soon as I figure out how...???

    The "Capture below" is simply to show the flow of data


    The challenge is the large amount of input for the tables needs to be brought over into a "finanicail summary"

    If you can imagine -
    Three owners of three companies need to borrow money.
    Company Financials for all three need to be entered (going back three years)
    Personal financails for all three need to be entered (Going back three years)
    A summary of ALL needs to be presented in one report. (parsing information from all 6)
    (I haven't done any tables for Personal financials yet)

    Click image for larger version. 

Name:	Capture.jpg 
Views:	18 
Size:	99.6 KB 
ID:	10486

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You are going to have to research Normalization. There is a link in this article on Relational Database Concepts which I think you should read
    http://forums.aspfree.com/attachment...2&d=1201055452

    You should research the Normalization and work toward a meaningful data model /Entity Relationship Diagram before getting into the innards of Access.

    see this for a process to create ERD
    http://www.rogersaccesslibrary.com/T...lationship.zip
    here's another
    http://www.rogersaccesslibrary.com/f..._Databases.zip

    Good luck

  5. #5
    Kingbenny is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Dec 2012
    Posts
    4
    Thank you for the Normalization paper... I was pretty careful when creating the tables to keep items as atomic as possible. The "A, B, C, D" labels are for entering 4 years worth of data. Is there a more logical way to accomplisht this? (see screenshot below)


    Click image for larger version. 

Name:	Capture2.JPG 
Views:	14 
Size:	82.5 KB 
ID:	10489
    Also, I used very few calculated fields within my tables - as I understand this can be done within a report/form and does not need to be stored. (I'm certain I have much more to learn!)


    After reading, I still feel most of my relationships will be one to one and can be between IDs. (only one business will have one list of assets, etc.)


    My DB is too large to post. Any tricks there?

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Getting your database (tables and relationships) normalized and designed to meet your needs is major. Then getting your data from the un-normalized structures it is in currently to your normalized tables seem a large task.
    You will find several posts related to naming conventions, techniques, debugging....
    I'm sure that some familiar with financial analysis might advise you to keep the analytical tasks in Excel.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 12-26-2012, 07:22 PM
  2. Replies: 5
    Last Post: 06-13-2012, 09:34 AM
  3. Replies: 2
    Last Post: 03-29-2012, 04:03 AM
  4. Have 3 tables - problem with pulling data for query
    By wulfhund in forum Database Design
    Replies: 2
    Last Post: 08-13-2010, 05:38 AM
  5. Pulling only certain data from tables.
    By stevman22889 in forum Access
    Replies: 2
    Last Post: 07-15-2010, 06:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums