Generating a Query based on two factors

[LeadTechIG]

All,



I'm looking to generate a list of what I call [Job Orders] - a 7 digit number assigned to a specific group of assets based on when they arrive. However, I only want the results to be the [Job Orders] whose assets are all recorded as "Shipped" under the [Asset Status] field.

Any thoughts?

Also - am I using the brackets and quotes correctly? Brackets are for fields and quotes are for the drop-down selection in that field.

[June7]

Don't understand the issue. Appears to be a simple SELECT query with filter criteria.

[LeadTechIG]

You may very well be correct - I just do not understand what it is that I am supposed to do. The struggle that I am having is that one particular Job Order Number will be applied to as many as 2,000 assets. Each of those 2,000 assets has a status. I'm looking to find a way to generate a list of job numbers where all the assets have "Shipped" as their status; there are six statuses that in play.

[June7]

Okay, if I understand:

You have JobOrders. JobOrders have associated assets. Each asset has its own status. You want a listing of JobOrders where for each all their assets are 'shipped'? That does get a little tricky. Something like:

SELECT * FROM OrdersTable WHERE NOT OrderID IN (SELECT OrderID FROM OrderAssets WHERE [Asset Status]<>"Shipped");

[LeadTechIG]

Thank you for your quick response.

Here is my SQL text.

SELECT * FROM [Database] WHERE NOT [Job Order] IN (SELECT [Job Order] FROM [DATABASE] WHERE [Asset Status]<>"Shipped");


Have I interpreted your response correctly? When I RUN this, Access prompts me by asking what Job Order I want.

I'm sorry that I am not up to speed with the rest of the community - I only know a small amount.

[ssanfu]

generate a list of what I call [Job Orders]

According to your first post, [Job Orders] has an "s".

SQL modified:
SELECT * FROM [Database] WHERE NOT [Job Orders] IN (SELECT [Job Orders] FROM [DATABASE] WHERE [Asset Status]<>"Shipped");



Edit:
BTW, "Database" is a reserved word in Access and shouldn't be used for object names.
Should only use letters, numbers and the underscore in object names. NO spaces, punctuation or special characters (except the aforementioned underscore)

[June7]

Why is the same table name referenced in both SELECT statements? Aren't two tables involved?

Table named Database is not very informative name.

[LeadTechIG]

Very good eye! What a detail to spot. But, unfortunately the error in typing was in the first post - the field title is singular.

I agree with your followup - but, I am not the original architect of our database.

[LeadTechIG]

We only utilize one table - and its called simply our "Database". I've been informed on multiple occasions that we are using Access in a manner deemed incorrect.

[ssanfu]

Can you post your dB with 10 - 20 records? Change any sensitive data....

[June7]

Okay, should still work, it does in my test in my db.

If you want to provide db for analysis, follow instructions at bottom of my post.