Need help with the schema for a run-time solution

[Rich S]

I'm writing a run-time solution and need a little guidance in how to set up the schema for its value lists; I'll use cars as an example.

In the "Main" table, there are two fields, both of them checkbox'ed value lists: one named Car Manufacturer and the other, Car Models.

In Main, after a user clicks on a Car Manufacturer in its checkbox'ed field, the Car Models' checkbox'ed field should autopopulate with that manufacturer's car models. Users would then click in all the checkboxes of models they're interested in; they'll appear in a scripted report.

As we all know, there are many car manufacturers and they have many models to choose from. So here's my quandary: I have the option of creating a separate table for each manufacturer where each one would have its models listed in a text field, List view.* The problem with that is I'd have to create _a lot_ of tables, making the schema a relational nightmare. The other option is to create a single "child" table that would have only two fields: Car Manufacturer and Model; all the available choices would be listed there. True, it would consist of a lot of records, but at least they'd all be in one table/place. (This is my preferred way of handling it.)

So, how should I set up Main so it'll work as described? Portals? A calculation? Look-ups?

Thanks in advance for your help!

*Since this is a run-time solution, it would be easier for future upgrades--and user editing--if the value lists entries were listed as separate lines/records in the "child" table using the "Use values from field...(specific field)" in Main instead of having them listed in Main as custom values in the Main table.

[bruceR]

Why would you create a table for each manufacturer?

Create a manufacturer table; and a model table. Put manufacturer Name and MfgID in Manufacturers.

Put fkMfgID and ModelName in Models. Set your value list to show only related records.

[Rich S]

Thanks again--I'll give it a try.