[Solved] Need Help With Query

[lit31686]

Hello,

I need a bit of help rewriting a query I have. First though, I'll give some background on my project.

I'm trying to create a database that shows the characteristics (i.e. Milk Free? Egg Free? Soy Free?) of a food (i.e. Bulks) based on the different ingredients (i.e. Raw Materials) that go into them. The raw materials that go into these bulks/foods can be purchased from different vendors and thus have different characteristics.

My end goal is to have a Form that shows the overall characteristics of the entire bulk, as well as the characteristics of the individual raw materials. This way, if a bulk contains milk, the user could see which raw material is causing this.
I have been able to put together the basic framework for the database thanks to the users of this site, but I’m running into a few more problems. My first issue is that I don’t think my database is normalized. Part of the problem is from the fact that there are multiple vendors for the same ingredient.

Second, several of the bulks I am working with include something called a Preblend. A preblend is essentially a mini bulk – it is a blend of raw materials that must be combined with each other before they are added to the rest of the raw materials in a bulk. For example:
BULK-8520 M&M Cookies includes RAWP-90089 Sugar, RAWP-90115 Butter, RAWP-90307 Eggs, RAWP-90365 Flour, and RAWP-91331 M&Ms Preblend
RAWP-91331 includes RAWP-90089 Sugar, RAWP-90115 Sugar, and RAWP-92111 Food Coloring

I’m having a hard time rewriting the “QUERY RM FREE OF BY VENDOR” so that it breaks down all of the ingredients in the preblend on the form.

Any help would be greatly appreciated. Also, if you have any suggestions on if I should set up the database a different way, feel free to let me know.

LINK: http://www.mediafire.com/download/ib9y9 ... -23-15.odb

[eremmel]

When you want to stick with the internal database (be care full to make lot's of backups), I think you data model looks reasonable. Only issue if there need to be a new 'free' topic to be added...

Your relation data model is not complete.

For your details on preblend you need to add to your query the results of PREBLEND -> RM with RM DESC. You can do that in the following way:

Code: Select all

SELECT RM."RM NUMBER", RM."RM NUMBER" AS "INI", RM."VENDOR", RM."DATE CHECKED", RM."MILK FREE", RM."EGG FREE", RM."WHEAT FREE", RM."SOY FREE", RM."FISH FREEE", RM."SHELLFISH FREE", RM."TREE NUT FREE", RM."PEANUT FREE", RM."GLUTEN FREE", RM."YEAST FREE", RM."SODIUM FREE", RM."SUGAR FREE", RM."ARTIFICIAL COLOR FREE", RM."ARTIFICIAL FLAVOR FREE", RM."ARTIFICIAL SWEETENER FREE", RM."PRESERVATIVE FREE", RM."VEGETARIAN", RM."VEGAN", RM."NON-GMO", RM."COUNTRY OF ORIGIN" 
FROM "RM FREE OF BY VENDOR" RM 
	INNER JOIN "RM NUMBER AND DESCRIPTION" RM_D ON RM."RM NUMBER" = RM_D."RM NUMBER"
UNION ALL
SELECT P."PREBLEND NUMBER", P_RM."RM NUMBER" AS "INI", P_RM."VENDOR", P_RM."DATE CHECKED", P_RM."MILK FREE", P_RM."EGG FREE", P_RM."WHEAT FREE", P_RM."SOY FREE", P_RM."FISH FREEE", P_RM."SHELLFISH FREE", P_RM."TREE NUT FREE", P_RM."PEANUT FREE", P_RM."GLUTEN FREE", P_RM."YEAST FREE", P_RM."SODIUM FREE", P_RM."SUGAR FREE", P_RM."ARTIFICIAL COLOR FREE", P_RM."ARTIFICIAL FLAVOR FREE", P_RM."ARTIFICIAL SWEETENER FREE", P_RM."PRESERVATIVE FREE", P_RM."VEGETARIAN", P_RM."VEGAN", P_RM."NON-GMO", P_RM."COUNTRY OF ORIGIN" 
FROM "PREBLENDS" P 
	left JOIN "RM FREE OF BY VENDOR" P_RM ON P."RM NUMBER" =  P_RM."RM NUMBER"
	left JOIN "RM NUMBER AND DESCRIPTION" P_RM_D ON P_RM."RM NUMBER" = P_RM_D."RM NUMBER"

You might need to flag Menu -> Edit -> 'Run SQL Command Directly'

Use this query as a View and use this view in your query. The only thing is that in your form you see all the vendors for all the RM's of the preblend, but you do not know what the RM is... I think it might be better to extend the form "RM NUMBER AND DESCRIPTION" with a list of all RM's of each preblend and leave the vendor as is. You can do this by adding an extra column to "RM NUMBER AND DESCRIPTION" where the PREBLEND - RM relation is mentioned. Use a comparable construct as above.

[lit31686]

I created a new query using the code you submitted and it looks like it worked. However, the RM Free Of By Vendor table on the Form doesn't change based on the Raw Material selected from RM Number and Description Table.

Any thoughts?

[lit31686]

Nevermind, I took the Query and changed it to a view. That seems to have solved my problem.

How can I edit the code you provided to include the RM Description from what is in the "RM NUMBER AND DESCRIPTION" table?

[eremmel]

Might be good first to try to understand the query. Learn SQL and then try to come with a proposal what you tried.

I limit myself to give direction to people and not to make it all happen.

[lit31686]

To be quite honest, I'm a newbie at this. I've been trying to read up on SQL as much as I can, but this is not my forte. I have been able to piece things together through trial and error, but this one is confusing me.

I tried adding "SELECT "RM NUMBER AND DESCRIPTION"."RM DESCRIPTION"" to the top of the code, but it didn't do anything. If you cannot help, hopefully someone else can step up and at the very least tell me which portion of the code I should focus on.

[Sliderule]

I tried adding "SELECT "RM NUMBER AND DESCRIPTION"."RM DESCRIPTION"" to the top of the code, but it didn't do anything. If you cannot help, hopefully someone else can step up and at the very least tell me which portion of the code I should focus on.

Code: Select all

SELECT 
   RM_D."RM DESCRIPTION",
   RM."RM NUMBER",
   RM."RM NUMBER" AS "INI",
   RM."VENDOR",
   RM."DATE CHECKED",
   RM."MILK FREE",
   RM."EGG FREE",
   RM."WHEAT FREE",
   RM."SOY FREE",
   RM."FISH FREEE",
   RM."SHELLFISH FREE",
   RM."TREE NUT FREE",
   RM."PEANUT FREE",
   RM."GLUTEN FREE",
   RM."YEAST FREE",
   RM."SODIUM FREE",
   RM."SUGAR FREE",
   RM."ARTIFICIAL COLOR FREE",
   RM."ARTIFICIAL FLAVOR FREE",
   RM."ARTIFICIAL SWEETENER FREE",
   RM."PRESERVATIVE FREE",
   RM."VEGETARIAN",
   RM."VEGAN",
   RM."NON-GMO",
   RM."COUNTRY OF ORIGIN"
FROM "RM FREE OF BY VENDOR" RM
INNER JOIN "RM NUMBER AND DESCRIPTION" RM_D ON RM."RM NUMBER" = RM_D."RM NUMBER"

UNION ALL

SELECT 
   P_RM_D."RM DESCRIPTION",
   P."PREBLEND NUMBER",
   P_RM."RM NUMBER" AS "INI",
   P_RM."VENDOR",
   P_RM."DATE CHECKED",
   P_RM."MILK FREE",
   P_RM."EGG FREE",
   P_RM."WHEAT FREE",
   P_RM."SOY FREE",
   P_RM."FISH FREEE",
   P_RM."SHELLFISH FREE",
   P_RM."TREE NUT FREE",
   P_RM."PEANUT FREE",
   P_RM."GLUTEN FREE",
   P_RM."YEAST FREE",
   P_RM."SODIUM FREE",
   P_RM."SUGAR FREE",
   P_RM."ARTIFICIAL COLOR FREE",
   P_RM."ARTIFICIAL FLAVOR FREE",
   P_RM."ARTIFICIAL SWEETENER FREE",
   P_RM."PRESERVATIVE FREE",
   P_RM."VEGETARIAN",
   P_RM."VEGAN",
   P_RM."NON-GMO",
   P_RM."COUNTRY OF ORIGIN"
FROM "PREBLENDS" P
LEFT JOIN "RM FREE OF BY VENDOR" P_RM ON P."RM NUMBER" = P_RM."RM NUMBER"
LEFT JOIN "RM NUMBER AND DESCRIPTION" P_RM_D ON P_RM."RM NUMBER" = P_RM_D."RM NUMBER"

Explanation:

1. When using a Query with the UNION ALL clause . . . the number of the columns in EACH select clause must match . . . both in number ( the number of columns defined in the select clause ), and, column type(s), for example, VARCHAR vs DECIMAL vs DATE etc.
2. COPY the code above, and, define your Query by following the steps here . . . it should run.
   1. Open / start your Base file ( *.odb )
   2. Click on the Queries icon on the left
   3. Under Tasks, click on: Create Query in SQL View...
   4. Copy and paste the query written above
   5. EITHER ( this is required because the Query is using a UNION ALL command, and, the Base Parser does not recognise it, but, the database back-end - HSQL does accept it )
      • On the task-bar, click on the Icon . . . SQL ( Run SQL command directly )
      • From the Menu: Edit -> Run SQL command directly
3. Run your Query . . . using F5, task-bar icon, or, Menu command
4. Save the Query, smile, and say: "Gee that was easy, now, all I have to do is mark the forum post as [Solved]"

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.