Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In my DB application (for an event mgt business), I have a table of events (parties) containing fields for event type, location, games. and date. Each event has multiple games that will be delivered. I also have a table of dealers that contains yes/no fields for each possible game (that the dealer can perform), the areas (six areas only) in which they can work, and the type of events they WILL NOT work (six available types can be declined - political campaign parties, for example).

    Here is my problem and I am trying to figure out how to approach it.

    I need to be able to assign dealers to an event and I would like to do this within the event form, if possible. Ideally, I would take some action (click something?) to assign a dealer then indicate which game I am seeking dealers for (there will almost always be multiples of the same game). Then I would be able to choose from the list of dealers presented - this list would contain only those dealers that can perform that game AND not otherwise be assigned to an event that day AND be available to work an event in that particular location (only six possible locations) AND (last one) willing to work that type of event.

    This sounds pretty complex and would like to find some solution for doing this rather keeping manual notes and writing in a calendar. Any thoughts on an approach?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The relationship between dealers and games is a many-to-many relationship: one dealer may be able to perform several games, and several dealers may be able to perform a particular game. So I would create a separate games table and a junction table with a record for each dealer-game combination.
    Similar for dealers and locations, and for dealers and events they're not willing to do.

    This would make it easier to create a query that returns the dealers that are available for a particular game at a particular event at a particular location.

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Okay, I think I understand the logic of that approach. Now, the practical matter of populating those tables... is that something that I would do manually or can they be populated from existing forms/tables? I'm not sure how to actually construct and populate the junction tables. I presume I would create the tables like any other.

    [quote name='HansV' post='772398' date='26-Apr-2009 13:42']The relationship between dealers and games is a many-to-many relationship: one dealer may be able to perform several games, and several dealers may be able to perform a particular game. So I would create a separate games table and a junction table with a record for each dealer-game combination.
    Similar for dealers and locations, and for dealers and events they're not willing to do.

    This would make it easier to create a query that returns the dealers that are available for a particular game at a particular event at a particular location.[/quote]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See this post (in reply to a question from you) for an example of how to handle data entry for a many-to-many relationship with a main form and subform.

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Hans - so much for my memory. Even so, I am ashamed to admit that I am as confused now as I was then despite your explanation. I understand that in the event form, I will need to have a subform for dealers and the dealers presented for selection will be based on a query.. However, what will I query? In order to assign a dealer, they will have to meet several criteria - skill, location, availability, etc. Here is a picture of my dealer form and the check boxes are all Yes/No fields in the table. Does this lend itself to populating any intermediate M2M table(s)?

    [quote name='HansV' post='772442' date='26-Apr-2009 22:29']See this post (in reply to a question from you) for an example of how to handle data entry for a many-to-many relationship with a main form and subform.[/quote]
    Attached Images Attached Images

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Don_Sadler' post='772593' date='28-Apr-2009 00:23']Does this lend itself to populating any intermediate M2M table(s)?[/quote]
    No, see Post 772398. You should NOT have fields for all these things in the main table. That's what the junction tables are for.

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How do I find Post 772398? When I click on that link I don't go to another post? How can I have the appearance I want in the form (check boxes) if those fields are not in the table associated with that form? I prefer to have the clean appearance of check boxes to facilitate data entry rather than the inserted scrolling subform.

    Unless... are you suggesting (or is it possible) that the subforms would actually appear to me part of the main form by using appropriate matching colors? Even so, Would that serve the purpose of populating the M2M table(s)?

    [quote name='HansV' post='772595' date='27-Apr-2009 15:30']No, see Post 772398. You should NOT have fields for all these things in the main table. That's what the junction tables are for.[/quote]

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    That post is #2 in the present topic (thread).

    You can make a subform look like part of the main form by setting its border to transparent etc.

    If you prefer to keep a myriad yes/no fields in the main table, it may still be possiblebut it will be more work. It would be helpful if you attached a stripped down, compacted and zipped copy of your database.

  9. #9
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans, I have attached a stripped down, compacted, and zipped version of the database.

    Honestly, although I rather like the interface of check boxes on the dealer, event, and affiliate forms, it is more important to have function than form. That said, I may have already gone way off on the wrong path. Operational requirements are that I can enter dealers into the DB with all their skills, attributes, and constraints. I also need to be able to enter events (along with games chosen by client and event attributes) into the DB then select Dealers for these events based on a skill match (games), event type match (no constraints), and availability of the dealer.

    I think I lack the vision to see how these parts fit together in the DB. Ideally, I should be able to go back and look at an event entry and see all the games and dealers that were associated with that particular event.

    I am certain my design is wrong because I cannot even come up with a way to do a dealer query based on the attributes & games of an event. Failing all else, that would at least provide a list from which to choose.

    Any advice is very welcome.


    [quote name='HansV' post='772602' date='27-Apr-2009 16:03']That post is #2 in the present topic (thread).

    You can make a subform look like part of the main form by setting its border to transparent etc.

    If you prefer to keep a myriad yes/no fields in the main table, it may still be possiblebut it will be more work. It would be helpful if you attached a stripped down, compacted and zipped copy of your database.[/quote]
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't have Access 2007 so I can't open .accdb databases.
    There are other Loungers who do have Access 2007 and who will undoubtedly take a look at your database.
    If you'd like me to have a go at it, please save your database in Access 2000 or Access 2002/2003 format, then zip and attach it.

  11. #11
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry about that. Here it is in 2003 format.


    [quote name='HansV' post='776579' date='23-May-2009 07:30']I don't have Access 2007 so I can't open .accdb databases.
    There are other Loungers who do have Access 2007 and who will undoubtedly take a look at your database.
    If you'd like me to have a go at it, please save your database in Access 2000 or Access 2002/2003 format, then zip and attach it.[/quote]
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I took a look at your database. Implementing the suggestions I gave you higher up in this topic would be possible, but it would take more time than I can afford to spend on it. Perhaps someone else is willing to do so, otherwise it might be a good idea to give it to a professional Access developer.

  13. #13
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I take it, then, that retaining those yes/no fields in the main table is the biggest constraint. If I were to forgo those and, instead, have little subforms for games the Dealers know, and venues they will work, and availability, then it would be simpler to have the functionality I desire... although not the look. Is this correct?

    [quote name='HansV' post='776625' date='23-May-2009 13:24']I took a look at your database. Implementing the suggestions I gave you higher up in this topic would be possible, but it would take more time than I can afford to spend on it. Perhaps someone else is willing to do so, otherwise it might be a good idea to give it to a professional Access developer.[/quote]

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Don_Sadler' post='777070' date='26-May-2009 23:53']I take it, then, that retaining those yes/no fields in the main table is the biggest constraint. If I were to forgo those and, instead, have little subforms for games the Dealers know, and venues they will work, and availability, then it would be simpler to have the functionality I desire... although not the look. Is this correct?[/quote]
    Yes, I think that a more normalized design would make it easier to create the queries that you need. It would still be quite complicated, though, because of the number of dimensions involved.

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='Don_Sadler' post='777070' date='27-May-2009 08:53']I take it, then, that retaining those yes/no fields in the main table is the biggest constraint. If I were to forgo those and, instead, have little subforms for games the Dealers know, and venues they will work, and availability, then it would be simpler to have the functionality I desire... although not the look. Is this correct?[/quote]
    If you design the db with a normaized design, you can usually find some way to get the look you want.
    The screen shot below shows information about a doctor's working times. The information about each day is in a subform, and comes from a related table, but data entry is still by ticking boxes. In this case, whenever a doctor record is created, then 7 records are created in the availability table, using code in the "after insert" event.
    [attachment=83979:doctordetails.gif]
    Attached Images Attached Images
    Regards
    John



Page 1 of 2 12 LastLast

Posting Permissions

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