asked on

I'm looking for help with a SQL query to pull orders from our database with a particular status.

Hello!

Let me preface this by stating that I'm new to SQL queries, and am learning on the fly. This type of question has been asked and answered multiple times, but I think my lack of expertise is keeping my from applying it properly to my environment.

I have a system with sales data kept in two separate SQL tables; salestable contains the status of the sales orders, while salesline has the detailed information from each order. 'SalesID' is common to both tables.

I need a query that will give the line item detail for every Sales Order with a particular status. I don't need to filter the results in the query (I can return all the columns), as we can do that afterward in Excel if need be.

For example:

dbo.SalesTable contains the SalesID, CustomerName, PurchaseOrderRef, and most importantly, SalesStatus (gives the status of the order as picked, packed for shipping, shipped, etc.).

dbo.SalesLines contains the SalesID, CustomerName (called CustAccount), ItemID, ShippingDateRequested, and ShippingDateConfirmed.

My query needs to return all information above for each order (identified by SalesID) that has a SalesStatus of 1 or 3.

I assume that this requires a join of some sort, but I'm struggling a bit with the syntax. Any help would be appreciated!

thanks!

Scott

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PatHartman

The QBE is a huge help when you are just learning. It is even a big help if you know how to write SQL. To create your query:
1. Open the QBE
2. Select the two tables.
3. If you have defined a relationship (and you should always do this), Access will automatically draw the join line as it is in the relationship window. If Access doesn't draw the join line, you need to do it. Click on the join field in one table and drag to the other table. If the names are the same, that is all you have to do. If the names are different, you may have to adjust the column name in the dialog box.
4. Select ONLY the columns you need from each table.
5. In the Criteria line for SalesStatus enter -
1 or 3
5a. I prefer the In() syntax shown by John so you could enter -
IN (1,3)
6. Name the query appropriately and save it.

Scott Milner

ASKER

@Vitor, thank you very much. Your query worked perfectly.

@PatHartman, thank you for your input. I'm not familiar with the QBE, so I'm going to do some research this weekend to better understand your response. I'll probably post back a follow-up question or two on Monday.

thanks again to you both, and have a great weekend!
'

Vitor Montalvão

smilner, do you need more help on this question?

Scott Milner

ASKER

Good morning, Vitor.

Thanks for the follow-up. I was busy this weekend, and didn't get back to the question like I'd hoped.

The only follow-up question I'd have for you is to ask if you could explain the syntax of your statement. Specifically, what does the capital T and L do in the statement?

Thank you... the query is working perfectly.

Scott

Vitor Montalvão

The select is one of the more basics that you can have in SQL. It returns all records (SELECT *) that sales has details (SalesID = SalesID) and where the status matches 1 and 3 (SalesStatus IN (1,3)).
The 'T' and 'L' are aliases respectively for SalesTable and SalesLines, so you won't need to write the whole table names again, otherwise the select command would be:

SELECT *
FROM SalesTable
    INNER JOIN SalesLines ON SalesTable.SalesID = SalesLines.SalesID
WHERE SalesTable.SalesStatus IN (1,3)

Open in new window

Scott Milner

ASKER

I see... so the 'SalesTable T' portion of the FROM statement just sets T as an alias?

Thanks!

Scott Milner

ASKER

The query worked perfectly... thank you!