Not Sure How Possible This Is...

[phoenix13]

I have an idea for a form in Access, but from everything I've seen I'm not sure how feasible it is.

I would like to create a form that can pull data from three tables. I also would like to be able to have it add data to two of the tables and the third would be mostly a drop down with the possibility of allowing for data to be filled into it as well.

How possible is that?

[CraigDolphin]

Very. Though your description is too vague to be able to comment meaningfully.

A drop-down list can be based on a table of potential values in the Row Source property of a combo box, which stores the key value from that table into a field in the table which your form is bound to.

If your other two tables are related to the table that your main form is bound to in a one to many relationship, then you need to use subforms (probably in continuous form view) in order to facilitate your objective.

HTH

[phoenix13]

Since you were asking for specifics, I can give you those. A cemetery wants to have one form that would take out a lot of steps from the way paper work is done now. We want to be able to update an index, a mailing list (with partial information from the index). Is that also possible? Or would I be looking at working in VBA or writing a macro?

[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.

[phoenix13]

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.

Actually that does help quite a bit. Thank you.