How to bypass a circular reference...

[elloco]

Good morning all,

I have a table called Releases. There, I store Orders for the shop to produce.
I have another table called Deliveries. Here, I store Orders delivered.
With a Find Unmatched Query I can find the Orders not delivered yet.

Is there any way to add those orders not delivered to a lookup in Deliveries table?

It is kind of a circular reference since I am trying to lookup for a result in a table that is been used to get that result. Of course it is not working and, of course too, I need your help to not :banghead:

Thanks in advance,

Ed

 

[plog]

Provide some context. Give us a big picture view of your database--pretend its career fair at an elementary school and you are explaining to them what it is you do. Then specifically tells us about what you are trying to do:

What are you hoping this accomplishes?
Is this a one time thing or something that will be ongoing with each new Order?
Why must you have essentially false data/blank deliveries?

 

[MajP]

If I understand correctly you can do a "NOT IN" query. Something liked this.

Select OrderID from Releases where OrderID Not in (Select OrderID from Deliveries)

https://www.w3schools.com/sql/sql_in.asp

 

[Minty]

Without seeing your table structure I'm thinking that you may be moving things around between table here unnecessarily.

If an Order line has a ordered qty and a supplied quantity you can always simply calculate the outstanding orders?

You could make it more robust by Have a table of deliveries that referenced the order ID if you ever partially ship orders, and again simply calculate which order lines haven't been completely fulfilled.

 

[jdraw]

I agree that readers need more info about your requirement in context. I suggest you respond to plog's post #2.

There are several generic, free data models at Barry Williams' site.

Here is one dealing with Customers, Orders and Deliveries that may provide some help.

 

[The_Doc_Man]

Possibly if the orders have the exact same nomenclature and ID, a JOIN between the two tables would tell you something for which you have an order that isn't yet released that corresponds to an order not yet delivered. However, without knowing more about this question, I cannot be more specific.

 

[Pat Hartman]

I'm not sure why there are two tables. Aren't released and received just status' of an order? Can you explain why you created two tables instead of one?

 

[June7]

Do you fulfill orders all at once or are partial deliveries allowed?