If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with a query...
Hello all,
I have table Customer with the field CP I have the table Cpostal with the field CP_NUM The relation between this two tables is Customer.CP 1-N Cpostal.CP_NUM This means that the for one CP code in the table Customer i have N CP_NUM in the table Cpostal. My goal is update the field CP with the first ocorrence of CP_NUM. How can i do this?... Thank's in advance, Nuno Gomes |
#2
|
|||
|
|||
Help with a query...
First, how is Customer related to CPostal table? That is what field(s) in the
two tables are used to establish the relationship. Second, how do you define first occurence - the earliest, latest, biggest, orsmallest value in CP_Num? Or is it based on some other field in the record. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Nuno Gomes wrote: Hello all, I have table Customer with the field CP I have the table Cpostal with the field CP_NUM The relation between this two tables is Customer.CP 1-N Cpostal.CP_NUM This means that the for one CP code in the table Customer i have N CP_NUM in the table Cpostal. My goal is update the field CP with the first ocorrence of CP_NUM. How can i do this?... Thank's in advance, Nuno Gomes |
#3
|
|||
|
|||
Help with a query...
Thank you John for your care...
The table Customer have the fields: NUM, NAME, ..., CP, ... The table CPOSTAL have the fields: CP_NUM, CP_TEXT .... and could have records like: 1000; AAAA 1000; AAAA 1000; BBBB 1100; AAAA 1200; CCCC The field ralated are Customer.CP with CPostal.CP_Num For me, the first occurence is the first occurence of CP_NUM ordering the table CPOSTAL by CP_TEXT asc. Thank you for any help. Nuno Gomes "John Spencer" escreveu na mensagem ... First, how is Customer related to CPostal table? That is what field(s) in the two tables are used to establish the relationship. Second, how do you define first occurence - the earliest, latest, biggest, orsmallest value in CP_Num? Or is it based on some other field in the record. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Nuno Gomes wrote: Hello all, I have table Customer with the field CP I have the table Cpostal with the field CP_NUM The relation between this two tables is Customer.CP 1-N Cpostal.CP_NUM This means that the for one CP code in the table Customer i have N CP_NUM in the table Cpostal. My goal is update the field CP with the first ocorrence of CP_NUM. How can i do this?... Thank's in advance, Nuno Gomes |
#4
|
|||
|
|||
Help with a query...
I'm sorry but I still don't understand what you are trying to do. Your
explanation as I understand it makes little sense. You say you want to update Customer.CP with a value from CPostal.CP_Num based on the order of CPostal.CP_Text. Since the records are related by Customer.CP to CPostal.CP_Num, Customer.CP should always be equal to CPostal.CP_Num and there would never be a change occuring. Are you relating the records on some other field value or are you trying to update some other value? Perhaps you can post a few records in Customer table and show how they are related to CPostal and then show which records in Customer would change and how they would change. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Nuno Gomes wrote: Thank you John for your care... The table Customer have the fields: NUM, NAME, ..., CP, ... The table CPOSTAL have the fields: CP_NUM, CP_TEXT ... and could have records like: 1000; AAAA 1000; AAAA 1000; BBBB 1100; AAAA 1200; CCCC The field ralated are Customer.CP with CPostal.CP_Num For me, the first occurence is the first occurence of CP_NUM ordering the table CPOSTAL by CP_TEXT asc. Thank you for any help. Nuno Gomes "John Spencer" escreveu na mensagem ... First, how is Customer related to CPostal table? That is what field(s) in the two tables are used to establish the relationship. Second, how do you define first occurence - the earliest, latest, biggest, orsmallest value in CP_Num? Or is it based on some other field in the record. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Nuno Gomes wrote: Hello all, I have table Customer with the field CP I have the table Cpostal with the field CP_NUM The relation between this two tables is Customer.CP 1-N Cpostal.CP_NUM This means that the for one CP code in the table Customer i have N CP_NUM in the table Cpostal. My goal is update the field CP with the first ocorrence of CP_NUM. How can i do this?... Thank's in advance, Nuno Gomes |
#5
|
|||
|
|||
Help with a query...
Thank you for helping me.
The problem is that for my table CPOSTAL i have N records for my field CP in the CUSTOMER table. So, i can't use the query like: UPDATE customer INNER JOIN cpostal ON customer.CP = cpoatal.CP_NUM SET= ....; .... because for 1 record in the table CUSTOMER the access find N records in the table CPOSTAL... So, for 10000 records in the table CUSTOMER, this update updates 138000 lignes. I think this is not ok. How i update the field CUSTOMER.CP with the first occorence in of CPOSTAL.CP_NUM I think i have a problem, or not?!?!... Nuno Gomes |
#6
|
|||
|
|||
Help with a query...
I am sorry, but we are not communicating. I really do not understand what you
are trying to do. Perhaps someone else will. As I said earlier: Post a small sample of records in Customer table (even one record) and a sample of the corresponding records in CPostal and then show us what the record in the Customer table should look like after it is updated. Perhaps then I (or someone else) can understand what you are attempting to do. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Nuno Gomes wrote: Thank you for helping me. The problem is that for my table CPOSTAL i have N records for my field CP in the CUSTOMER table. So, i can't use the query like: UPDATE customer INNER JOIN cpostal ON customer.CP = cpoatal.CP_NUM SET= ....; ... because for 1 record in the table CUSTOMER the access find N records in the table CPOSTAL... So, for 10000 records in the table CUSTOMER, this update updates 138000 lignes. I think this is not ok. How i update the field CUSTOMER.CP with the first occorence in of CPOSTAL.CP_NUM I think i have a problem, or not?!?!... Nuno Gomes |
#7
|
|||
|
|||
Help with a query...
Nuno Gomes wrote:
Thank you for helping me. The problem is that for my table CPOSTAL i have N records for my field CP in the CUSTOMER table. So, i can't use the query like: UPDATE customer INNER JOIN cpostal ON customer.CP = cpoatal.CP_NUM SET= ....; ... because for 1 record in the table CUSTOMER the access find N records in the table CPOSTAL... So, for 10000 records in the table CUSTOMER, this update updates 138000 lignes. I think this is not ok. How i update the field CUSTOMER.CP with the first occorence in of CPOSTAL.CP_NUM I think i have a problem, or not?!?!... Show some example data from both tables. Then show what you want the data in the customer table to look like after the query is run (based on the example data you provide) -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. |
#8
|
|||
|
|||
Help with a query...
Hello, all
I'm going to give you the real case... I've got my CUSTOMER table: C_CLIENTE, CP_NUM_1, CP_NUM_2, CP_TEXTO 1 4000 055 PORTO 6 1000 LISBOA 7 1000 LISBOA .... And there is the CPOSTAL table: CP4 CP3 CPALF 1000 262 LISBOA 1000 263 LISBOA 1000 261 LISBOA 1000 264 LISBOA 1000 249 LISBOA 1000 265 LISBOA 1000 080 LISBOA 1000 996 LISBOA 1000 268 LISBOA 1000 019 LISBOA 1000 021 LISBOA 1000 022 LISBOA 1000 271 LISBOA 1000 215 LISBOA 1000 266 LISBOA 1000 225 LISBOA 1000 094 LISBOA 1000 216 LISBOA 1000 217 LISBOA This is what i wont after update the fied CP_NUM_2 in the table CUSTOMER, using the field CP3 in the table CPOSTAL. CUSTOMER table: C_CLIENTE, CP_NUM_1, CP_NUM_2, CP_TEXTO 1 4000 055 PORTO 6 1000 019 LISBOA 7 1000 019 LISBOA .... Thank you for all your help. Nuno Gomes |
#9
|
|||
|
|||
Help with a query...
PERHAPS what you are looking for is
UPDATE Customer SET CP_Num_2 = DMin("CP3","CPostal","CP4=" & CP_Num_1) WHERE Customer.CP_NUM_2 Is Null IF CP4 is not a number field, but is a text field then modify that to: UPDATE Customer SET CP_Num_2 = DMin("CP3","CPostal","CP4=""" & CP_Num_1 & """") WHERE Customer.CP_NUM_2 Is Null John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Nuno Gomes wrote: Hello, all I'm going to give you the real case... I've got my CUSTOMER table: C_CLIENTE, CP_NUM_1, CP_NUM_2, CP_TEXTO 1 4000 055 PORTO 6 1000 LISBOA 7 1000 LISBOA ... And there is the CPOSTAL table: CP4 CP3 CPALF 1000 262 LISBOA 1000 263 LISBOA 1000 261 LISBOA 1000 264 LISBOA 1000 249 LISBOA 1000 265 LISBOA 1000 080 LISBOA 1000 996 LISBOA 1000 268 LISBOA 1000 019 LISBOA 1000 021 LISBOA 1000 022 LISBOA 1000 271 LISBOA 1000 215 LISBOA 1000 266 LISBOA 1000 225 LISBOA 1000 094 LISBOA 1000 216 LISBOA 1000 217 LISBOA This is what i wont after update the fied CP_NUM_2 in the table CUSTOMER, using the field CP3 in the table CPOSTAL. CUSTOMER table: C_CLIENTE, CP_NUM_1, CP_NUM_2, CP_TEXTO 1 4000 055 PORTO 6 1000 019 LISBOA 7 1000 019 LISBOA ... Thank you for all your help. Nuno Gomes |
Thread Tools | |
Display Modes | |
|
|