update subquery

[supicek]

Problem description:

TABLE: tbCustomers with fields: cuID, cuStatus (customer, prospect, key
accnt,etc)
QUERY: qLastSales with fields: cuID, lastInvoiceDate, newStatus (Customer,
Customer1, Customer2)

the query goes through sales table and lookup the last invoice date, based
on this date it allocates newStatus for each customer. The statuses are:
Customer - last sales not older then 1 year
Customer1 - last sales older then 1 year
Customer2 - last sales older then 2 years

I would like to create an UPDATE query which would amend the
tbCustomers.cuStatus field with value from qLastSales.newStatus. I think I
would have to use a subquery, but cannot figure it out.

thanks for your help

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This is incorrect thinking re: Relational Databases. You should NOT
update a table with varying data. You SHOULD create a query (or View)
that will indicate the customers' status whenever you run the query -
you've done that with your query "qLastSales." If the user just looks
at a View (aka query), the newly calculated data will always be correct.

Therefore, you should use your query qLastSales as the "Customer Status"
View. You can display the query's output as a query datasheet, in a
form, or in a report.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSTJTMYechKqOuFEgEQJ80QCgoLLS6klICNmQBgdfZmgyek6/6DsAn371
u/9ywBRAmdpE8sUZ6wTrM5yy
=aJ3Y
-----END PGP SIGNATURE-----

 

[raghu.bhandari.tech]

This may help you:

http://sqlhelp.wordpress.com/2008/12/19/sub-query-update/

Raghu
MCP