How do I create a relational database between two tables using MS Query based on two criteria?

Posted on 2014-12-17
Last Modified: 2014-12-27

I am trying to learn about MS Query, and how to write SQL statements and return the results to excel.

I would like to link the two tables within excel (see attached) together based on two criteria, to produce one table that has the following headings:
SO Number, Item Number, SO Quantity, Invoice Quantity, Invoice Number.

As each Sales Order (SO) can have multiple Items, I need to create a relationship between the SO number and the Item numbers on both tables. I require the results to display ALL the SO numbers (as there cannot be an invoice without a Sales Order), with a zero value should there be no invoice.

I understand how to open MS Query from excel, it is the SQL Statement I am struggling with.

Also, as a very new user to MS Query, are there any resources online that explains MS Query at a basic level?

Many Thanks
Question by:martinibbo
  • 5
  • 4
LVL 77

Assisted Solution

arnold earned 500 total points
ID: 40506330
When you try to load the excel as the external source, do you get an error that the table data is not located or it is not in the correct format??
You first need to save your file in XLS format.
You then using a new workbook use the data import external database query, and use excel as the source which is where you will select the .xls version of this file.
At that point, the table should be listed, you may have to use options to include system tables.
You will then be prompted with the column selections that you want to appear on the report.

Then you will have the table, in MS query, there is the JOIN option which is where you will pick the criteria column from table INV and a corresponding column from table SO.

When you have tables in the display, you can map relationships by selecting a column from one table and dragging it to the matching/corresponding column in the second table establishing that between these two tables these two columns have a relationship.
The two tables you have  what is the relationship between them since they both include almost the same set of data.

usually you have one source of data in one table, the other table will have another set of data with each having a single columns that binds them together.

i.e. you have an invoice table with invoice numbers, cost of the invoice, number of items in the invoice. Then you have an items table, that will have item Id, invoice number (this is the column that will be the basis to join the items to the invoice),description of the item, quantity of the item, price of the item, etc.

Note ID columns in each table must be unique.
start small:
create two tables with max 8 rows in each.

using the criteria tool is where you can narrow down your selection.

not sure how a sales order have a sales order quantity?
LVL 77

Assisted Solution

arnold earned 500 total points
ID: 40506346
Here is the excel example with three tables

Included is also the query file.

What thie external example does is combines the data in the three tables, orderid, who ordered, what items were ordered, in what quantity, what is the per item cost and what is the cost of all items in the order.

hope this helps you along.

If you have questions, .....
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40506359
It seems you need  4 tables:
TableSales: To enter SO Number and Sales Date (if necessary)
TableProductSole: Here again we need to show what SO Number this is related and What product along with its Quantity
TableInvoive: When product is being shipped, enter Invoice Number and Invoice Date (if necessary)
TableShipped: Weaves SQ Quantity with Invoice Quantity.

Sorry if it seems I have totally missed your question, but a database is foundation of forms and reports and needs to be designed to produce consistent and accurate reports.  

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

LVL 77

Assisted Solution

arnold earned 500 total points
ID: 40506361
If you modify the posted example query to
SELECT `order$`.orderid, `order$`.`who bought`, Sum(`invoice$`.`item quantity`*`item$`.cost) AS 'Sum of itemid'
FROM `invoice$` `invoice$`, `item$` `item$`, `order$` `order$`
WHERE `order$`.orderid = `invoice$`.orderid AND `invoice$`.itemid = `item$`.itemid
GROUP BY `order$`.orderid, `order$`.`who bought`

Open in new window

You will get the report of oderid, who purchased it and the total cost.
LVL 77

Assisted Solution

arnold earned 500 total points
ID: 40506380
Here is the query data file that you would need needs to be saved with a .dqy suffix
DSN=Excel Files;DBQ=[REPLACE THIS WITH WHERE YOU SAVED]\example_for_MS_query.xls;DefaultDir=[REPLACE THIS WITH WHERE YOU SAVED];DriverId=790;MaxBufferSize=2048;PageTimeout=5;
SELECT `order$`.orderid, `order$`.`who bought`, Sum(`invoice$`.`item quantity`*`item$`.cost) AS 'Total Cost'  FROM `invoice$` `invoice$`, `item$` `item$`, `order$` `order$`  WHERE `order$`.orderid = `invoice$`.orderid AND `invoice$`.itemid = `item$`.itemid  GROUP BY `order$`.orderid, `order$`.`who bought`

orderid	who bought	Total Cost

Open in new window

The query can be reused within a worksheet as many times as need by altering the SQL to reflect what you want.

Assisted Solution

martinibbo earned 0 total points
ID: 40506592
Thank you for your quick responses, I think I have a better grasp of what I need to do now.
Arnold your example appeared to link the tables based on one criteria for each table..... What I think I need is two outer joins.

When I tried to achieve this in MS Query "joins wizard" I was confronted with an error that said only one outer join was possible.

As a workaround I opened the SQL editor and added an "and" to the Join. code used below:
SELECT `'New INV 2$'`.`SO Number`, `'New INV 2$'`.`Item Number`, `'New SO 2$'`.`SO Quantity`, `'New INV 2$'`.`Invoice Quantity`, `'New INV 2$'`.`Invoice Number`, `'New SO 2$'`.`Item Number`, `'New SO 2$'`.`SO Number`
FROM {oj `C:\Users\x\Desktop\SourceData.xlsx`.`'New SO 2$'` `'New SO 2$'` LEFT OUTER JOIN `C:\Users\x\Desktop\SourceData.xlsx`.`'New INV 2$'` `'New INV 2$'` ON `'New SO 2$'`.`SO Number` = `'New INV 2$'`.`SO Number` and `'New SO 2$'`.`Item Number` = `'New INV 2$'`.`Item Number`}

Open in new window

The results appear to be what I am after, although I will need to tidy up the additional "Item Number 2" and "SO Number 3" so that all the data is in the same columns.

What I would like to know is if adding in the second outer join will cause any issues, as I kind of bypassed the warnings from MS Query?

Many Thanks

Author Comment

ID: 40506670
To perhaps answer some of your questions:
1) I am using .xslx which is picked up fine by MS Query, so I don't think that there is a need to change the file type to .xls.
2) the relationship between the two is SO number = SO Number AND Item Number = Item Number.
3) A Sales Order Quantity exists (perhaps incorrectly named) This is the quantity ordered for an item on the sales order.

The aim is to create a report from existing databases which shows whether a sales order has been invoiced, broken down into individual item lines on the sales order.

As a sales order can have up to 20 items, we need to know which Sales Order items have been invoiced, and which have not. There are two quantities because the Sales Order Quantity does not always equal the invoiced quantity.

I originally tackled this by using VBA, however it was very slow, and MS Query appears to be very fast in comparison.

I hope this explains it better.

Many Thanks

Author Comment

ID: 40508814
My remaining questions before I can mark this as solved are:

1) will adding a second outer join cause any data issues? (see above)
2) if the external data is stored in excel, can this be queried from multiple MS Query sessions at the same time? i.e Is multi-user possible, and does this cause any issues?
3) Can you query tables from stored in different excel workbooks in one query?
4) Are there any data limits, as my data may get rather large? e.g it can only query x number of rows/columns?

hopefully they are fairly simple to answer :)

Many Thanks
LVL 77

Accepted Solution

arnold earned 500 total points
ID: 40510439
If you need to have multiple users query, you shoul look at getting sql revert express version that you can then query from your excel file.
Within the same excel file, you can have multiple querysntp from the same set of data.

File based is fine for soothing, the more people and complexity, use an sql express might be better s it provide a much more flexible set of options.

Keeping track of files, tables, could become unmanageable rather quickly.  Have not tried pulling in multiple excel files to make the tables within appear as though part of the same main dataset, but should be doable as the source of tables did not appear to be limited to a single workbook with multiple pages.

Author Closing Comment

ID: 40519492
My response directly addresses the issue in question relating to MS Query, however this was only arrived at due to the assistance of the other responses.

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question