What Kind of query is needed for this?

[Rednels]

Hello all,
I need help with making a query this will check the OrdQty for all items in a specific order against the Sup1inv field, then if supplier 1 can not fill the order have it check against Sup2inv field.
Here is an example of the data im working with

Order# Item# OrdQty Sup1Inv Sup2inv
555 widget1 2 1 3
555 widget2 1 1 4
556 widget3 1 3 0
557 widget1 7 1 3

The part i am having trouble with is making sure the query "understands" that both items on order 555 are the same order so the end result would tell me that order 555 needs to be placed at supplier 2, not 1.

Thanks in advance for the help,
Eddie

 

[ajetrumpet]

making a query that will check the OrdQty for all items in a specific order against the Sup1inv field, then if supplier 1 can not fill the order have it check against Sup2inv field

This is vague, Access doesn't know the command "IF cannot fill". Be more specific...you mean if any value for Sup1 is 0?? Value less than a certain amount?? Sum of values for Sup1 less than sum of values for Sup2?? You still have a few questions to ask before you write I think...

 

[Rednels]

oK, sorry for the vagueness.
What i mean is I want it to return records where [Sup1inv]>=[Ordqty]

I know how to do this query, the problem I am having is the query does not know that both lines for order # 555 are the same order with 2 different items. When i put the critera in for above it will still show the 1 item for order 555 that is in stock at supplier 1 not taking into account the fact that half the order is missing.

How can I make it understand that the mutiple lines for order 555 are in fact the same order and have to be "checked as one order" not as 2 seperate items.

Thanks,
Sorry if i am not making sense

 

[ajetrumpet]

oK, sorry for the vagueness.
What i mean is I want it to return records where [Sup1inv]>=[Ordqty]

I know how to do this query, the problem I am having is the query does not know that both lines for order # 555 are the same order with 2 different items.

It's not going to know this with just your above criteria. The program reads "records", not groups, unless you specify otherwise.

When i put the critera in for above it will still show the 1 item for order 555 that is in stock at supplier 1 not taking into account the fact that half the order is missing.

"Half the order is missing"?? What do you mean?? Supplier does not have enough?? Other records with same order number not being returned??

How can I make it understand that the mutiple lines for order 555 are in fact the same order and have to be "checked as one order" not as 2 seperate items.

First thing is to include only records that include the order number in question. Add that field to your query criteria. So, like....

WHERE [Sup1inv]>=[Ordqty] AND [OrderID]=[555]

 

[Rednels]

Thanks for the reply.
So, if I understand correctly, I need to have the query look at each order # one at a time, then move on to the next order #.
What would be the command to make the query check order 555, then move to order 556?

Thanks again for the help, I really appreciate it.

Eddie

 

[Rednels]

Ok, I just ran the query as in your post:
WHERE [Sup1inv]>=[Ordqty] AND [OrderID]=[555]
and unfortunatly its doing the same thing.

see, on order 555 widget1 can not be ordered from suplier1 becasue there are not enough, but widget2 on order 555 can becasue the supplier has enough.
When I run the query it returns a result of order 555 widget2 being in stock at supplier1, however, the second line of order 555 is missing in the results becasue it did not meet the criteria. But what I need the query to return is that order 555 can not be filled by supplier 1 becasue ALL items are not in stock at that vendor.

Order# -Item# -OrdQty -Sup1Inv -Sup2inv
555 ---widget1 --2 -------1 --------3
555 ---widget2 --1 -------1 --------4
556 ---widget3 --1 -------3 --------0
557 ---widget1 --7 -------1 --------3

Also, I think this need to go towards being a true/false kind of situation where either all items on an order are in stock, or they are not.

I know im having a hard time getting this across and I appologize, Im trying to be as specific as possible with the limited knowledge I have.

Thanks,
Eddie

 

[ajetrumpet]

Ok, I just ran the query as in your post:
WHERE [Sup1inv]>=[Ordqty] AND [OrderID]=[555]
and unfortunatly its doing the same thing.
When I run the query it returns a result of order 555 widget2 being in stock at supplier1, however, the second line of order 555 is missing in the results becasue it did not meet the criteria.

That is right, it did what it was told, but that's not what you want.

But what I need the query to return is that order 555 can not be filled by supplier 1 becasue ALL items are not in stock at that vendor.

I know what you want...and that will take a few more steps than just one query I think. Without looking at your file, I cannot say for sure. I can say though that I think you're trying to write the query as to answer a question of "yes"/"no" (like you said, "true, false"). Write the query first, then let Access answer the "yes/no" question by referencing that query in the next step that you take to get to your final answer.