Help on update query

[Edward G]

There is no reason to add a column to the customers table. What you want is
a calculated field in your query.
You can add that query to the underlying query of any form, subform, report
etc you might possibly want to make.
The calculated field would be as follows:
CustomerStatus: Iif([YourTotalsField]>0 and
[YourTotalsField]<=50000,"Standard
Customer") & Iif([YourTotalsField]>50000 and
[YourTotalsField]<=100000,"Silver
Customer") & Iif([YourTotalsField]>100000,"Gold Customer")

 

[Amir]

Assuming I do need to create update query for that matter.

How can this be done?

Amir.

There is no reason to add a column to the customers table. What you want
is
a calculated field in your query.
You can add that query to the underlying query of any form, subform,
report
etc you might possibly want to make.
The calculated field would be as follows:
CustomerStatus: Iif([YourTotalsField]>0 and
[YourTotalsField]<=50000,"Standard
Customer") & Iif([YourTotalsField]>50000 and
[YourTotalsField]<=100000,"Silver
Customer") & Iif([YourTotalsField]>100000,"Gold Customer")

(I used the sample database "Northwind" for my question)

I added a field to the "customers" table, and name it "customer status".
This field should be updated by update query as follows:
I wish to calculate each customer's total purchases based on "order details"
table. I multiply the "unitprice" in "quantity" fields.
I know how to make totals query, so I can see the total amount of orders per
customers.

I wish that the "customer status" field will be update as follows: if the
total amount per customer is higher than 100,000 the status will be updated
to "Gold Customer", if it is between 50,000 and 100,000 it will be
updated
to "Silver Customer" and the rest will be updated to "Standard Customer"

Please advise how can I do that.

Thanks.

 

[Edward G]

Amir,
I was hoping one of the other posters here would come to my assistance here
in discouraging you from
doing the unnecessary, but if you insist on going this route:
Add your CustomerStatus field to your table and add it to the QBE grid of
your query.
Change the Select Query to an Update Query by clicking on the word Query on
the Design View menu bar.
Then in the Update To: row of the QBE Grid under your CustomerStatus field
enter:
Iif([YourTotalsField]>0 and
[YourTotalsField]<=50000,"Standard
Customer") & Iif([YourTotalsField]>50000 and
[YourTotalsField]<=100000,"Silver
Customer") & Iif([YourTotalsField]>100000,"Gold Customer")
Then click the exclamation point on the Toolbar to run the update.

E

Assuming I do need to create update query for that matter.

How can this be done?

Amir.

There is no reason to add a column to the customers table. What you want
is
a calculated field in your query.
You can add that query to the underlying query of any form, subform,
report
etc you might possibly want to make.
The calculated field would be as follows:
CustomerStatus: Iif([YourTotalsField]>0 and
[YourTotalsField]<=50000,"Standard
Customer") & Iif([YourTotalsField]>50000 and
[YourTotalsField]<=100000,"Silver
Customer") & Iif([YourTotalsField]>100000,"Gold Customer")

(I used the sample database "Northwind" for my question)

I added a field to the "customers" table, and name it "customer status".
This field should be updated by update query as follows:
I wish to calculate each customer's total purchases based on "order details"
table. I multiply the "unitprice" in "quantity" fields.
I know how to make totals query, so I can see the total amount of

orders
per

customers.

I wish that the "customer status" field will be update as follows: if the
total amount per customer is higher than 100,000 the status will be updated
to "Gold Customer", if it is between 50,000 and 100,000 it will be
updated
to "Silver Customer" and the rest will be updated to "Standard Customer"

Please advise how can I do that.

Thanks.

 

[Amir]

Edward,

Your explation is very helpful.

Last question... which was my biggest problem from the beggining...
The field you reffered as "YourTotalsfield" is indeed a calculated field.
As far as I know I can't base update query on totals query.

So.. how can I bring the calculated results, into the queries you suggested?

Amir.

Amir,
I was hoping one of the other posters here would come to my assistance
here
in discouraging you from
doing the unnecessary, but if you insist on going this route:
Add your CustomerStatus field to your table and add it to the QBE grid of
your query.
Change the Select Query to an Update Query by clicking on the word Query
on
the Design View menu bar.
Then in the Update To: row of the QBE Grid under your CustomerStatus field
enter:
Iif([YourTotalsField]>0 and
[YourTotalsField]<=50000,"Standard
Customer") & Iif([YourTotalsField]>50000 and
[YourTotalsField]<=100000,"Silver
Customer") & Iif([YourTotalsField]>100000,"Gold Customer")
Then click the exclamation point on the Toolbar to run the update.

E

Assuming I do need to create update query for that matter.

How can this be done?

Amir.

There is no reason to add a column to the customers table. What you
want
is
a calculated field in your query.
You can add that query to the underlying query of any form, subform,
report
etc you might possibly want to make.
The calculated field would be as follows:
CustomerStatus: Iif([YourTotalsField]>0 and
[YourTotalsField]<=50000,"Standard
Customer") & Iif([YourTotalsField]>50000 and
[YourTotalsField]<=100000,"Silver
Customer") & Iif([YourTotalsField]>100000,"Gold Customer")




(I used the sample database "Northwind" for my question)

I added a field to the "customers" table, and name it "customer status".
This field should be updated by update query as follows:
I wish to calculate each customer's total purchases based on "order
details"
table. I multiply the "unitprice" in "quantity" fields.
I know how to make totals query, so I can see the total amount of orders
per
customers.

I wish that the "customer status" field will be update as follows: if the
total amount per customer is higher than 100,000 the status will be
updated
to "Gold Customer", if it is between 50,000 and 100,000 it will be
updated
to "Silver Customer" and the rest will be updated to "Standard Customer"

Please advise how can I do that.

Thanks.

 

[Edward G]

Convert your totals query into a Make table query and run it (click Query
on Menu Bar in Design View and select Make table then click exclamation
point on toolbar). Create new query that contains this new table and your
customer
table. Add "yourTotalsField" from the new table and the CustomerStatus Field
from Customers. Convert to Update Query as before and run as before.

Edward,

Your explation is very helpful.

Last question... which was my biggest problem from the beggining...
The field you reffered as "YourTotalsfield" is indeed a calculated field.
As far as I know I can't base update query on totals query.

So.. how can I bring the calculated results, into the queries you suggested?

Amir.

Amir,
I was hoping one of the other posters here would come to my assistance
here
in discouraging you from
doing the unnecessary, but if you insist on going this route:
Add your CustomerStatus field to your table and add it to the QBE grid of
your query.
Change the Select Query to an Update Query by clicking on the word Query
on
the Design View menu bar.
Then in the Update To: row of the QBE Grid under your CustomerStatus field
enter:
Iif([YourTotalsField]>0 and
[YourTotalsField]<=50000,"Standard
Customer") & Iif([YourTotalsField]>50000 and
[YourTotalsField]<=100000,"Silver
Customer") & Iif([YourTotalsField]>100000,"Gold Customer")
Then click the exclamation point on the Toolbar to run the update.

E

Assuming I do need to create update query for that matter.

How can this be done?

Amir.

There is no reason to add a column to the customers table. What you
want
is
a calculated field in your query.
You can add that query to the underlying query of any form, subform,
report
etc you might possibly want to make.
The calculated field would be as follows:
CustomerStatus: Iif([YourTotalsField]>0 and
[YourTotalsField]<=50000,"Standard
Customer") & Iif([YourTotalsField]>50000 and
[YourTotalsField]<=100000,"Silver
Customer") & Iif([YourTotalsField]>100000,"Gold Customer")




(I used the sample database "Northwind" for my question)

I added a field to the "customers" table, and name it "customer status".
This field should be updated by update query as follows:
I wish to calculate each customer's total purchases based on "order
details"
table. I multiply the "unitprice" in "quantity" fields.
I know how to make totals query, so I can see the total amount of orders
per
customers.

I wish that the "customer status" field will be update as follows:

if
the

total amount per customer is higher than 100,000 the status will be
updated
to "Gold Customer", if it is between 50,000 and 100,000 it will be
updated
to "Silver Customer" and the rest will be updated to "Standard Customer"

Please advise how can I do that.

Thanks.

 

[Amir]

(I used the sample database "Northwind" for my question)

I added a field to the "customers" table, and name it "customer status".
This field should be updated by update query as follows:
I wish to calculate each customer's total purchases based on "order details"
table. I multiply the "unitprice" in "quantity" fields.
I know how to make totals query, so I can see the total amount of orders per
customers.

I wish that the "customer status" field will be update as follows: if the
total amount per customer is higher than 100,000 the status will be updated
to "Gold Customer", if it is between 50,000 and 100,000 it will be updated
to "Silver Customer" and the rest will be updated to "Standard Customer"

Please advise how can I do that.

Thanks.

 

[Amir]

Many thanks for your help.

It's working like a magic.

Convert your totals query into a Make table query and run it (click Query
on Menu Bar in Design View and select Make table then click exclamation
point on toolbar). Create new query that contains this new table and your
customer
table. Add "yourTotalsField" from the new table and the CustomerStatus
Field
from Customers. Convert to Update Query as before and run as before.

Edward,

Your explation is very helpful.

Last question... which was my biggest problem from the beggining...
The field you reffered as "YourTotalsfield" is indeed a calculated field.
As far as I know I can't base update query on totals query.

So.. how can I bring the calculated results, into the queries you suggested?

Amir.

Amir,
I was hoping one of the other posters here would come to my assistance
here
in discouraging you from
doing the unnecessary, but if you insist on going this route:
Add your CustomerStatus field to your table and add it to the QBE grid of
your query.
Change the Select Query to an Update Query by clicking on the word
Query
on
the Design View menu bar.
Then in the Update To: row of the QBE Grid under your CustomerStatus field
enter:
Iif([YourTotalsField]>0 and
[YourTotalsField]<=50000,"Standard
Customer") & Iif([YourTotalsField]>50000 and
[YourTotalsField]<=100000,"Silver
Customer") & Iif([YourTotalsField]>100000,"Gold Customer")
Then click the exclamation point on the Toolbar to run the update.

E

Assuming I do need to create update query for that matter.

How can this be done?

Amir.

There is no reason to add a column to the customers table. What you
want
is
a calculated field in your query.
You can add that query to the underlying query of any form,
subform,
report
etc you might possibly want to make.
The calculated field would be as follows:
CustomerStatus: Iif([YourTotalsField]>0 and
[YourTotalsField]<=50000,"Standard
Customer") & Iif([YourTotalsField]>50000 and
[YourTotalsField]<=100000,"Silver
Customer") & Iif([YourTotalsField]>100000,"Gold Customer")




(I used the sample database "Northwind" for my question)

I added a field to the "customers" table, and name it "customer
status".
This field should be updated by update query as follows:
I wish to calculate each customer's total purchases based on "order
details"
table. I multiply the "unitprice" in "quantity" fields.
I know how to make totals query, so I can see the total amount of
orders
per
customers.

I wish that the "customer status" field will be update as follows: if
the
total amount per customer is higher than 100,000 the status will be
updated
to "Gold Customer", if it is between 50,000 and 100,000 it will be
updated
to "Silver Customer" and the rest will be updated to "Standard
Customer"

Please advise how can I do that.

Thanks.