Pull data for multiple information

Question

Hi, I would like some help. I am using Access 2007 and as I have just started to create some databases for home use.  The above is a screenshot of a Form I have created, in the background (as tabs) I show the 2 Tables I need help with.

I am trying to have it so I can run a query on one or more ingredients to return all recipes with this information.

At the moment I have 1 column of ingredients in the "Ingredient Listing" table and the same for the units of measure in the "Unit of Measure Listing" table. I think this should stay this way.

I have some basic queries set up, to pull information from my "Recipes" table which holds all the information. I have 15 separate columns to hold the unit of measure information and another 15 to hold the ingredient information. I am willing to have 15 identical/duplicate columns in the tables holding this information (instead of one), but there must be a better way.

Also I have tried to use information I have found to solve this problem, but I am unable to get what I think is the best and easiest solution to work. I found this "set Fieldb = fielda" and have tried to put it in an Expression Builder (with my database details) in the tables to duplicate information from 1 column to 15, no success.

If there is a way to query an ingredient without inputting its name under each (15) column, in separate rows I would be very interested. With the number of ingredients there are in a recipe, there is not enough rows in a query for me to do this.

I have attached this particular screenshot so you can see that some recipes show an ingredient more than once (generally with different units of measure). Also not all ingredients such as salt and pepper have a unit of measure to be recorded.

I feel like it is pointless to continue entering hundreds or thousands of recipes into this database until I can search for want I want properly. Any help with this would be greatly appreciated.

Answer 1

When you save a form you are saving the design of the object not the data. So all the properties of the form should be saved. If you lost the "look", its because you didn't save the changes.

Answer 2

I obviously only had issues when I was working on the forms fields/data.

Thanks Scottgem, I will stop worrying about that now.

Answer 3

1.  Wouldn't it be safer to run a delete query when you need to instead?

2.  Do I assume that you feel the 'update related fields' cascade is not optional, and is a must?

3.  Ken you state 'two foreign key columns, each of which references the primary key of the recipes table', but it won't let me enforce referential integrity.

1.  Not necessarily.  The enforcement of referential operations is an integral part of a well designed relational database.  This is why the functionality is included in Access, and every other relational database management system with which I'm familiar.  Knowing when to enforce cascade deletions and when not to is part of the developer's skill set.  

2.  Cascade updates are unnecessary if surrogate autonumber primary keys are used as the value of the key cannot be changed.  There are situations where natural keys have significant advantages, however, in which case cascade updates should be enforced.  An example is my Prescriptions demo on OneDrive.  This uses natural keys throughout, with the exception of the key of the Patients table, personal names being unsuitable as keys.  The interface for this demo relies very heavily on correlated combo boxes, a methodology for which natural keys make things far easier.  If a drug name were to be changed you would obviously want to the values in the columns in the three referencing tables in which the drug name is a foreign key to automatically be changed to the new value of the key of the referenced table.

3.  To create the relationships you need to add two instances of the referenced table (recipes in your case) to the relationships window and one instance of the table which models the adjacency list   You then relate the latter separately to each instance of the former.  In my BoM demo the model is:


Answer 4

Fixed, I understand it is joined correctly. But how do I need to enter the data as I what the name for the crust in the ingredient listing for the pie recipe?

Any other table design modifications needed?

Does the order in which you use access generally go; Table Relationship Structure, Create Forms, Data Entry, Queries then Reports.

Answer 5

Does the order in which you use access generally go; Table Relationship Structure, Create Forms, Data Entry, Queries then Reports.

Apart from entering dummy test data for debugging purposes, operational data entry should only be undertaken following extensive testing of the application.  It will be necessary to write queries before designing forms as forms will almost invariably be based on queries, not base tables.

There is one stage which should precede everything, however, which is the drawing up of an entity relationship diagram.  This looks similar to the relationship window, but is done on paper or by means of a diagramming application which has specific provision for relational database design.  Building the logical model conceptually in this way allows the developer to mentally test and amend it without the need to amend the actual tables and relationships.  

The building of an entity relationship model can be a highly formalized process and complex process in itself, but even in a very simplified form (the conceptual model) can save a lot of wasted effort when it comes to creating the database itself (the physical model).  Like the prospect of execution, it concentrates the mind wonderfully.