Originally Posted by
CraigDolphin
I think you misunderstood what I meant a bit. But you say this is for a cemetary? I have no idea what cemetary's actually track in their paperwork but I'll make a guess to demonstrate the kind of detail that would be useful to know to advise you.
I assume you need to track entities such as 'plots', 'customers','addresses', and suchlike?
And that not all 'customers' are currently in a plot (relatives, prepaid customers etc).
So, your customer table might look something like:
Customers
CustomerID (auto, pk)
CustomerFirstName
CustomerLastName
CustomerDeceased (y/n)
CustomerAddresses
CustomerAddressID (auto, pk)
CustomerID (fk)
AddressTypeID (fk)
AddressStartDate
AddressStreet
AddressCity
AddressState
AddressPostCode
AddressTypes
AddressTypeID (auto, pk)
AddressType (Text: values like 'Mailing Address','Physical Address', 'Delivery Address' etc)
Plots
PlotID (auto, pk)
PlotDesignation (text)
PlotLocation_Lat
PlotLocation_Long
PlotTypeID (fk)
etc
PlotTypes
PlotTypeID (auto, pk)
PlotType (Text: values like 'Grave', 'Urn', 'Plaque-Only' etc)
PlotInternmentHistory
PlotInternmentHistoryID (auto, pk)
PlotID (fk)
Interned_CustomerID (fk)
InternmentDate
DisinternmentDate
Paying_CustomerID (fk)
This kind of table structure would allow you to quickly and easily create a list of all plots, who's in those plots, who to send notifications to, and where, etc. Obviously you must have systems in place to ensure that any necessary data is collected, and that data is maintained as the people paying the maintenance fees etc die (not all of those relatives will necessarily be buried at your cemetary)
I have no idea whether this structure is suitable for use at a real cemetary or not, but it demonstrates the kind of detail needed to actually assist you.
So, if your main form were bound to the Customer table, you would likely need a subform for addresses which was bound to the customer addresses table. You would likely need a subform for the PlotInternmentHistory table too (deceased customers might get moved over time necessitating more than one record per customer). On those subforms, you would likely have combobox controls (dropdowns) looking up values from other tables like AddressType, but storing the AddressTypeID.
If your main form were based on table plots, you would likely need only one subform (based on the CustomerInternmentHistory table) since you may have more than one customer in a plot over time (I do not know if more than one person can be in a plot at a time or not, but this design can handle it either way). And you would likely need the combobox control to look up the plottype details for each plot.
Hopefully that makes sense and gets you started on your project.