update table

[smalty]

I hope this question is in the correct forum. Any help greatly appreciated

I have two tables tClients and tFeesComm. Fields in tClient include ClientStatus and fields in tFeesComm include FeeStatus and AdviceArea.

What I would like to happen is if ClientStatus = Signed Up and if FeeStatus = Completed and if AdviceArea = Investment or Pension then create a record in another table called tClientFundPer with the client name and a default percentage of 100 JUST ONCE.

Any suggestions would be welcome

Smalty

 

[Andrzejek]

How does the tClient table relate to tFeesComm table?

Do you also have these fields in:[tt]
tClient table
ClientID (?)
ClientStatus
ClientFirstName (?)
ClientMI (?)
ClientLastName (?)
...[/tt]

"create a record in another table called tClientFundPer with the client name "
You should be using [tt]ClientID[/tt] instead, or include 'default_percentage' field in your [tt]tClient[/tt] table, IMHO

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[smalty]

I am using both your suggestions (ClientID and a percent field that has a default value of 100. My problem is getting the new record in the other table if all 3 criteria are met.

 

[Andrzejek]

I am using both your suggestions" is that:
[tt]
tClient table
ClientID
ClientStatus
ClientFirstName
ClientMI [blue]
Client_default_percentage[/blue]
ClientLastName
[/tt]

If so, what 'other table' in "the new record in the other table if all 3 criteria are met"? There is no other table, you have this field in the Client table. No nwe record needed.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[smalty]

My apologies for not explaining properly. I will try and be clearer.

I have three tables like this:

tClients [ClientID] [FullName] [ClientStatus].............

tFeeComms [FeeCommID] [ClientID] [FeeStatus] [AdviceArea]..............

tClientFundPerc [FundPercID] [ClientID] [Fund] [Percent]

So the situation is this: The [Percent] field only appears in the tClientFundPerc.
The normal process is that a Client record is created with basic information once contact is made. Over time FeeComm data will be added and at some point a client will commit a sum of money to be invested in a fund or funds. At this point the [ClientStatus], [FeeStatus] and [AdviceArea] are changed to the values mentioned in my original post and it is now and only now that when this happens I need a record created in tClientFundPerc to include [ClientID] [Percent](which has a default value of 100).
After this record has been created and at some time in the future the user will update the tClientFundPerc table with full details of all the funds a client has invested in and the percentages for each once that has been finalised.

So just to recap......I need the initial record entered into the tClientFundPerc table as and when the three criteria are met from the fields in the other two tables but this to happen just once per client.

I hope this clarifies the problem

Thank you for your interest

Regards
Smalty

 

[Andrzejek]

How about this.

Wherever you have any of the Update of tFeeComms table:[tt]
UPDATE tFeeComms
SET someField = XYZ
WHERE ...[/tt]

Do this check:
[tt]
SELECT C.[ClientStatus], F.[FeeStatus], F.[AdviceArea]
FROM tClients C, tFeeComms F
WHERE C.ClientID = F.ClientID
AND C.ClientID = 1234
AND C.[ClientStatus] = 'Signed Up'
AND F.[FeeStatus] = 'Completed'
AND F.[AdviceArea] IN ('Investment', 'Pension')
[/tt]
open the rst based on the SQL above and:
[pre]
If rst.RecordCount = 0 Then (or rst.BOF = rst.EOF)
INSERT INTO tClientFundPerc(FundPercID, ClientID, Fund, Percent)
Values ( , , , )
End If[/pre]

Would that work?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[smalty]

I am pretty sure that your suggestion will work as I understand the theory. However I am very 'rusty' on most of this stuff and therefore it will take me a while to work out exactly how to write the entire code you have described. I will get back as soon as I have done that.

Once again I really appreciate your help

Regards
Smalty

 

[smalty]

In fact I have re read your suggestion and I don't think it will work.

I think you are saying when any change is made in either tClient or tClientFeeComms check if the 3 criteria exist for that client using an sql to produce a record set. If the record count for that record set is zero add that client into tClientFundPerc.

This last part is not correct. In fact if the record count is zero I don't want anything to happen.

If however the record count is 1 then I want to check tClientFundPerc has a record for that client.

If there is no record for that client in tClientFundPerc then create one.

But if there is a record in tClientFundPerc then do nothing.

I hope that makes sense.

Regards
Smalty