Solved

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

Posted on 2014-12-17
10
183 Views
Last Modified: 2014-12-27
Hi,

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
SourceData.xlsx
0
Comment
Question by:martinibbo
  • 5
  • 4
10 Comments
 
LVL 76

Assisted Solution

by:arnold
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?
0
 
LVL 76

Assisted Solution

by:arnold
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, .....
external-query.xls
example-for-MS-query.xls
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40506359
RefrentialIntegrity
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.  

Mike
0
 
LVL 76

Assisted Solution

by:arnold
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.
0
 
LVL 76

Assisted Solution

by:arnold
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
XLODBC
1
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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Assisted Solution

by:martinibbo
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
0
 

Author Comment

by:martinibbo
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
0
 

Author Comment

by:martinibbo
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
0
 
LVL 76

Accepted Solution

by:
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.
0
 

Author Closing Comment

by:martinibbo
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.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now