Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    10

    Unanswered: Base Form on more than one query

    I have a simple database (no complex relationships) but unfortunately there are a lot of fields. Since Access limits the number of fields in a table, I broke my table up into several separate one-to-one tables. Now I want to build a form for data entry and I'm bumping up against the number of fields in a record set. So again, I want to break my query up into two parallel queries (A query that joins tables 1-8 and a table that joins tables 9-12 and both are linked to table 1 which provides the linking key). Is there a way I can base some of the fields of my form on Query1 and some of the fields in my form on Query2?

    Are there any other tricks for overcoming the limitations of Access?

    Thanks,

    Becky

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    of course.

    use a query (use the design grid to get started). show all three tables/queries you discussed in your post. drag the linking field "from" one table/query "to" the other (doesn't actually move of course, but creates a "joining line"). repeat for the other related table, and drag your fields of interest into the grid.

    you might have to mess around with a right-click/join-properties on the "joining line" to get the result you are looking for.

    ...review the SQL you created: design grid can't do everything that A can do, so it's nice to write this stuff manually.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by bmcd
    Are there any other tricks for overcoming the limitations of Access?
    Maybe you want to use a multi-part form for entering the data. You will have to look at your data and consider a logical way to slice it up for entering the data. For example a New Product R&D:

    Open Form One to add a record:
    Enter the Name of the R&D Project, etc.
    <Click Next Button> Opens Form 2
    Enter Budget information, etc.
    <Click Next Button> Opens Form 3, Closes Form 2
    Enter Team Member Names, etc.
    <Click Next Button> Opens Form 4, Closes Form 3
    Enter Milestone Dates...

    This way you can have unlimited number of fields filled in without giving to much data in any one form. For the form filling process you would set the filter property of the 'Next' form to open to the same record as the previous form. Additionally when entering a new record you'll need to have a process for appending the record to each of the related tables. You can either set this in the Relationship properties or use a query or DAO/ADO append record method on each related table.
    ~

    Bill

  4. #4
    Join Date
    Aug 2004
    Posts
    10
    Bill,

    Thanks. I think this is the most logical way to do things. I will find a nice break so that the queries I create don't return more than 255 fields in a record set and I will create a form that corresponds to each query. I have all the relationships set up properly so hopefully, everything will work in data entry mode.

    Thanks!

    Becky

Posting Permissions

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