SBS - The best value in QMS software

Creating a Multiple Table SQL (Structured Query Language) Query in Minitab

C

candid

#1
Hi guys, just found this awesome page through google here and now i'm giving this a try..

I need help creating a SQL query in minitab.

My database is structured the following way:

XPI_INJ_TRACE is the name of the db.

Tables:

dbo.T_INJ_TRACEABILITY
dbo.T_EOL_TEST
dbo.T_INJ_ASSEMBLY

Just by looking at the names, you can probably figure out i'm doing this for a work related problem, namely that my collegues are not friends with SQL Server Manager, which i use for SQL querys before i paste into minitab.
So i want to creat a macro, and later maybe an executable minitab file.

Now, i have figured out how to do the connect part, it looks like this:

ODBC;
Connect "Description=Sesosco0632;DRIVER=SQL Server;SERVER=SESOCO0632;UID=XP" &
"IUser;PWD=BLANK;APP=Microsoft Data Access Components;WSID=N28609;DATA" &
"BASE=XPI_INJ_TRACE";

So, the part i need help with, is the SQL string.

What i want to do is the following;

i need 1 thing from each table, and how to connect them together..

So something like this:

dbo.T_EOL_TEST.DATE_TIME <-- DATE_TIME from table dbo.T_INJ_TEST
dbo.T_INJ_ASSEMBLY.OP1MEASUREMENT <-- OP1MEASUREMENT from table dbo.T_INJ_ASSEMBLY
dbo.T_INJ_TRACEABILITY.ASSEMBLYLINE <-- ASSEMBLYLINE from table dbo.T_INJ_TRACEABILITY

Now, they all have a column called ID_TEST which link's the tables together.

What i want is a query that gives me the OP1MEASUREMENT, plus the DATE_TIME in 1 column in Minitab, and has the "Where" function set to:

where dbo.T_INJ_TRACEABILITY.ASSEMBLYLINE = '1'

Also in the select part, i want to have only SELECT TOP 1000.

So what i would do for me, is to select the TOP 1000 measurements from ASSEMBLYLINE 1.
This would help me figure out everything i need for now - And i apreciate any help i can get.
 
Elsmar Forum Sponsor
B

Barbara B

#2
Re: Multiple Table SQL Query in Minitab

AFAIK you have to use three different ODBC commands to select variables out of three different tables in Minitab. In the Query Database menu you could generate a SQL statement.

Minitab provides a course Makro Programming which covers ODBC/SQL syntax along with other topics. For automisation the ODBC/SQL command has to be invoked using a Global Macro (GMACRO). Here's one option for getting the appropriate syntax out of the history folder:
  1. File > Query Database (ODBC)
  2. Click "Machine Data Source" tab
  3. Double-click "MS Access Database" (or the appropriate format for your database)
  4. Login and find the database
  5. Uncheck "List available tables and fields in alphabetical order" (don't know why this is important or what will be changed by unchecking)
  6. Choose the table from the drop-down list "available tables" and the variables out of this table (the ID should be selected for all tables to have the link between the tables within the data sheet)
  7. Click "Use rows" to generate the SQL statement (looks like the usual calculator in Minitab)

Date/time values in the SQL command have to be enclosed by the hash key # (e.g. #6/15/2012#), text values by single quotes ' for Microsoft Access databases (e.g. 'M1') and double quotes " (e.g. "M1") for Oracle or SQL server databases. Numeric values are used as they are (without any enclosing signs).

Depending on the variables format in your database the SQL string in "Use Rows" dialog box for selecting only ASSEMBLYLINE=1 could be
  • where ASSEMBLYLINE = '1' (text format)
  • where ASSEMBLYLINE = 1 (numeric format)

After selecting variables out of the three tables there should be six columns in the worksheet:
c1: ID_TEST (out of dbo.T_EOL_TEST)
c2: DATE_TIME (out of dbo.T_EOL_TEST)
c3: ID_TEST_1 (out of dbo.T_INJ_ASSEMBLY)
c4: OP1MEASUREMENT (out of dbo.T_INJ_ASSEMBLY)
c5: ID_TEST_2 (out of dbo.T_INJ_TRACEABILITY)
c6: ASSEMBLYLINE (out of dbo.T_INJ_TRACEABILITY)

The link between c2, c4 and c6 is given with ID_TEST, but maybe there are different ID_TEST, ID_TEST_1 and ID_TEST_2 values in the Minitab worksheet. To get the correct values for the measurement data (OP1MEASUREMENT) the menu
Data > Code > Use Conversion Table
could be used (see attachment). The selected data for DATE_TIME and ASSEMBLYLINE is stored in c7 "date" and c8 "line".

Afterwards the four columns ID_TEST_1, OP1MEASUREMENT, date and line are copied to a new worksheet (optional: selecting line=1 here). If you only want to have the first 1000 rows in the final worksheet, choose "Include row numbers" and enter 1:1000 (see attachments).

Hope this helps :bigwave:

Barbara
 

Attachments

C

candid

#3
Re: Multiple Table SQL Query in Minitab

Thanks Barbara for your quick reply and your help.

However, these functions are ok for me to use, but it get's very tricky for doing something that is quite easy for me to do in SQL instead.

What i want instead, is to make a macro out of this, and i am pretty sure minitab sql query can handle multiple tables from the same database.

I mainly need help with connecting the tables, and getting 1 SQL string to do so for me.

Thanks again anyways - If there is no other way to use multiple tables, maybe i'll go through your suggestion again and really try it out.
 
B

Barbara B

#4
Re: Multiple Table SQL Query in Minitab

Sorry for the misunderstanding: You don't have to use the menus for this more than once. First you generate the appropriate code for ODBC/SQL queries and worksheet manipulation. Afterwards you copy the code from the history folder and use it for your GMacro.

I'm not quite sure if you can select from different databases or database tables within 1 GMacro, but you can make different GMacros for every single ODBC/SQL query (e.g. %Assembly, %Line) and invoke these in another GMacro (e.g. %ProcessAnalysis). In the end you'll have 1 GMacro which does all the work.

Maybe there's a more straightforward way to achieve this and somebody else in the Cove could provide additional informations. Alternatively you can contact your local Minitab support.
 
C

candid

#5
Re: Multiple Table SQL Query in Minitab

My own attempt looks like this:

SQLString " SELECT DISTINCTROW ""dbo"".""T_INJ_ASSEMBLY"".""MEASUREMENT1"", ""dbo"".""T_INJ_TRACEABILITY"".""ASSEMBLYLINE""," &
""" dbo"".""T_INJ_ASSEMBLY"".""MEASUREMENT1"", ""dbo"".""T_INJ_TRACEABILITY"".""ASSEMBLYLINE""," &
" FROM ""dbo"".""T_INJ_ASSEMBLY"" INNER JOIN ""dbo"".""T_INJ_TRACEABILITY"" ON ""dbo"".""T_INJ_ASSEMBLY"".""ID_TEST""= ""dbo"".""T_INJ_TRACEABILITY"".""ID_TEST""" ".
:confused:
 
B

Barbara B

#6
Re: Multiple Table SQL Query in Minitab

My own attempt looks like this:
Code:
SQLString " SELECT DISTINCTROW ""dbo"".""T_INJ_ASSEMBLY"".""MEASUREMENT1"", ""dbo"".""T_INJ_TRACEABILITY"".""ASSEMBLYLINE""," & 
""" dbo"".""T_INJ_ASSEMBLY"".""MEASUREMENT1"", ""dbo"".""T_INJ_TRACEABILITY"".""ASSEMBLYLINE""," & 
" FROM ""dbo"".""T_INJ_ASSEMBLY"" INNER JOIN ""dbo"".""T_INJ_TRACEABILITY"" ON ""dbo"".""T_INJ_ASSEMBLY"".""ID_TEST""= ""dbo"".""T_INJ_TRACEABILITY"".""ID_TEST""" ".
There seems to be no "Where"-statement (e.g. where ASSEMBLYLINE = 1), or do I miss something here? What happens if you create a GMacro out of the ODBC and SQL lines in the history folder?
 
C

candid

#7
Re: Multiple Table SQL Query in Minitab

No you're right.

This is without the where part, instead i try using some suggestions from Minitab's own homepage regarding multiple table query, maybe if you read through you understand better than i do, have a go if you feel like it.

I'm not allowed to post Links on this forum yet, but it's on minitab dot com.
Then search for 'ID 97' - it's called "Querying more than one table using ODBC.."
 
B

Barbara B

#8
Re: Multiple Table SQL Query in Minitab

Here's the link for ID 97: Querying more than one table using ODBC (Minitab knowledgebase). But I can't read more of your code because I don't have access to your database.

My suggestion is
  1. Try the ODBC query until you have all the variables you need to have in your worksheet.
  2. Afterwards create an appropriate SQL string for the ODBC query with "Use rows" (see above).
  3. Finally copy the code from the history folder and create a GMacro for the ODBC/SQL commands.
 
C

candid

#9
Re: Multiple Table SQL Query in Minitab

Well, I really don't want to do it that way..

Since I have 700.000+ entries in the database, I really need to use the "where" function, and In the table T_INJ_ASSEMBLY there is only measurement data, so I have to JOIN it with T_INJ_TRACEABILITY to set "where ASSEMBLYLINE=1" (Now my data is down to 120.000+ entries) then I also would like to use "SELECT TOP 300 ORDER by T_EOL_TEST.DATE_TIME desc". So i cant do one query for measurement data, one query for dates, and one query for assemblylines.. It just makes for one horrible query really..
 
B

Barbara B

#10
Re: Multiple Table SQL Query in Minitab

The way you eat an elephant is one bit at a time :bigwave: It is tricky and time-consuming to get the right ODBC-SQL-code, but once established you could use it for the next years (if your database structure remains the same). And you could also use parameters within a GMacro, e.g. to select the assembly line which should be extracted (in case you want to analyze data of assembly line=2 or 3 in future analysis).

Probably it would be less annoying to get some help. My suggestion is the local Minitab support which can be contacted out of Minitab (Help > Contact Us) or via the Minitab website (if you're not located in the US, change default language and country on the upper right).
 
Thread starter Similar threads Forum Replies Date
R Accelerated Aging - Creating test samples - Implantable medical device Question Other Medical Device Related Standards 4
L Hazardous Waste - Tips for creating standardized training Miscellaneous Environmental Standards and EMS Related Discussions 2
M Informational Creating a post market surveillance (PMS) system for medical devices – Part 1 Medical Device and FDA Regulations and Standards News 7
J Sample size for creating a data base as a reference to a tested variable Other Medical Device and Orthopedic Related Topics 6
8 Creating Flow and Pull Game Lean in Manufacturing and Service Industries 1
B Main responsibility for Control Plans - creating and maintaining FMEA and Control Plans 15
shimonv Creating a new commercial product based on a modification to an existing product Other US Medical Device Regulations 4
R Creating WIs for a Heavy Civil Engineering Services company Document Control Systems, Procedures, Forms and Templates 19
M Creating a Plant Level Value Stream Map Process Maps, Process Mapping and Turtle Diagrams 1
T IMDS - Creating an MDS out of material and a made item? RoHS, REACH, ELV, IMDS and Restricted Substances 4
D How do I go about creating document logs and registers with the MS Excel Excel .xls Spreadsheet Templates and Tools 2
W ISO9001:2015 - Clause 7.5.2 - Requirements for Creating & Updating Documents ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 6
K Creating together Standard Definition for Prevention and Detection FMEA FMEA and Control Plans 1
Marc Creating an Internal Audit Program That Works for Your Organization Internal Auditing 0
M Creating change / Forcing change within a Company Lean in Manufacturing and Service Industries 7
A Creating a policy to evaluate the Third Party Security IEC 27001 - Information Security Management Systems (ISMS) 4
S Creating goals and objectives with targets and measurables for self-evaluations Management Review Meetings and related Processes 2
L Creating a xlsx Customer Complaint file to track Complaints Excel .xls Spreadsheet Templates and Tools 2
S When creating a 510k, which Guidance Document Wins? Other US Medical Device Regulations 9
P GS1 NHRN AIs - Creating Barcodes for Human Product Other ISO and International Standards and European Regulations 4
O Creating a Tool to Track & Verify Mistake Proofing Devices Document Control Systems, Procedures, Forms and Templates 5
B Any suggestion on creating the best paper plane that can hit a target perfectly? Coffee Break and Water Cooler Discussions 2
P Creating a PPAP document for my Suppliers APQP and PPAP 1
L Any recommendations on software for creating hierarchical workflows ? Misc. Quality Assurance and Business Systems Related Topics 2
AnaMariaVR2 Creating Global Giants from a Culture of Israeli Start-Ups Coffee Break and Water Cooler Discussions 0
R Creating a c-chart spreadsheet to use in my department Excel .xls Spreadsheet Templates and Tools 5
R Inspection Database Picker - Creating an Inspection Report Document Control Systems, Procedures, Forms and Templates 1
P Problem with creating VSM current map Lean in Manufacturing and Service Industries 3
G Creating an Organization Chart - AS9100 AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 5
L Creating DHF (Design History File) for Medical Device Systems Design and Development of Products and Processes 8
Q Recommendations for criteria on creating a CAPA (Corrective and Preventive Action) ISO 13485:2016 - Medical Device Quality Management Systems 8
C Resources for creating Process Validation Procedures Qualification and Validation (including 21 CFR Part 11) 5
B Creating an Audit Mechanism (System/Plan) General Auditing Discussions 3
A Customer Profiles - Creating a Customer 'Profile' for our Top 10 Customers Document Control Systems, Procedures, Forms and Templates 2
M Creating an Index for our Documents - Imaging Millions of Paper Records Document Control Systems, Procedures, Forms and Templates 13
N ISO 9001 based Audit Schedules: Creating and Maintaining - Template wanted General Auditing Discussions 5
P Creating a Traceability Database with Microsoft Access Document Control Systems, Procedures, Forms and Templates 7
V Creating a Cosmetic Inspection Specification for Powder Coated Painted Finished Parts Manufacturing and Related Processes 7
I Difficulty in creating a Quality Manual Quality Management System (QMS) Manuals 4
J Creating an Audit Schedule & Revamping QMS Internal Auditing 18
L How can you capture many positions held without creating a lengthy resume Career and Occupation Discussions 4
Miner Intro to MSA of Continuous Data - Part 10: Creating gauge families Imported Legacy Blogs 10
E Manufacturing Planning - Creating Improvements Misc. Quality Assurance and Business Systems Related Topics 7
R Creating Quality System and using it before it is complete ISO 13485:2016 - Medical Device Quality Management Systems 8
G Creating a "Records Policy" - Control of Quality Records Records and Data - Quality, Legal and Other Evidence 13
C Creating a Micro QMS - 4 geographical sites and 3 types of core activities ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 2
S Sources for Tips, Quotes with Graphics to be used for Creating Awareness? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 1
R The difference between Deviation and Failure - Creating an SOP to handle them Food Safety - ISO 22000, HACCP (21 CFR 120) 24
A Creating a Control Plan from scratch FMEA and Control Plans 3
M Problem creating a Macro in Minitab Using Minitab Software 4

Similar threads

Top Bottom