find date last used, deativate if older than 12 months

[trevorC via AccessMonster.com]

Hi All,
I have a new task to add to my DB,

I need to deactivate some customers we have if they have not conducted any
business with us for more than 12 months.

I have a customer tbl with Customer name and Active fields ' this is the one
i need to update '
i have a Jobs tbl with customer name, dispatch date.

I need to find the last time a customers job was here, and if it is more than
12 months then deactivate the customer, and create a report list of all the
customers just deactivated.

I can use VBA to loop through the Jobs tbl but am unsure of the code to use
to do the above.

regards
Trevor

 

[John W. Vinson]

Hi All,
I have a new task to add to my DB,

I need to deactivate some customers we have if they have not conducted any
business with us for more than 12 months.

I have a customer tbl with Customer name and Active fields ' this is the one
i need to update '
i have a Jobs tbl with customer name, dispatch date.

I need to find the last time a customers job was here, and if it is more than
12 months then deactivate the customer, and create a report list of all the
customers just deactivated.

I can use VBA to loop through the Jobs tbl but am unsure of the code to use
to do the above.

regards
Trevor

No VBA is needed: an Update query will do it instead:

UPDATE tblCustomer SET Active = False WHERE NOT EXISTS
(SELECT [Customer Name] FROM tblCustomer WHERE [dispatch date] >
DateAdd("yyyy", -1, Date()))

I have to question your use of customer name as a key, though! Names are NOT
unique; what will you do if Robert Smith wants to do business with you but you
already have a Robert Smith in the database? You really should have a unique
CustomerID instead.

 

[trevorC via AccessMonster.com]

Thanks John,

I have to question your use of customer name as a key, though! Names are NOT
unique; what will you do if Robert Smith wants to do business with you but you
already have a Robert Smith in the database? You really should have a unique
CustomerID instead.

I do have a unique Customer Id as a primary key, I only showed the relevant
fields, Customer name and Active. We also have many customers that have sub
customers that we have to track for them. So we may have cust1 and we have to
track the jobs for thier customer so our customer entry would be cust1_subA
as a Customer SUB name as well as the main customer name.

thanks for your assistance with this.

regards
Trevor

 

[John W. Vinson]

Thanks John,

I do have a unique Customer Id as a primary key, I only showed the relevant
fields, Customer name and Active. We also have many customers that have sub
customers that we have to track for them. So we may have cust1 and we have to
track the jobs for thier customer so our customer entry would be cust1_subA
as a Customer SUB name as well as the main customer name.

I answered the question as posted. My crystal ball has been a bit foggy
lately!

So do you still have a problem? I have no idea how your tables store customers
and subcustomers and dates.

thanks for your assistance with this.

You're welcome... I guess... it's not clear that I'm helping at this point!

 

[trevorC via AccessMonster.com]

Hi John.

Been on holiday...

Thanks for your assistance with this but there is confusion about the tables,
i'll try and explain it again.

I have 2 tables, Customers and Jobs,
Table 1 = Customer Names
Customer name
Active
and ...other fields not relative to this

Table 2 = Jobs
Customer name
Dispatch Date
and ...other fields not relative to this

I need to find from the Active customers, any customer who has not had any
jods from us for the past 12 months, the JOBS table has the Dispatch date and
customer name.

I need to provide a report of the customers to be De-Activated and then
change thier active status.
The Update query you provided only shows the tblCustomer.

UPDATE tblCustomer SET Active = False WHERE NOT EXISTS
(SELECT [Customer Name] FROM tblCustomer WHERE [dispatch date] >
DateAdd("yyyy", -1, Date()))

 

[John W. Vinson]

Hi John.

Been on holiday...

Thanks for your assistance with this but there is confusion about the tables,
i'll try and explain it again.

I have 2 tables, Customers and Jobs,
Table 1 = Customer Names
Customer name
Active
and ...other fields not relative to this

Table 2 = Jobs
Customer name
Dispatch Date
and ...other fields not relative to this

The customer name should *SIMPLY NOT EXIST* in the Jobs table.

A Job doesn't have a first name or a last name. Those fields should exist in
the Customers table *AND NOPLACE ELSE*.

You say the other fields (CustomerID?) are irrelevant. I emphatically
disagree. The Jobs table should have a CustomerID as a link to the primary key
of the Customers table. That's how relational databases *work*.

What are the other (irrelevant to you) fields in these tables? What is the
Primary Key of each table? How are the tables related?

I need to find from the Active customers, any customer who has not had any
jods from us for the past 12 months, the JOBS table has the Dispatch date and
customer name.

I need to provide a report of the customers to be De-Activated and then
change thier active status.
The Update query you provided only shows the tblCustomer.

UPDATE tblCustomer SET Active = False WHERE NOT EXISTS
(SELECT [Customer Name] FROM tblCustomer WHERE [dispatch date] >
DateAdd("yyyy", -1, Date()))

That's what you asked for: a query to set Active to false. So that's the
question I answered.

If you want to create a report to *display* (but not to update) the inactive
customers, try

SELECT tblCustomer.*, tblJobs.*
FROM tblCustomer INNER JOIN tblJobs
ON tblCustomer.CustomerName = tblJobs.CustomerName
WHERE NOT EXISTS
(SELECT [Customer Name] FROM tblJobs WHERE [dispatch date] >
DateAdd("yyyy", -1, Date()))

 

[John W. Vinson]

Apologies; on rereading your original message you did ask for a report.

You'll need two queries, one for the action, one for the report. I'd STRONGLY
suggest running the report first, so you can check the data; if you delete the
records, there's no way to recover them except from a backup of your database.