Updatable queries

[Guest]

Sorry if this is too complicated, but I am desperate to work this out! I
have two tables, the first one (ContactDetails) contains information about
clients, the usual stuff, name, address, etc, but it also contains 2 other
fields: GAStatus and RGStatus.

The second table contains client transactions, including the fields
TransDate and TransType. The tables have a one (ContactDetails) to many
(FinancialDetails) relationship. I have created a query where the criteria
is:
- all transactions where TransDate (from FinancialDetails) is in last month
AND
- the TransTypes (from FinancialDetails) are “TTA†or “TTB†AND
- the GAStatus (from ContactDetails) is “GAD†AND
- RGStatus (from ContactDetails) is “LIVEâ€.

I would like to use this query to create a form where I can input to another
field in FinancialDetails called RGPaymentCycle. Everytime I have tried this
is says the query is not updatable, so I am stuck. Any advice gratefully
received.

Thanks,

 

[Guest]

Try this
This puts a fixed value ("XX")in RGPaymentCycle. If you need something else
you can adjust the SET clause

UPDATE FinancialDetails
LEFT JOIN ContactDetails ON FinancialDetails.ClientID =
ContactDetails.ClientID
SET FinancialDetails.RGPaymentCycle = "XX"
WHERE
(((FinancialDetails.TransType)="TTA" Or (FinancialDetails.TransType)="TTB")
AND
((ContactDetails.GAStatus)="GAD") AND
((ContactDetails.RGStatus)="LIVE") AND
((DatePart("m",[FinancialDetails]![TransDate]))=DatePart("m",DateAdd("m",-1,Date()))));

 

[Guest]

Thanks I will try tomorrow and let you know if it worked. I'm in a late time
zone here, so off to bed. Thanks for replying.

Try this
This puts a fixed value ("XX")in RGPaymentCycle. If you need something else
you can adjust the SET clause

UPDATE FinancialDetails
LEFT JOIN ContactDetails ON FinancialDetails.ClientID =
ContactDetails.ClientID
SET FinancialDetails.RGPaymentCycle = "XX"
WHERE
(((FinancialDetails.TransType)="TTA" Or (FinancialDetails.TransType)="TTB")
AND
((ContactDetails.GAStatus)="GAD") AND
((ContactDetails.RGStatus)="LIVE") AND
((DatePart("m",[FinancialDetails]![TransDate]))=DatePart("m",DateAdd("m",-1,Date()))));

Sorry if this is too complicated, but I am desperate to work this out! I
have two tables, the first one (ContactDetails) contains information about
clients, the usual stuff, name, address, etc, but it also contains 2 other
fields: GAStatus and RGStatus.

The second table contains client transactions, including the fields
TransDate and TransType. The tables have a one (ContactDetails) to many
(FinancialDetails) relationship. I have created a query where the criteria
is:
- all transactions where TransDate (from FinancialDetails) is in last month
AND
- the TransTypes (from FinancialDetails) are “TTA†or “TTB†AND
- the GAStatus (from ContactDetails) is “GAD†AND
- RGStatus (from ContactDetails) is “LIVEâ€.

I would like to use this query to create a form where I can input to another
field in FinancialDetails called RGPaymentCycle. Everytime I have tried this
is says the query is not updatable, so I am stuck. Any advice gratefully
received.

Thanks,