If Statement

[redbay]

I have 3 tables, tblComplaint, tblCustomer & tblAction. tblComplaint can have 0ne or more customers & the customer may have one or more action, each table has a status field but what i want to achieve is for example complaint no 102 has 2 customers (customer 1 & 2)& they each have two actions against them, i want to be able to say if all actions against each customer are closed then tblCustomer status = closed then if all customers are closed then complaint status = closed. is there an easy way to build this query please?

 

[Remou]

Hi
You say:

tblComplaint can have 0ne or more customers

How is this set up? Is there a CustomerComplaint table, for example, or are Complaints duplicated for each Customer? Similarly, how are Actions related to Customers?

 

[redbay]

tblComplaint
CompaintID - A/N P/K

tblCustomer
CustomerID - A/N P/K

tblAction
ActionID - A/N P/K

tblCompalint - one-to-many relationship with tblCustomer

tblCustomer - one-to-many relationship with tblActions

 

[Remou]

Hello again.
I think I can see a way to update the customer table based on a count of completed actions (a similar method could be applied to update the complaints table). However, I have a problem with the customer table. From your description above, a customer can only occur once in the customer table and therefore can only have one complaint. I would have expected this table to be called an incident table, created by including a customer ID from a customer table and a complaint ID from a complaint table, with other fields as appropriate.
I have tried the SQL below, and it seems to work, but it does not come with any guarantees, because I cobbled it together from other posts in these fora.

UPDATE tblCustomer SET tblCustomer.Status = True
WHERE ((((Select Count(*) FROM tblAction A 
    WHERE A.CustomerID=tblCustomer.ID ))=(Select Count(*) FROM tblAction A 
    WHERE A.CustomerID=tblCustomer.ID AND A.Status=True)));

 

[redbay]

Hi Thanks for responding,

i am getting there slowly. i copied your SQL and put it into a query. if i update the action closed in my form nothing happens but if i run the query it opens 3 input boxes, the first asks for A.CustomerID, the next tblCustomerID the next A.Status. if a dont put any data into te boxes it closes all the status fields in the tblCustomer if i input any data it says you are about to update 0 records.....i would like it so that when all the actions are closed then the customer status will close automatically, are we nearly there?

 

[Remou]

I should have offered more explanation.
[Blue]Query is in blue[/Blue]
Note is in black.
[Blue]UPDATE tblCustomer[/Blue]
Where tblCustomer is the name of the customer table (from above)
[Blue]SET tblCustomer.Status = True[/Blue]
Where Status is the name of the status field in tblCustomer that you wish to update. It is a Yes/No field type in this example. You should change this to the correct field name.
[Blue]WHERE ((((Select Count(*) FROM tblAction A[/Blue]
This sets up a sub query. tblAction is the name of the Action table (from above) and A is an alias, to be used later.
[Blue]WHERE A.CustomerID=tblCustomer.ID ))[/Blue]
This continues the sub query. A.CustomerID is the A alias, just mentioned, and the customer ID field in tblAction, necessary for the join shown in your notes. You should change this to the correct field name. tblCustomer.ID is the customer table name and customer ID field name. I should have noted from above that this is CustomerID, so this should probably read tblCustomer.CustomerID.
[Blue]=(Select Count(*) FROM tblAction A
WHERE A.CustomerID=tblCustomer.ID AND A.Status=True)));[/Blue]
This is a second sub query. The notes regarding A.CustomerID and tblCustomer.ID apply here as well. A.Status is the name of the status field from tblAction and the A alias. It is a Yes/No field type in this example. You should change Status to the correct field name.
The general idea is update the status of customer to true if the count of all actions (sub query 1) for a customer equals the count of actions with a status of true (sub query 2).

 

[redbay]

Very complex but i am trying to get my head around it, this is my SQl
UPDATE tblCustomer SET tblCustomer.Status = True
WHERE ((((Select Count(*) FROM tblAction A
WHERE A.CustomerID=tblCustomerID ))=(Select Count(*) FROM tblAction A
WHERE A.CustomerID=tblCustomerID AND A.ActionClosed=True)));

But it's still trying to update all the records in the table???

 

[Remou]

Just at a quick glance, you are missing fields from both the sub queries:
WHERE A.CustomerID=tblCustomer[red].CustomerID[/red]

 

[redbay]

Sorry have changed this but i'm still getting the same parameter boves open, then the message you are about to update 57 records

 

[Remou]

This:

SELECT tblAction.CustomerID, tblAction.ActionClosed, tblCustomer.Status, (Select Count(*) FROM tblAction A 
    WHERE A.CustomerID=tblCustomer.customerID ) AS AllActs, (Select Count(*) FROM tblAction A 
    WHERE A.CustomerID=tblCustomer.customerID AND A.ActionClosed=True) AS CompleteActs
FROM tblCustomer LEFT JOIN tblAction ON tblCustomer.customerID = tblAction.CustomerID;

Should produce:
[tt](tblAction tblAction tblCustomer Sub Query 1 Sub Query 2)
CustomerID ActionClosed Status AllActs CompleteActs
1 Yes Yes 2 2
1 Yes Yes 2 2
2 Yes No 2 1
2 No No 2 1
6 Yes No 3 2
6 Yes No 3 2
6 No No 3 2[/tt]

CustomerID 1 is the only one that should update as AllActs (ie count of all actions) is equal to CompleteActs (ie Actions marked complete).
As this is a select, rather than update, it may help us fiddle around safely to find out where my visualisation of the tables differs from yours. <grasps furiously at straws >

 

[redbay]

This is where i'm going wrong. I also have a table called tblHistory that has 3 fields ComplaintID, CustomerID & ActionID which links all 3 tables together so when i apply your sql above it all go's to pot, ive included the history table into the query and my sql is as so

SELECT tblAction.CustomerID AS Expr1, tblAction.ActionClosed, tblCustomer.Status, (Select Count(*) FROM tblAction A WHERE A.CustomerID=tblCustomer.customerID ) AS AllActs, (Select Count(*) FROM tblAction A WHERE A.CustomerID=tblCustomer.customerID AND A.ActionClosed=True) AS CompleteActs
FROM tblCustomer INNER JOIN (tblAction INNER JOIN tblHistory ON tblAction.ActionID = tblHistory.ActionID) ON tblCustomer.CustomerID = tblHistory.CustomerID;

but it still opens 2 parameter boxes and the reults are

Expr1 ActionClosed Status AllActs CompletedActs
yes no 0 0
yes no 0 0
yes no 0 0

Sorry for complicating things!!!

 

[redbay]

Hi Remou

Have realised, tblAction is ActionID so i've changed that and got rid of the parameter boxes. Now the query shows as you have mapped except the data in status allacts and complete acts is still 0, we are getting there and thanks for all your help

 

[Remou]

Hi
I think that we have a small misunderstanding, the CustomerID in tblAction is the foreign key for the customer table, not the primary key for the table itself. You mentioned "tblCustomer - one-to-many relationship with tblActions", which seemed to imply a set up like this:
[tt]tblAction )From your post
ActionID - A/N P/K )
CustomerID F/K - a guess[/tt]

The history table comes as a surprise to me. If it also included a staus field, it might be the easiest way to control all this.

 

[redbay]

tblAction
ActionID - A/N P/K
has no CustomerID but is linked through History table

 

[Remou]

I strongly suggest you move the ActionClosed flag to the history table. This will eliminate nearly all your problems. You would need to start again with the queries as they are based on having two tables, not three.

 

[redbay]

OK will try that and thanks for all your help

 

[Remou]

You are very welcome. I hope it all works out.