Adding Multiple Features Table/Subform to database 1

[clearwave]

How do I integrate my Form (picture #3) into my existing structure? I have tried to create a junction table but can never make it work as a subform on my 10 page tabbed form. (I created this "wizard-style" interface by setting the Tab button style to NONE.)

1) This is my original layout.

2) This is the actual table view showing the records.

3) This is a copy of what my current Form looks like except that it doesn't work yet. I'm trying to allow a User to just "Check-Off" the applicable items that apply for their new SERVICE from tblServices?

4) This is a copy of the New Service form which will have the Features applied if applicable.

Thanks in advance for any help with this! I'm a bit weak on the Many to Many thing if that's what this is.
c.w.

 

[willir]

cw
What you are trying to do, from what I see, is create a user-friendly interface for taking orders.

You envision the sales person to select all that apply with the checkboxes. Not a bad idea, but before proceeding, there a couple of things to consider. In the future...
- Will you add / remove services - probably yes.
- Will offer "packages" for a discount price?

Basically, does your system allow for "change"?

With the end user "presentation", you may find your self making lots of changes to accomodate change in services, etc. You have to change the form and all the code behind the form.

There are many variations on the solution. Here is something I have done. In one form, you display a list of services. The second form displays the order and order detail (form + subform). The operator double clicks on a service, and the entry is entered into the order detail section. Pretty user friendly, and because the "services" are presented as a record in a contineous form, any changes just require a change in record for the service.

What about packaged services?
This may require a re-thinking of your design. You "kind have" got this by have services and features. But the customer buys a service that include one or more features.

tblService
ServiceID - pk
ServiceName
ServicePrice
Active - boolean yes/no
GoodUntilDate - date
...etc

Discussion:
Similar to your approach. The "Active" yes/no boolean field to flag active and inactive services. Suppose you have a service or special offer that is offered. Two years later, you change the offer or discontinue the service. Since existing customer have already paid for the service, you can not changes things for these customers. Say a 100 min weekend service was not competitive. You still need to provide this service to existing customers, but you no longer want to offer it for future customers. The inactive service is not displayed to the sales person but you maintain database integrity by not deleting the service for those receiving the service.

The GoodUntilDate is just to tweak your idea juices to consider how you may offer "specials". The active

tblFeature
FeatureID - pk
FeatureName
DefaultPrice
...etc

Discussion:
This would be your call waiting, caller id, 911 services or features.

What is missing?
A feature can belong to more than one service.
A service can have more than one feature.

This is the "many-to-many" relationship you identified, and you need an intermediary or "joiner" table.

tblServiceFeature
ServiceID - foreign key to tblService
FeatureID - foreign key to tblFeature
RevDate - date
...etc

Primary key = ServiceID + FeatureID

Can also include the RevDate if you offer the same service at different dates at different prices (akin to airlines during peak season).


Purchased service...
You have to decide on how to track a service that is purchased. You may have made a committment on $10 per month for first 6 months; then the regular rate.

If you plan to do this, then you need one or two more tables. These tables capture the service and agreed upon price. Billing would be based on this table or tables, and not on the tblService since this can change for new cusotmers, etc.

A little complicated, but basically the same principle as as the tblService.

With one table...

tblServiceAgreement
ServiceAgreementID - pk
ServCustID - fk to tblServiceCustomer
ServiceID - foreign key to tblService
PriceAgreement - currency
StartDate
EndDate
...etc

Type of thing

If you want a coding example of the double-click on a service to create an entry in another table, post your email address. So as not to spammed, include a nonesense component such as

ChrisDeleteThisWhite@service.com

Richard


In review...
- customer purchases a service
- a service includes one or more features


Discussion

 

[clearwave]

Willir, Thanks so much for the detailed & insightful comments. Are you also in Telecom? I started out with alot of steam on this project & then came to a complete stand-still as I started laying out my forms. The limitations in my tables/relationships were becoming very evident to me and I can't seem to get anywhere now.

In response to your thread:

1) Yes to all your questions & suggestions.

2) You mentioned one Form to display a list of Services. In my database that list is in my tblCategories.(download my sample below to see) Is this the right place for those?

3) The joiner table for multiple Features. I have added it & temporarily created frmNewtblOrders. How would you connect the multiple features to this form? Any help on this would be much appreciated.

4) On my frmWizard, Page 003 I still have trouble seeing how to integrate multiple, checked features on the Features form to each Order? What I'm trying to do is let the user select a Service(#1) then enter/choose items (#2-#7) and lastly add Features(#8) to the Service(#1) chosen in the first place. Again, Any help on this would be much appreciated.

This is the link to download the database:

http://cerberus.clearwave.com/jerry/csvZIPLookup2003.zip

Again, I want to say thanks for the help. I have always thought that Order Entry database samples needed a simpler interface. Hopefully my sample will stir some interest.

PS: In regards to the database download sample, when finished the Page Tabs are set to "None" so that the user only has access to the Wizard's buttons.

Thanks in advance,
c.w.