MS Access Compare two columns

Question

MS Access Compare two columns

jayoub

SSCertifiable

Points: 6991
More actions
February 11, 2018 at 10:35 am

#344035

MS Access Script
I hope you don’t mind me asking for script in an MS Access database, but I don’t know much about this topic
I am helping my daughter with a school project and it involves students with allergies and the foods that they can and cannot eat.
I need to be able to compare columns in two different tables
Table.Column 1 – ALLERGY.ALLERGY_NAME
Table.Column2 = INGREDIENT.INGREDIENT_NAME
The issue is that I need to compare names that closely match as well. For example, if the ALLERGY.ALLERGY_NAME is EGG for the student I need it to match EGG, EGGS, egg white, egg yoke basically any ingredient with egg in the name of the INGREDIENT.INGREDIENT_NAME table.column.
I tried using the LIKE '[ALLERGY_NAME]*' as the criteria in design view but the results were very large.
Please any feedback is appreciated.
Jeff

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63506 More actions · Answer 1

Jeff,
I would model it like this:
Person--(1,M)--FoodAllergy--(M,1)--FoodItem--(1,M)--Ingredient_In--(M,1)--Recipe
Person has FoodAllergy to FoodItem which can be an Ingredient in one or more Recipes.

The structure of a junction table, like FoodAllergy is like this:
CREATE TABLE FoodAllergy ( PersonID INT, FoodItemID INT CONSTRAINT pkFoodAllergy PRIMARY KEY (PersonID, FoodItemID) ...); -- missing FOREIGN KEY constraints to Person and Food Item Tables.

("Ingredient_In" would follow this pattern (RecipeID, FoodItemID) combination would be primary key.)

Then you would have a FoodItem table (FoodItemID would be primary key (INT), and then FoodItemName would be unique. I have worked on databases where people use text columns as foreign keys and allow "free text" entries. The combination makes searching and summarizing the data in the tables extremely hard.

Hope this helps.

jayoub SSCertifiable Points: 6991 More actions · Answer 2

Thank you very much for the feedback

I have actually decided to modify the database by addinga look up table for the Allergy values and adding a table called “Contains” asa one to many from the Food table. The “Contains”data will use the same drop down list as the allergy look up table.

The reason for this is so that I can perform the matchmuch easier and also I did realize today that if a student has an allergy toTree Nuts, which my daughter has, and an ingredient is Cashews or Walnuts itwould be impossible to make that match using just the allergy and ingredients tables.

Thanks for your help on this.

Jeff

pietlinden SSC Guru Points: 63506 More actions · Answer 3

General rule of thumb when you're not sure of a design - create the tables you think are correct. Then write queries to answer the questions you have about the data. If you can't answer simple questions with simple queries, usually there's a problem with your design.

Good luck!