Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Oct 2004
    Location
    Sacramento, California, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create form from multiple tables/queries (2000)

    Hi. I'm relatively new to doing more than just the basics in Access and am having a heck of a time trying to build a read-only form based on multiple tables and queries. I'm trying to display both current-year financial figures and next-year estimates -- all of which are stored in various existing tables.

    From some tables, I need to show multiple records for the current year and totals for the columns in these records. From other tables, I need to show only the sum of one field for all records for the current year. I also need to be able to add and subtract these different sums and totals and display the resulting figures on the form.

    I also need to be able to do what I have described above for the next-year (sometimes pulling from the same tables and other times not) on the same form. I then need to be able to add and subtract the various totals from both the current year and the next-year and display the results on the form.

    I've had some success combining queries and subforms to display the multiple records, but I'm having trouble figuring out how to display totals for the columns. Likewise, I've had some success simultaneously displaying the "sum of one field" data using subforms, but I'm at a loss for how to add and subtract all of these figures. I have not yet progressed to trying to display next-year records on this form and am wondering if it would even be possible, given that the form and subforms are linked based upon year.

    The subform "solution" that I've been working with seems cumbersome and ill-suited to the task at hand. I'm guessing that I've been barking up the wrong tree and that there is a simpler way to accomplish what I'm trying to accomplish--I just don't have the experience to know where to begin. Can anyone point me in the right direction? Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create form from multiple tables/queries (2000)

    Since your form needs to be read-only and to display totals, wouldn't a report be more appropriate? Reports are read-only by nature, you can easily sort and group data, and calculate sums in group headers or footers.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create form from multiple tables/queries (2000)

    One other pointer to raise is to ensure that the relationships in your underlying tables are correct. Since you are trying to create a form (or report) from multiple tables and queries, you should first ensure that your relationships are enforced referencially! Secondly, take a logical look at the relationships and group necessary data together in queries, and add calculated fields if required. Test the queries at this point to check if they can accept new records. (If it is summary queries where you have grouped the data, you cannot use it for data entry!)
    Once you have built the necessary queries, you can attempt to compile the data into a for (or report). Use the wizard as far as possible if you are not familiar with the intricacies of designing a hierarchial form!
    Regards,
    Rudi

  4. #4
    New Lounger
    Join Date
    Oct 2004
    Location
    Sacramento, California, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create form from multiple tables/queries (2000)

    Thanks Hans and Rudi!
    I'll take a closer look at this database I inherited and try to make something work with a report.

    Jon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •