How to transform record entries to fields in another table?

[rockypierre]

I have one table called "Make and Model," with two fields: MAKE, and MODEL.

Another table, called "Options," has two fields: OPTION NAME, and OPTION DESCRIPTION.

I would like to create a third table, with fields for MAKE, MODEL, and one Yes-No field for each OPTION NAME (which exist as Records in the "Options" table).

How can I do this, besides creating this new table from scratch?

 

[doulostheou]

The following suggestion assumes that there is a valid relationship between your two existing tables and that I am correctly understanding what you are trying to do

You simply need to setup a crosstab query based off of your options table. Then setup another query that links your make and model table to that crosstab. Turn that into a make table query, run it, and your done.

 

[cogent1]

If I understand correctly, you are trying to show relationships between Models and their options.

If so, you have to consider the following:

What is the situation with your Options?

a) One model can have Many options?

b) An option is available for more than one model?

If only a) is true, you don't need a third table.

If a) & b) are true, you do need a third table, but you don't need the yes/no field.You need key fields in the existing tables and foreign keys in the third. (not as bad as it sounds.)

So I'll await your response before taking this further.

 

[rockypierre]

Thank you both for clear and cogent replies.

1. I've attempted the crosstab query from the Options table, but get the error: "Not Enough Fields," since the Options table now has only three fields: "Option" and "Option Description," plus an auto-number primary key.

2. I did create a relationship between the Options table and the Make and Model table.

3. Answer to your inquiry is "b." A model can have many options, and these options can apply to many models.

4. Sorry to create further complications, but since you mentioned the yes/no field: some options are "standard" on some models: a field called "Standard" is needed for each model and option; "yes" in "Standard" for the intersection of that model and option should force "yes" in the field for that option (for that model).

 

[cogent1]

You need :

in Table1:Makes&Models

A primary Autonumber key field (MakeID)

In Table2: Options

A primary Autonumber Key field (OptionId)

in table3: ModelOptions

A primary Autonumber Key field (ModelOptionID)
A foreign key Long Integer field (MakeID
A foreign Key Long integer field (OptionID)
A Yes/No field(Standard Option?)

Remove the join between the options and Models table!

in the Relationships table create a one-to-many join between the key fields in Tables 1 & 2 and the corresponding foreign keys in Table 3. Enable referential integrity and cascade deletes & updates.

I know of no easy way to rebuild your 2 existing tables because there is an undocumented many to many relationship between them, which you now have to document. You will have to create forms with subforms so you can call up a model and show a list of all the options and whether they are standard or not.

How many records are involved in each table?

 

[rockypierre]

Thanks.

Done, as per your instructions.

# of records:

MakeModel....259

Options....110

 

[cogent1]

These are manageable numbers . Do you need further advice or are you up to speed with subforms? If you need more explanation about what your third table does, investigate the "Order Details" table in Northwind. Post again if you need more specific help.

 

[rockypierre]

Thanks again.

I created the database as per your instructions (see attached) and compared it with Northwind.

This is my first encounter with subforms, hence some questions remain:

1. Where's the data? Opening the subforms in Northwind shows all fields and corresponding records from the related tables. Mine are blank. Is this what you mean by "I know of no easy way to rebuild your 2 existing tables because there is an undocumented many to many relationship between them, which you now have to document?" I do understand what is meant by "documenting" the relationship...could not find a definition in Access Help, or in a couple of texts. Will I need to re-enter all the data? If so, the numbers are manageable, as you say.

2. The Order Details table: the field "UnitPrice' is common with the Products table. I'm assuming it follows the ProductID, but I do not understand the connection, if one exists, since the unit price varies among orders for the same product.

3. As I'm somewhat pressed (as usual) to get something built to receive data entry immediately, I've resorted to an Excel Spreadsheet with one worksheet for each Make: rows are Options and columns are Models, with permissible entries as: Y (for Yes, available), S (for Standard), and Null (not available).

Will this present difficulties later when importing the spreadsheet into Access? This step is necessary, since the ultimate destination is dynamic web pages via Cold Fusion (I'm a web designer by trade...the database guy is out sick!).

Many thanks once more for your time.

 

[rockypierre]

oops, typo...

"I do understand what is meant by "documenting" "

should read:

"I do NOT understand what is meant by "documenting" "

 

[cogent1]

Rocky_pierre

That wasn't "Technospeak", just my way of saying that you haven't got any linkage established between Makes and Options, and you now have to do this.

You have blank records for exactly this reason-because your query is asking "show me the records in the "Option" table that have the same ID as records in the "Model" table" - and there aren't any yet!

Some observations; Don't put hyphens in your field names: unnecessary and can cause complications (don't ask!)

Scrap the Option and Model text fields in your ModelOption table. In a relational database, non-key fields need appear once only.

In your linking table, OptionID and ModelID should be Joint primary key fields with Duplicates allowed. This ensures that, for example, you can't have the same option more than once for a given model.

Having corrected thes points, You will set up a form via the wizard and pull all the fields from the Make table and all the fields from the ModelOptions table into the grid. The wizard will "get" the relationship and..

This is getting too wordy. I'm going to set up an example and send it . Suffice it to say that once you've done this leg work, you'll be able to display a form which shows all models using a particular option, or another showing options used by a particular model, which is what you want , I hope!

 

[cogent1]

Sample sent to your email address