Recipe Ingredient Database - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Recipe Ingredient Database

David Hunter

6 years ago

Hello Richard. I have been viewing your Microsoft Access Videos and I will explain what I am trying to do in Microsoft Access. I have recently been teaching myself how to cook. One of the things I have been doing is going through various cookery books and typing up recipes into my computer. I have also made a note of the ingredients in a large Microsoft Excel spreadsheet and been round the neighbouring supermarkets round me and asked the store manager to tick a box one by one as to what ingredients they have and what they don't have.

What I do next is match the ingredients on the excel spreadsheet which my recipes list to see which supermarkets stock all the ingredients for each recipe. The long and tedious way of doing it is to type it in one by one and match off with the excel spreadsheet but for all 108 recipes this takes a good 3 days per supermarket, which is not exactly ideal if you have got a full time job. Someone did suggest to me that if you have Microsoft Access you could run some kind of query system to do the job automatically. If I was to move flat, doing this the tedious way would probably take a good 2 to 3 weeks, but thanks to Microsoft Access I could potentially get the job done within one afternoon.

I have purchased the following videos off your website:

Access Expert 1
Access Expert 2
Access Expert 4
Access Expert 7
Access Expert 8
Access Expert 13
Access Expert 14
Access Expert 16

I have a table for the checklist for ingredients and also a table with each ingredient, the quantity, the title of the recipe, the cookery book it came from and what index number. What I am trying to do is match between The list of ingredients and also the List of Recipes to see which supermarket stock which Recipes so I can make a 2 or 4 week rota which consists of a balance of your 5 essentials plus tasty food. The problem is when I run the IIF statement in the update query I get thefollowing error message:

"Microsoft Access didn't update 0 field(s) due to a type conversion failure, 286 record(s) due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations."

I can send you a copy of the Microsoft access file attached if you can agree to help me design a suitable query system.

Thank you,

David Hunter.

Richard Rost @Reply

6 years ago

Well you've got Access Expert 7 so that's what you need to cover many to many relationships and that's exactly what you're going to need for this database. You'll need a list of ingredients, a table for the recipe and the ingredients (with the appropriate junction table) and then a table for each grocery store and the ingredients they carry (another junction table). So there's 2 many to many relationships.

Then if you want to generate a list of what recipes each supermarket can provide you with all of the ingredients for you'll need another query with a join. You probably could do it without an IIF statement although this might be easier with some recordset work.

Richard Rost @Reply

6 years ago

Honestly I'd have to play with it to figure out the best solution. This does sound like a very interesting example to build one of my Templates out of. So if you're not in a hurry give me a little time and maybe I'll throw something together with a video. I am busy with a couple of other projects right now so it might be a week or two. If you're in a hurry though and you need help right away you can submit the database to me. Read over the information on my Consulting page and then get back to me.

	David Hunter OP @Reply 6 years ago
OK, I'm in no hurry and if you wish, you can use my database to build one of you Templates out of. I will e-mail it acorss to you. Thanks again, David Hunter.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View