MS Access 2007 VBA: How to go through records in one table and compare to another (bo

[Mickmcmanners]

Sorry if my question is not clear. I have not programmed VBA in a while and am pretty rusty. Basically I have a table of recipes (recipes) with recordIDs that have a one to many relationship to the ingredients (ingredients). I want to go through each recipe and compare the ingredients to another similar recipe and ingredients table (recipe1, ingredients1)

E.g.

Table 1 (Recipes):

Field: RecipeID: Value: 1

Table 2 (ingredients):

Field: RecipeID: Values: 1

Field: Ingredients: Values: flour, egg, sugar

Table 3 (recipe1):

Field: Recipe1ID: Values: 1, 2

Table 4 (ingredients1):

Field: Recipe1ID: Value: 1

Field: Ingredients: Values: self raising flour, Flour, butter

Field: Recipe1ID: Value: 2

Field: Ingredients: Values: egg, Flour, salt

I want to create another table that will hold a score for the results of the checks giving 10 points to each matched ingredient:

RecipeScores:

Field: Recipe: Value: 1

Field: Recipe1: Value: 1

Field: Score: Value: 10

Field: no_ingredients_ matched: Value: 1

Field: Recipe: Value: 1

Field: Recipe1: Value: 2

Field: Score: Value: 20

Field: no_ingredients_ matched: Value: 2

I was thinking about doing it using recordsets and arrays but am a bit stuck where to start! Appologies again if my question is not clear!

[zmbd]

The way you have described your table structures/design is very difficult to follow. It appears as if you have one table for each recipe, i.e. it appears that if you had 10 cake recipes then there is 10 tables.

1) If that's so then normalization is your friend
(( Database Normalization and Table Structures ))

2) You wouldn't create another table for what you are proposing, you would do this via query. Which would be easier if your database is normalized.

So let's start out, please clarify your database design. Is it normalized (ibdid Link in (1))?