Multi-step Query

[aladrach]

I am trying to put together an advanced query, but can't figure out how. I need to pull data from 3 tables.

Table A has Order Number and Part Number as entered by user.
Table B has order information (eg. Company Name)
Table C has the part information for each order line item

I need to build a query to take the input from A and display the Order info and Part info based on that input.

Any ideas?

 

[plog]

Sounds like you have it thought out correctly, what's your specific problem? What steps are you taking and what's not occuring that you expect to occur? Be sure to give field names and which fields relate to the other tables in your response. Sample data would help too.

 

[aladrach]

OK. I've managed to tie the tables successfully and get the data I need by linking the three tables. Now, when I try to run my criteria to filter out closed orders Access "stops working" and closes. Here is my criteria:

In
(SELECT DISTINCT [Order Number] FROM [Time Table] AS Q1 WHERE exists
 (SELECT DISTINCT [Part Number] FROM [Time Table] AS Q2 WHERE [Part Number] NOT IN 
(SELECT DISTINCT [Part number] 
FROM [Time Table] AS Q3
WHERE [Location]='Shipped' AND Q2.[Order Number]=Q3.[Order Number])  AND Q2.[Order Number]= Q1.[Order Number] ))

This happens on any computer I try it on.

 

[Guus2005]

You need to join the three tables together using the key fields.
The result would look something like this

select * from Order inner join OrderDetail on Order.OrderID = OrderDetail.OrderID inner join Product on OrderDetail.ProductID = Product.ProductID

Using "Where In ...", "Where Exists ...", the "distinct" keyword and the fact that you connect the three tables in a where clause instead of using a join slows the query down considerably.

Enjoy!

 

[aladrach]

You need to join the three tables together using the key fields.
The result would look something like this

select * from Order inner join OrderDetail on Order.OrderID = OrderDetail.OrderID inner join Product on OrderDetail.ProductID = Product.ProductID

Using "Where In ...", "Where Exists ...", the "distinct" keyword and the fact that you connect the three tables in a where clause instead of using a join slows the query down considerably.

Enjoy!

So, if this is my complete query:

SELECT [Time Table].[Order Number], *
FROM ([Time Table] INNER JOIN dbo_salesordera ON [Time Table].[Order Number] = dbo_salesordera.RefNumber) INNER JOIN dbo_salesorderlinedetail ON dbo_salesordera.TxnID = dbo_salesorderlinedetail.IDKEY
WHERE ((([Time Table].[Order Number]) In (SELECT DISTINCT [Order Number] FROM [Time Table] AS Q1 WHERE exists
 (SELECT DISTINCT [Part Number] FROM [Time Table] AS Q2 WHERE [Part Number] NOT IN 
(SELECT DISTINCT [Part number] 
FROM [Time Table] AS Q3
WHERE [Location]='Shipped' AND Q2.[Order Number]=Q3.[Order Number])  AND Q2.[Order Number]= Q1.[Order Number] ))) AND ((dbo_salesorderlinedetail.ItemRef_FullName)=[Part Number]));

How would you advise fixing it?

 

[Guus2005]

Can you post a sample database?

 

[aladrach]

Here you are. Typically runs off a SQL server, but made a localized copy for you. Also converted to MDB for your convenience

 

[aladrach]

OK. Just tried the localized version and it works, but the SQL one does not...

 

[aladrach]

We have a query that we use to hide completed orders, when all parts in an order are finished, and have a shipped line item. The code is as follows and is added to the criteria for the Order Number column:

In (SELECT DISTINCT [Order Number] FROM [Time Table] AS Q1 WHERE exists
 (SELECT DISTINCT [Part Number] FROM [Time Table] AS Q2 WHERE [Part Number] NOT IN 
(SELECT DISTINCT [Part number] 
FROM [Time Table] AS Q3
WHERE [Location]='Shipped' AND Q2.[Order Number]=Q3.[Order Number])  AND Q2.[Order Number]= Q1.[Order Number] ))

If this is used in the localized version, it works fine, but in the SQL verion, it crashes Access...

 

[aladrach]

Bump----^----

 

[Guus2005]

Bumping the thread doesn't work.
I check this site once a week and follow up on the threads i react to.
More or less...

 

[Guus2005]

I can't figure out what you want in the where clause you have written.
What is the query designed to return?

Can you write it down?

 

[aladrach]

What the query is designed to do, is first group the results by order number, then part number. It takes that grouping, and looks for the "SHIPPED" location in a line item for one of the entries. Once a "SHIPPED" line item has been added for each part on an order, the order disappears.

 

[Guus2005]

So every item can be shipped separately?
When every item is shipped the line must dissapear?

So if the number of Shipped items in an order doesn't match the number of items in that order the line must be visible.

The name "Time Table" is a strange name for a table. Is it used to store only items that are shipped?

When you have answered these questions, it will be a lot easier for me to solve this puzzle.

HTH