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

I think I understand that the way it will know which relates to which is by me telling it, by manually inputting the ID references in the 'tblRecipeParts'.  (Ken, I have been going between your posts and BoM PartsTree information to improve my understanding of this. Thank you for supplying this.)

This is what I now have

If you think any changes are needed, please advise.

I am also waiting confirmation that having special characters in the data entered will not cause any issues,

such as - or / or ( ), please advise.

Answer 2

1.  I think I understand that the way it will know which relates to which is by me telling it, by manually inputting the ID references in the 'tblRecipeParts'.

2.  I am also waiting confirmation that having special characters in the data entered will not cause any issues,

such as - or / or ( ), please advise.

1.  Yes, but you would not insert the values manually.  They would be inserted via a recipe parts subform within a recipes parent form.  See the form for adding a new part in my BoM.accdb demo for an example.

2.  In most cases it won't be a problem if the column is of text data type.  One thing to be aware of is that, if you enter quotes characters as part of the value in a text column, there will be circumstances when you will need to  make special provision for this in your code when referencing the column, as the quotes character is the delimiter for text expressions.

Answer 3

1. I am unable to open BoM database (even though I had no problem opening PartsTree database, from the same zip). It came up with an unrecognised database format message.

I have done what I thought you meant, and have included here for comments;


The (related) ID data is only a number so it is a straight up entry field.

Further form questions:

2. Is there a way so that the label doesn't repeat per line/entry, only the data field? (Having only 1 label when it is running down the form, next to each other.)
3. Is their a way to stop the subforms from entering a new blank/record line once I have pressed a key in a field. This is leaving me with a blank entry line at the bottom of my subform information once everything is in. Their 'Default View' properties are 'Continuous Forms', I feel this is the probable cause.
4. You may also notice, some green markings on certain fields. When I deleted my Folder, CatergoryName, & CardNumber columns from my Recipe table, my form then showed #Name? for all these fields. The reason would be that their comboboxes have the 'Control Source' still with the name of the deleted columns. It is not allowing me to change it to a column which is not in the recipe table (which the form is based).

Am I able to put (either copy/drag) another table into my 'Field List' for 'Fields available for this view' section?

I didn't want to try it just in case I screwed something up.

5. Do I need to do another subform to be able to fix the Control Source issue? Or redo the whole form off a query which includes the relevant tables, instead of just being off this one?

Answer 4

1.  You do not need the control bound to the main recipe ID column in the subform.  The value is inserted automatically by the linking mechanism when this column is referenced as the LinlChildFields property.

2.  The label should be in the form header section of the subform, not in the details section.  That way it shows as a 'column heading'.

3.  You can set the subform's AllowAdditions property to False and include some mechanism to set it to True when you want to insert a new row, e.g. a command button.  In the subform's AfterInsert event procedure rest the value of the property to True.  But why do you object to the empty row at the bottom of the subform? This is usual in continuous forms view or datasheet view forms which allow the entry of new records.

4.  These values cannot be returned in controls in the parent form as there is no tree-structured chain of relationships which maps each recipe to a single value, as there would be in a case such as the common scenario:

Addresses>----Cities>----States>-----Countries

No, you cannot make the columns available by dragging the tables into the field list.  See below for the solution.

5.  You will need two subforms, one for folders and one for cards, both in continuous forms view and with the latter correlated with the former so that it shows the cards for the folder in the currently selected row of the former.  This does assume that model accurately reflects the entity and relationship types of the reality being modelled of course.  Only you can say whether that is the case; we have no basis on which to say whether it is or not.

For an example of correlated subforms see CorrelatedSubs.Zip in my OneDrive folder.  This uses Northwind data as an example of the  correlation of an order subform with an order details subform.

Answer 5

1. I just left it the way it was when I created the subform from that table. I removed the Control Source field, and the field just reads as Unbound. Unfortunately then as I viewed each form they all had "1" as their MainRecipeReferenceID.

Think I know what you meant now, that the Parent Form holds this information and so should be in it and not picked up through the subform. (I had removed it when I first set up my form.) Fixed.

2. Although if I put the label (only) in the header it is unable to have the field next to it, the field itself would still need to be in the 'Detail' and would then create a gap of at least 1 field. Is there another way?

I think I came up with a solution after taking out the MainRecipeReferenceID field, I only have one field (and label left). I put the label in the Parent form so the field can be next to it and be the only one repeating.

3. When I have all my information in I just want it to look neat and complete, not like it is waiting for extra information. I don't find in Datasheet View (like Excel), but would prefer the form to be cleaner.
4. Played around with subform in subform, it is fixed now. Thanks.

Is there a way to set up a filter for 1 combo box once a selection has been chosen from the previous, from the information in the tables and not that in the form. As I have all my categories and their folders listed in their tables but have not input recipes for them all yet. I think my joins and subforms makes this more complicated.

I would just like to confirm that you would generally do my form off a query with the 2 subforms? (for continuous entry)

Also you would do the bulk of your data entry through forms, right?