[Solved] Need Help With Query

Creating tables and queries

[Solved] Need Help With Query

Postby lit31686 » Mon Nov 23, 2015 10:07 pm

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
Last edited by lit31686 on Wed Dec 02, 2015 12:06 am, edited 1 time in total.
OpenOffice 4.1.1 on Windows 7
lit31686
 
Posts: 20
Joined: Sat Oct 24, 2015 9:30 pm

Re: Need Help With Query

Postby eremmel » Thu Nov 26, 2015 12:35 am

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   Expand viewCollapse view
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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1041
Joined: Tue Dec 30, 2008 1:15 am

Re: Need Help With Query

Postby lit31686 » Mon Nov 30, 2015 10:08 pm

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?
OpenOffice 4.1.1 on Windows 7
lit31686
 
Posts: 20
Joined: Sat Oct 24, 2015 9:30 pm

Re: Need Help With Query

Postby lit31686 » Mon Nov 30, 2015 10:40 pm

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?
OpenOffice 4.1.1 on Windows 7
lit31686
 
Posts: 20
Joined: Sat Oct 24, 2015 9:30 pm

Re: Need Help With Query

Postby eremmel » Mon Nov 30, 2015 10:42 pm

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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1041
Joined: Tue Dec 30, 2008 1:15 am

Re: Need Help With Query

Postby lit31686 » Mon Nov 30, 2015 11:29 pm

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.
OpenOffice 4.1.1 on Windows 7
lit31686
 
Posts: 20
Joined: Sat Oct 24, 2015 9:30 pm

Re: Need Help With Query

Postby Sliderule » Tue Dec 01, 2015 12:43 am

lit31686 wrote: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   Expand viewCollapse view
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 :ucrazy: )
      • 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]" :bravo:
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.
User avatar
Sliderule
Volunteer
 
Posts: 1205
Joined: Thu Nov 29, 2007 9:46 am


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest