Self-join and form issues

[Ragothlham]

Hello,



I am completely stuck with one issue of a task, and it is driving me insane because the logic seems so simply, but I am lost as to how to make Access do what I want.

Basically, I have an entity/table, Work, with has several fields. One of those fields is Work_Name, which contains the names of various jobs/services. This field will also contain "package" deals, which are made up of various combinations of the jobs/services in Work_Name. I also have a field that simply marks whether or not an observation in Work_Name is a package.

I know this is an issue with self-join, and in the Relationships window, I have added a second copy of the Work table, and have created a third table for the relationship "CONTAINS." tbl.Contains has two foreign keys, "Package_Name" and "Service_Name", both of which refer to "Work_Name" in the Work table.

I want to create a form based on Work where I can enter services and, if I am entering a package, use a subform to:

1) Enter the package in Work, and
2) Enter the package and its component parts/services into Contains.

How do you get Access to know that Contains should only be populated/updated when you're adding something that is a package, but also enforce integrity in the sense that both the name of the package has to agree on both tables, and that the services listed as a part of the package have to exist in Work as well?

Please help, driving myself insane.

Thanks.

[June7]

I think you meant: This table will also contain "package" deals, which are made up of various combinations...

I think it will be simpler if you also consider Purchases of only one Work item as a 'Package'. So tables and relationships would be:

Purchases
PurchaseID (primary key)
ClientID (foreign key)
PackageID (foreign key)
ActualTime
ScheduledDate

Packages
PackageID (primary key)
PackageName
CostPerHour

WorkPackages
WorkPackageID (primary key)
PackageID (foreign key
WorkID (foreign key)

Work
WorkID (primary key)
Description

The problem I see is the CostPerHour field. If you offer special pricing for 'packages' then need pricing in the Packages table. If you don't offer special pricing then why bother with 'packages'? Without 'packages', would do:

Purchases
PurchaseID (primary key)
ClientID (foreign key)
ActualTime
ScheduledDate

PurchaseDetails
PurchaseDetailsID (primary key)
PurchaseID (foreign key
WorkID (foreign key)

Work
WorkID (primary key)
CostPerHour
Description