SQL code help (Case)

[alka]

Hello, I have been buggin for 5hours on this SQL coding... Here is what I'm trying to do. I just can't figure out how to code it. PLEASE HELP.




Please let me know if I need to clarify my request.

[June7]

Did you mean Tb_id?

Criteria that relies on data in other records of same table is difficult. Usually requires use of subquery or domain aggregate function (DLookup, DMax, DCount, etc)

Also, saving calculated data is usually a bad idea.

However, you can try a query that joins the table to itself, linking Li_depart to Li_arrive and Pi_id to Pi_id:
SELECT Table1.Pi_id, Table1.Li_depart, Table1_1.Li_arrive, Table1.Lb_qte_livre, Table1_1.Lb_qte_recu
FROM Table1 AS Table1_1 INNER JOIN Table1 ON (Table1_1.Li_arrive = Table1.Li_depart) AND (Table1_1.Pi_id = Table1.Pi_id);

Unfortunately, the resulting query will not be updatable. If the table has a unique ID field (autonumber will serve), it could be used to identify the records for editing.

SELECT Table1_1.ID, Table1.Pi_id, Table1.Li_depart, Table1_1.Li_arrive, Table1.Lb_qte_livre, Table1_1.Lb_qte_recu
FROM Table1 AS Table1_1 INNER JOIN Table1 ON (Table1_1.Li_arrive = Table1.Li_depart) AND (Table1_1.Pi_id = Table1.Pi_id);

This query could be opened as a recordset in VBA then cycle through the recordset and update the records in table as appropriate.

[orange]

Can you tell us in plain English WHAT you are trying to do? Your diagram has not clarified anything for me.

[alka]

Sorry for late respond, Yes I meant Tb_id june 7.

What I'm trying to do is.
Basicly whenever Li_arrive where Tb_id =1 EQUALS li_depart where Tb_id = 2 (Here ENT2 = ENT2) AND whenever Pi_id from Tb_id =1 EQUALS Pi_id from a Tb_id = 2 :When it's the case, I substract lb_qte_livre where tb_id = 2 TO lb_qte_recu where tb_id =1

Hope I made myself understandable...




It's in french but tb_id =1 is a receiving order.
tb_id =2 is a shipping order.
Pi_id is a tool.
Li_depart is the initial place of the shipping and Li_arrive is the receiving place.
Lb_qte_recu is the quantity received and lb_qte_livre is the quantity shipped.

Thanks for the help, trying June's code atm.

[alka]

I'm not sure June's code will do what I'm trying to do...

[June7]

All my suggested query was intended to do was get the related data manipulated into a structure where the values could be viewed as a single record.

I tested with the sample data provided and it worked. If the data is actually more complex than shown, then need more info.

As stated, if that query works then it can be used in code to run a procedure that updates the data in table.

I cannot stress enough that saving calculated data is usually a bad idea.

I really don't understand this particular calculation requirement.

[alka]

Let me explain the full problem with all the real information, I tried to simplify it to get a quicker solution but I guess it wasn't helpful ahah. I did evrything in french but I'll do my best to translate.

My main table is "Bon". It is basicly an order.

B_id is the ID of the order.
Tb_id is "Type bon" wich is the type of the order (where 1 = receiving order, 2 = shipping order, 3 = Query of tools and 4 = buying order).
For now just keep in mind 1 and 2 (we will be dealing with receiving and shipping order).
Fo_id is the ID of a tool supplier
Li_depart is the start point of a shipping order (it's a place, a plant, a factory, warehouse, ... call it how you want) --> It will be empty
Li_arrive is the finish point of a shipping or receiving order (it's a place, a plant, a factory, warehouse like Li_depart)


My second table is "Ligne bon" wich is where I add tools to my "bon" (order)

I Chose a B_id then Add tools to it.

For exemple I received tools in my factory: I generate a new "Bon", choose Tb_id = 1 (receiving) and where I'm going to put the tools I received.
In Ligne bon,
Pi_id is the id of tools in my database
lb_qte_demande is the quantity asked (it will be empty unless its a buying order: tb_id = 4 or a query of tools: tb_id =3)
lb_qte_livre is the quantity shipped
lb_qte_recu is the quantity received.

STEPS HOW I WANT MY DATABASE TO BE USED:

I first create a B_ID (for exemple b_id generates 21) with a TB_ID=4 (Buying order): I buy tools; adds the tools Pi_id 9 6 and 2. Also I chose a Li_arrive (destination factory) = ENT2. I enter the quantities in Lb_qte_demandé (asked)
When the package arrives at factory ENT2, the employee take the package and look at the b_id (here it's 21). He goes to the database and change his TB_id to 1: receiving order (with the right quantity) from b_id=21. I enter the quantities in lb_qte_recu (received)

Then, if ENT2 wants to SHIP those tools to APP1 (another factory), he creates a new shipping order including the tools he want. In my example, he creates B_id = 25; tb_id = 2 (shipping).
Then choose Li_arrive (where he wants the tools to be shipped) and Li_depart is ENT2 of course (where the tools come from; from his factory).

This is where I have the formula needed:

[June7]

Customary approach in relational database is to enter transaction records for incoming and outgoing (purchases and sales) then calculate the difference to determine balance on hand. In your case it looks like you are documenting movement of assets between entities of your organization. I am not sure how this should be modeled. Do you ever receive tools from the other locations? Are you the central tool purchasing entity for the entire organization? Tools must be accounted for by quantity, not individually, across all locations?

[alka]

The only tools I receive is those I bough, so the tools I entered in a B_id (in that case the tb_id would be 4). Then when I receive them I change the tb_id to 1. And for defining quantity, I use three field per tool: lb_qte_demande (qty asked) lb_qte_recu (qty received) lb_qte_livre (qty shipped).

[June7]

Why would tb_id value change from 4 to 1?

Didn't really answer last two questions of my previous post.

[alka]

I would change my tb_id from 4 to 1 because once I receive what I ordered, I want to make it as a Receiving order.


I'm not sure I understood your last two questions then :S

After 3 days I was able to find a solution. In my form, so in vba code I do the calculus!

Private Sub Command96_Click()
DoCmd.RunCommand acCmdSaveRecord

Dim recQ As Recordset 'Query Inventaire
Dim recT As Recordset 'Table Ligne bon

Set recQ = CurrentDb.OpenRecordset("Inventaire")
Set recT = CurrentDb.OpenRecordset("Ligne bon")

recQ.MoveFirst

Do While Not recQ.EOF
'Si la pièce rentré existe déjà dans la table inventaire à un autre lieu et la livre ailleur, on enlève la qté livré à recu
If recQ!Pi_id = Combo30.Value And recQ!Li_arrive = Me.Parent!Combo21.Value And recQ!Fo_id = Me.Parent!Combo15.Value Then

recQ.Edit 'Ici j'edit la table Ligne bon
recQ.Fields("lb_qte_recu") = recQ.Fields("lb_qte_Recu") - Lb_qte_livre.Value
MsgBox ("La quantité de " & recQ.Fields("pi_nom") & " au bon " & recQ.Fields("B_id") & " au lieu " & recQ.Fields("li_arrive") & " est maintenant de " & recQ.Fields("lb_qte_recu"))
'MsgBox ("La quantité de " & RecQ.Fields("pi_nom") & " au bon " & RecQ.Fields("B_id") & " au lieu " & RecQ.Fields("li_arrive") & " est maintenant de " & RecT.Fields("lb_qte_RecQu") & RecQ.Fields("uni_nom"))
recQ.Update

End If
recQ.MoveNext

Loop