473,324 Members | 2,531 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

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!
Mar 3 '16 #1
1 1148
zmbd
5,501 Expert Mod 4TB
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))?
May 6 '16 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Dalien51 | last post by:
Has anyone else encountered this? I wrote a Access 2000 database which has a linked table to a HTML file which worked perfectly. However, I Have recently installed Access 2007 and now when I use...
3
by: anthony | last post by:
I've been busy converting an existing 2003 database into 2007 by dint of creating a new accdb database and importing the objects into it. Everything went smoothly and all the objects imported...
21
by: Cele Balser | last post by:
Can someone tell me the VBA code to use in my module to select the External Data ribbon? I have a module that runs through some queries and at the end of them I want to export the query to Excel. I...
3
SBCUser666
by: SBCUser666 | last post by:
This is driving me crazy. I want 1 field from the first row of a table. SELECT TOP 1 fielda FROM mytable Every row in the table will have the same value in fielda. I just want to check to...
2
by: HSXWillH | last post by:
I'm working in Access 2007 and coming to something that's always caused me problems. I have a form titled frmUsers. There are 3 subforms that list exercise dates and exercise types for each user. ...
6
by: trixxnixon | last post by:
this code worked in access 97 and i am getting an error on the internet explorer variable. Is there an updated way of having vba open this program? Function getEmail(enumber As String) Dim url As...
5
by: Scorp Scorp | last post by:
Dear All, I joined newly to Bytes :) Am stuck in the following issue like a week, and couldent find proper fix :( I ll try to breif: I have a recordset, of record count over 4000 records....
3
by: chintu4love | last post by:
I need a help as I am new to the development aspect of access with Vba and I had to restrict few users ( i need to get users from environ function ) not to see the reports and some forms in the...
4
by: Prisoner362670 | last post by:
Hi, I have combo on birth date. A birth date can return a single record, but when it returns more than one it provides a filtered set of records. For example, if my table contains 20 records and 4...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.