Help with a query...

[Nuno Gomes]

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

 

[John Spencer]

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]

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]

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]

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

 

[John Spencer]

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

 

[Bob Barrows [MVP]]

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)

 

[Nuno Gomes]

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

 

[John Spencer]

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