If "X" field of dif entries are the same, join em

[capsula4]

I have tblOPERATIONS, tblCONTAINERS, tblPRODUCTS

all of em, one-many relationship, 1 op-many containers, 1 container-many products

Sometimes, in a same operation you have dif containers but both containers may have the same products.

I have tblProducts with fields: CONTAINER_ID ,PRODUCT_NAME, QUANTIY, PRICE

So for my invoice I would be make a query that sorts out products by operation in ascendent order

OP_ID, PRODUCT_NAME, QUANTITY, PRICE

Then I would do use "while EOF" and check PRODUCT_NAME of current entry with PRODUCT_NAME of comming entry (how can i make this?) and if they are the same, delete one entry and sum quantity of that product (how to make this?).

So my questions would be, how to compare one field of an entry with the same field of the coming entry and how to delete an entry.

This merging of info should only be done per OPERATION! How would I make this criteria?

 

[ajetrumpet]

check PRODUCT_NAME of current entry with PRODUCT_NAME of comming entry (how can i make this?) and if they are the same, delete one entry and sum quantity of that product (how to make this?).

I think the question I want to ask you here is:

1) What is a comming entry?

I assume "comming entry" refers to the next record in sequence as you are looping through a recordset. Yes?

If I'm right here, I understand this portion of your post. If you want to evaluate sequential field values as you are looping through records in a set, store the value (of the current record) in a variable, then write a IF, THEN statement in the loop when you get to the next record in succession. That is the simplist way to do it.

 

[capsula4]

Okay thank you Adam I got that one, I actually didn't realise how I could make it, really newbie at VBA.

And is there a way to delete a whole record? not erase the data but instead delete the entry using VBA.

 

[ajetrumpet]

And is there a way to delete a whole record? not erase the data but instead delete the entry using VBA.

You can use the DELETE action when you have a recordset open in VBA. This will delete the entire record from the table and automatically move your position to the next record. Is this what you want to know?

 

[capsula4]

You can use the DELETE action when you have a recordset open in VBA. This will delete the entire record from the table and automatically move your position to the next record. Is this what you want to know?

Actually yeah but since I'm pretty newbie I'm not really sure how it works, or is there a sample somewhere which I may see?

 

[ajetrumpet]

Actually yeah but since I'm pretty newbie I'm not really sure how it works, or is there a sample somewhere which I may see?

Yeah, like this...