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


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
Who is Participating?
arnoldConnect With a Mentor Commented:
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.
arnoldConnect With a Mentor Commented:
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?
arnoldConnect With a Mentor Commented:
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, .....
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Mike EghtebasDatabase and Application DeveloperCommented:
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.  

arnoldConnect With a Mentor Commented:
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.
arnoldConnect With a Mentor Commented:
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.
martinibboConnect With a Mentor Author Commented:
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
martinibboAuthor Commented:
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
martinibboAuthor Commented:
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
martinibboAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.