Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Displaying Multiple Entries For One Variable (1)

    I have multiple tables for roles (producer, director, etc.). My main table (project) contains a one-to-many (projectid to projectid) link with those tables. My project entry form contains input fields for each of these tables. (For example, the user can enter as many producers as he/she wishes for a particular project.) For confirmation, I have verified that the "role" table contains the correct projectids.

    I also have a "booking reports" table and form. Projects are linked to this table and form.

    Scenario: Project is called "Candlestick;" associated producers include "John" and "Suzy." I need to create a booking report which will pull the values for producers from the project form (....which, in turn, is linked to the producers table.)

    Question: I want to be able to open a booking report for Project Candlestick and "auto-populate" the booking report with the values for the variable producers. (Note: Hans was recently kind enough to show me how to autopopulate when using two tables through a query. Since a third form/table is thrown in on this and the values can be multiple lines, I'm thrown for a loop.)

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

    Re: Displaying Multiple Entries For One Variable (1)

    There are (at least) two possible solutions:

    1. Create a query joining the three tables. Then create a report based on the query. You can add grouping levels to the report, and decide whether repeated information will be displayed. The easiest way to do this is to use the Report Wizard to create the report; it will prompt you for grouping levels etc.

    2. Create a report based on the Projects table, a report based on the Producers table and a report based on the Directors table. Insert the latter two into the first one as subreports. If you link main report and subreports correctly, the subreports will display information for the producer(s)/director(s) associated with a project.

  3. #3
    New Lounger
    Join Date
    Mar 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying Multiple Entries For One Variable (1)

    Hans, thanks again. Although I was going down a slight different path, this helped. ...One other question (perhaps your second solution answers it, but I am a bit unclear)... My appointments and producer(s) table are linked to projects. I would like to generate a report which includes appointment information for a particular client and the producers involved. I created a query for this and all is fine. However, I also have a "casting director(s)" table. When I include this in the query I essentially get more data than I need. For example, a project has two casting directors (John and Jim) and one producer (Jill). The query ends up returning one column which reads John and Jim (correct), but in the next column it reads Jill and Jill. Although a second row is required for Jim, there is no need to push out Jill a second time. Any advise?

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

    Re: Displaying Multiple Entries For One Variable (1)

    Probably, the second alternative I outlined in my previous reply (with a subreport for each associated table) is the best solution for this problem - it can easily be extended for more tables, as long as they are all related to the main table.

  5. #5
    New Lounger
    Join Date
    Mar 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying Multiple Entries For One Variable (1)

    This worked and removed the duplication. My appointments contains a name variable (last name, first name). Via a QRY, I know that it is possible to prompt a user to enter that information (make-table query) and then generate a table. Given this, would you recommend that I use the resulting table in conjunction with the others?

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

    Re: Displaying Multiple Entries For One Variable (1)

    Why do you want to use a make table query? Can't you use a selection query (with one or more parameters if needed) as record source of the report?

  7. #7
    New Lounger
    Join Date
    Mar 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying Multiple Entries For One Variable (1)

    True, it probably does not matter. I have already created a selection query (against the "actor" column in appointments). Similar to your recommendation regarding casting director, etc. - I created a report from this query. This did not get me very far since I am getting a run-time error (keeps asking for actor's name). Whatever the case, since this report is dependent on who is entered as the actor, it still gets me nowhere.

    Basically, I simply want to have the user enter the actor's name (appointment table) and then generate a report which includes info from the app'ts, projects, casting director's, producer's tables.

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

    Re: Displaying Multiple Entries For One Variable (1)

    It should be possible to do all this using queries, but it's hard to give specific suggestions.

    One more tip: you could create an unbound form (empty record source) with a list box or combo box that displays the list of actors. That way, the user can select an actor instead of typing the name. You can use the value of the list box/combo box as parameter for the query: instead of [Enter Actor Name] or something like that, use [Forms]![frmSelectActor]![cmbActors] (with the appropriate form and control name substituted, of course.)

  9. #9
    New Lounger
    Join Date
    Mar 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying Multiple Entries For One Variable (1)

    Thanks, again. I will be submitting the project shortly and may not have an opportunity to try what you suggested - perhaps later.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Displaying Multiple Entries For One Variable (1)

    Hans suggestion should only take you 5-10 minutes at most.
    It really is as straight forward as Hans describes.
    Pat

Posting Permissions

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