Strategy/planning question

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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.

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.

Reply | Quote

See [post=”364203″]this post[/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.

Reply | Quote

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)?

See [post=”364203″]this post[/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.

Reply | Quote

Does this lend itself to populating any intermediate M2M table(s)?

No, see [post=”772398″]Post 772398[/post]. You should NOT have fields for all these things in the main table. That’s what the junction tables are for.

Reply | Quote

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)?

No, see [post=”772398″]Post 772398[/post]. You should NOT have fields for all these things in the main table. That’s what the junction tables are for.

Reply | Quote

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.

Reply | Quote

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.

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.

Reply | Quote

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.

Reply | Quote

Sorry about that. Here it is in 2003 format.

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.

Reply | Quote

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.

Reply | Quote

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?

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.

Reply | Quote

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?

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.

Reply | Quote

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?

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.

Reply | Quote

Thank you Hans & John,

I am now rebuilding my DB from scratch and trying to normalize as much as possible. I think I have the concepts down although I am intrigued by John’s reference to inserting seven reords using code… That is a great idea and I would love to see the code that will automatically insert seven records.

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.

Reply | Quote

The exact details depend on the structure of your tables, but the general idea is like this:

– It’s best to have a table that contains as many records as you need to create. For example if you want to create a record for each day of the week, you need a table tblDays with records for Sunday, Monday, …, Saturday.
– Add a record to the main form (or to the table behind the main form).
– Retrieve the primary key of the new record (if the primary key field is an AutoNumber field, its value will be generated when you start creating the new record.
– Use this value to create an SQL statement that inserts a record into the subform table for each of the records of the auxiliary table. Air code:

lngID = … ‘ the primary key value
strSQL = “INSERT INTO tblSub (DoctorID, DayID) SELECT ” & lngID & “, DayID FROM tblDays”
CurrentDb.Execute strSQL

Reply | Quote

Here is my version, which is called in the After Insert event for Doctors

Code:

Public Sub sbCreateAvailability(lngdoctorID As Long)
	Dim sql As String
	Dim inti As Integer
	
		For inti = 1 To 7
			  sql = "Insert into tblAvailability  (DoctorID, DayofWeek) Values(" & lngdoctorID & "," & inti & ")"
		   ' Debug.Print sql
			CurrentDb.Execute sql
		Next inti
 
End Sub

Reply | Quote