Trouble with query

[Radhika]

I have a query with the following information
1. ID#
2. InitialDate
3. DateofSurgery
4. ReasonForVisit
5. Date diff (calculated) = InitalDate-DateofSurgery

I want to update fields in ReasonforVisit such that: If Datediff is less
than 30, ReasonForVisit=Initial Fit.

I tried to do this using an update query, but I get a message saying that I
must use an updateable query.

What am i doing wrong?

Thank you,

Shukla

 

[Douglas J. Steele]

UPDATE MyTable
SET ReasonForVisit = "Initial Fit"
WHERE DateDiff("d", [InitialDate], [DateofSurgery]) < 30

 

[Graham Mandeno]

Hi Shulka

I can't say what is wrong with your query without seeing the SQL, but a
query like this ought to work:

Update [YourTable] Set [ReasonForVisit]='Initial Fit'
where [ReasonForVisit] is Null and [InitialDate]-[DateOfSurgery]<30;

 

[Radhika]

Thank you

I am still having trouble.
Below is my SQL:
UPDATE tbl_NewPatientInfo INNER JOIN qry_FirstDate_TEPRecords ON
tbl_NewPatientInfo.[MDACC#] = qry_FirstDate_TEPRecords.[MDACC#] SET
tbl_NewPatientInfo.[Reason for referral] = "Initial Fit"
WHERE (((DateDiff("d",[tbl_NewPatientInfo].[Date 1st TEP],[MinOfDate]))<30));

I keep getting a message saying 'Operation must use an updateable query'.
The table 'tbl_NewPatientInfo' is on the one side of a one-to-many
relationship. In the above query, it is linked to another qry that selects
the first date for each ID# (I need to first day to make the datediff
calculation).

Is there something that I am doing incorrectly.

Thank you!

UPDATE MyTable
SET ReasonForVisit = "Initial Fit"
WHERE DateDiff("d", [InitialDate], [DateofSurgery]) < 30

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a query with the following information
1. ID#
2. InitialDate
3. DateofSurgery
4. ReasonForVisit
5. Date diff (calculated) = InitalDate-DateofSurgery

I want to update fields in ReasonforVisit such that: If Datediff is less
than 30, ReasonForVisit=Initial Fit.

I tried to do this using an update query, but I get a message saying that
I
must use an updateable query.

What am i doing wrong?

Thank you,

Shukla

 

[Douglas J. Steele]

Presumably qry_FirstDate_TEPRecords is intended to return the first date on
file for the patient.

You could try the following, but it may be slow if your table is large:

UPDATE tbl_NewPatientInfo
SET [Reason for referral] = "Initial Fit"
WHERE DateDiff("d", [Date 1st TEP],
DMin("NameOfDateField", "tbl_NewPatientInfo", "[MDACC#] = '" & [MDACC#] &
"'")) < 30

I've assumed that MDACC# is a text field.

Replace NameOfDateField with the appropriate field name.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you

I am still having trouble.
Below is my SQL:
UPDATE tbl_NewPatientInfo INNER JOIN qry_FirstDate_TEPRecords ON
tbl_NewPatientInfo.[MDACC#] = qry_FirstDate_TEPRecords.[MDACC#] SET
tbl_NewPatientInfo.[Reason for referral] = "Initial Fit"
WHERE (((DateDiff("d",[tbl_NewPatientInfo].[Date 1st
TEP],[MinOfDate]))<30));

I keep getting a message saying 'Operation must use an updateable query'.
The table 'tbl_NewPatientInfo' is on the one side of a one-to-many
relationship. In the above query, it is linked to another qry that selects
the first date for each ID# (I need to first day to make the datediff
calculation).

Is there something that I am doing incorrectly.

Thank you!

UPDATE MyTable
SET ReasonForVisit = "Initial Fit"
WHERE DateDiff("d", [InitialDate], [DateofSurgery]) < 30

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a query with the following information
1. ID#
2. InitialDate
3. DateofSurgery
4. ReasonForVisit
5. Date diff (calculated) = InitalDate-DateofSurgery

I want to update fields in ReasonforVisit such that: If Datediff is
less
than 30, ReasonForVisit=Initial Fit.

I tried to do this using an update query, but I get a message saying
that
I
must use an updateable query.

What am i doing wrong?

Thank you,

Shukla

 

[Radhika]

Thank you.

I am still having some trouble. The Date is from a second table called
'tbl_TEPRecords'. The Date of 1st TEP is from then first table,
'tbl_NewPatient'. They are related by a one-to-many relation, where
tbl_NewPatientInfo is on the one side and tbl_TEPRecords is on the many side.
MDACC is the primary key in both tables and is a text field. How do I
incorporate this into the SQL?

Thank you,
Shukla

Presumably qry_FirstDate_TEPRecords is intended to return the first date on
file for the patient.

You could try the following, but it may be slow if your table is large:

UPDATE tbl_NewPatientInfo
SET [Reason for referral] = "Initial Fit"
WHERE DateDiff("d", [Date 1st TEP],
DMin("NameOfDateField", "tbl_NewPatientInfo", "[MDACC#] = '" & [MDACC#] &
"'")) < 30

I've assumed that MDACC# is a text field.

Replace NameOfDateField with the appropriate field name.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you

I am still having trouble.
Below is my SQL:
UPDATE tbl_NewPatientInfo INNER JOIN qry_FirstDate_TEPRecords ON
tbl_NewPatientInfo.[MDACC#] = qry_FirstDate_TEPRecords.[MDACC#] SET
tbl_NewPatientInfo.[Reason for referral] = "Initial Fit"
WHERE (((DateDiff("d",[tbl_NewPatientInfo].[Date 1st
TEP],[MinOfDate]))<30));

I keep getting a message saying 'Operation must use an updateable query'.
The table 'tbl_NewPatientInfo' is on the one side of a one-to-many
relationship. In the above query, it is linked to another qry that selects
the first date for each ID# (I need to first day to make the datediff
calculation).

Is there something that I am doing incorrectly.

Thank you!

UPDATE MyTable
SET ReasonForVisit = "Initial Fit"
WHERE DateDiff("d", [InitialDate], [DateofSurgery]) < 30

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query with the following information
1. ID#
2. InitialDate
3. DateofSurgery
4. ReasonForVisit
5. Date diff (calculated) = InitalDate-DateofSurgery

I want to update fields in ReasonforVisit such that: If Datediff is
less
than 30, ReasonForVisit=Initial Fit.

I tried to do this using an update query, but I get a message saying
that
I
must use an updateable query.

What am i doing wrong?

Thank you,

Shukla

 

[Douglas J. Steele]

UPDATE tbl_NewPatientInfo
SET [Reason for referral] = "Initial Fit"
WHERE DateDiff("d", [Date 1st TEP],
DMin("NameOfDateField", "tbl_TEPRecords", "[MDACC#] = '" & [MDACC#] & "'"))
< 30


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you.

I am still having some trouble. The Date is from a second table called
'tbl_TEPRecords'. The Date of 1st TEP is from then first table,
'tbl_NewPatient'. They are related by a one-to-many relation, where
tbl_NewPatientInfo is on the one side and tbl_TEPRecords is on the many
side.
MDACC is the primary key in both tables and is a text field. How do I
incorporate this into the SQL?

Thank you,
Shukla

Presumably qry_FirstDate_TEPRecords is intended to return the first date
on
file for the patient.

You could try the following, but it may be slow if your table is large:

UPDATE tbl_NewPatientInfo
SET [Reason for referral] = "Initial Fit"
WHERE DateDiff("d", [Date 1st TEP],
DMin("NameOfDateField", "tbl_NewPatientInfo", "[MDACC#] = '" & [MDACC#] &
"'")) < 30

I've assumed that MDACC# is a text field.

Replace NameOfDateField with the appropriate field name.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you

I am still having trouble.
Below is my SQL:
UPDATE tbl_NewPatientInfo INNER JOIN qry_FirstDate_TEPRecords ON
tbl_NewPatientInfo.[MDACC#] = qry_FirstDate_TEPRecords.[MDACC#] SET
tbl_NewPatientInfo.[Reason for referral] = "Initial Fit"
WHERE (((DateDiff("d",[tbl_NewPatientInfo].[Date 1st
TEP],[MinOfDate]))<30));

I keep getting a message saying 'Operation must use an updateable
query'.
The table 'tbl_NewPatientInfo' is on the one side of a one-to-many
relationship. In the above query, it is linked to another qry that
selects
the first date for each ID# (I need to first day to make the datediff
calculation).

Is there something that I am doing incorrectly.

Thank you!

:

UPDATE MyTable
SET ReasonForVisit = "Initial Fit"
WHERE DateDiff("d", [InitialDate], [DateofSurgery]) < 30

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query with the following information
1. ID#
2. InitialDate
3. DateofSurgery
4. ReasonForVisit
5. Date diff (calculated) = InitalDate-DateofSurgery

I want to update fields in ReasonforVisit such that: If Datediff is
less
than 30, ReasonForVisit=Initial Fit.

I tried to do this using an update query, but I get a message saying
that
I
must use an updateable query.

What am i doing wrong?

Thank you,

Shukla

 

[Radhika]

Hello,
I attempted the SQL you sent me and I get a message saying that [MDACC#] is
a field that is present in multiple tables. How do I insert the table name
beside each [MDACC#] name in the SQL?

Thank you!
Radhika

UPDATE tbl_NewPatientInfo
SET [Reason for referral] = "Initial Fit"
WHERE DateDiff("d", [Date 1st TEP],
DMin("NameOfDateField", "tbl_TEPRecords", "[MDACC#] = '" & [MDACC#] & "'"))
< 30


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you.

I am still having some trouble. The Date is from a second table called
'tbl_TEPRecords'. The Date of 1st TEP is from then first table,
'tbl_NewPatient'. They are related by a one-to-many relation, where
tbl_NewPatientInfo is on the one side and tbl_TEPRecords is on the many
side.
MDACC is the primary key in both tables and is a text field. How do I
incorporate this into the SQL?

Thank you,
Shukla

Presumably qry_FirstDate_TEPRecords is intended to return the first date
on
file for the patient.

You could try the following, but it may be slow if your table is large:

UPDATE tbl_NewPatientInfo
SET [Reason for referral] = "Initial Fit"
WHERE DateDiff("d", [Date 1st TEP],
DMin("NameOfDateField", "tbl_NewPatientInfo", "[MDACC#] = '" & [MDACC#] &
"'")) < 30

I've assumed that MDACC# is a text field.

Replace NameOfDateField with the appropriate field name.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thank you

I am still having trouble.
Below is my SQL:
UPDATE tbl_NewPatientInfo INNER JOIN qry_FirstDate_TEPRecords ON
tbl_NewPatientInfo.[MDACC#] = qry_FirstDate_TEPRecords.[MDACC#] SET
tbl_NewPatientInfo.[Reason for referral] = "Initial Fit"
WHERE (((DateDiff("d",[tbl_NewPatientInfo].[Date 1st
TEP],[MinOfDate]))<30));

I keep getting a message saying 'Operation must use an updateable
query'.
The table 'tbl_NewPatientInfo' is on the one side of a one-to-many
relationship. In the above query, it is linked to another qry that
selects
the first date for each ID# (I need to first day to make the datediff
calculation).

Is there something that I am doing incorrectly.

Thank you!

:

UPDATE MyTable
SET ReasonForVisit = "Initial Fit"
WHERE DateDiff("d", [InitialDate], [DateofSurgery]) < 30

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query with the following information
1. ID#
2. InitialDate
3. DateofSurgery
4. ReasonForVisit
5. Date diff (calculated) = InitalDate-DateofSurgery

I want to update fields in ReasonforVisit such that: If Datediff is
less
than 30, ReasonForVisit=Initial Fit.

I tried to do this using an update query, but I get a message saying
that
I
must use an updateable query.

What am i doing wrong?

Thank you,

Shukla

 

[Douglas J. Steele]

I'm sorry, I can't see what would have caused that error message.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,
I attempted the SQL you sent me and I get a message saying that [MDACC#]
is
a field that is present in multiple tables. How do I insert the table name
beside each [MDACC#] name in the SQL?

Thank you!
Radhika

UPDATE tbl_NewPatientInfo
SET [Reason for referral] = "Initial Fit"
WHERE DateDiff("d", [Date 1st TEP],
DMin("NameOfDateField", "tbl_TEPRecords", "[MDACC#] = '" & [MDACC#] &
"'"))
< 30


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you.

I am still having some trouble. The Date is from a second table called
'tbl_TEPRecords'. The Date of 1st TEP is from then first table,
'tbl_NewPatient'. They are related by a one-to-many relation, where
tbl_NewPatientInfo is on the one side and tbl_TEPRecords is on the many
side.
MDACC is the primary key in both tables and is a text field. How do I
incorporate this into the SQL?

Thank you,
Shukla

:

Presumably qry_FirstDate_TEPRecords is intended to return the first
date
on
file for the patient.

You could try the following, but it may be slow if your table is
large:

UPDATE tbl_NewPatientInfo
SET [Reason for referral] = "Initial Fit"
WHERE DateDiff("d", [Date 1st TEP],
DMin("NameOfDateField", "tbl_NewPatientInfo", "[MDACC#] = '" &
[MDACC#] &
"'")) < 30

I've assumed that MDACC# is a text field.

Replace NameOfDateField with the appropriate field name.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thank you

I am still having trouble.
Below is my SQL:
UPDATE tbl_NewPatientInfo INNER JOIN qry_FirstDate_TEPRecords ON
tbl_NewPatientInfo.[MDACC#] = qry_FirstDate_TEPRecords.[MDACC#] SET
tbl_NewPatientInfo.[Reason for referral] = "Initial Fit"
WHERE (((DateDiff("d",[tbl_NewPatientInfo].[Date 1st
TEP],[MinOfDate]))<30));

I keep getting a message saying 'Operation must use an updateable
query'.
The table 'tbl_NewPatientInfo' is on the one side of a one-to-many
relationship. In the above query, it is linked to another qry that
selects
the first date for each ID# (I need to first day to make the
datediff
calculation).

Is there something that I am doing incorrectly.

Thank you!

:

UPDATE MyTable
SET ReasonForVisit = "Initial Fit"
WHERE DateDiff("d", [InitialDate], [DateofSurgery]) < 30

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query with the following information
1. ID#
2. InitialDate
3. DateofSurgery
4. ReasonForVisit
5. Date diff (calculated) = InitalDate-DateofSurgery

I want to update fields in ReasonforVisit such that: If Datediff
is
less
than 30, ReasonForVisit=Initial Fit.

I tried to do this using an update query, but I get a message
saying
that
I
must use an updateable query.

What am i doing wrong?

Thank you,

Shukla

 

[Graham Mandeno]

Hi Radhika

I think you have not used the SQL that Doug sent you, because it mentions
only one table.

The SQL you posted originally was:

UPDATE tbl_NewPatientInfo INNER JOIN qry_FirstDate_TEPRecords ON
tbl_NewPatientInfo.[MDACC#] = qry_FirstDate_TEPRecords.[MDACC#] SET
tbl_NewPatientInfo.[Reason for referral] = "Initial Fit"
WHERE (((DateDiff("d",[tbl_NewPatientInfo].[Date 1st
TEP],[MinOfDate]))<30));

Assuming qry_FirstDate_TEPRecords is a GROUP BY query including a Min
function, it will not be updatable. Including it in your update query then
renders the update query not updatable.

Are you sure you do not still have the INNER JOIN clause included in your
update query?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello,
I attempted the SQL you sent me and I get a message saying that [MDACC#]
is
a field that is present in multiple tables. How do I insert the table name
beside each [MDACC#] name in the SQL?

Thank you!
Radhika

UPDATE tbl_NewPatientInfo
SET [Reason for referral] = "Initial Fit"
WHERE DateDiff("d", [Date 1st TEP],
DMin("NameOfDateField", "tbl_TEPRecords", "[MDACC#] = '" & [MDACC#] &
"'"))
< 30


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you.

I am still having some trouble. The Date is from a second table called
'tbl_TEPRecords'. The Date of 1st TEP is from then first table,
'tbl_NewPatient'. They are related by a one-to-many relation, where
tbl_NewPatientInfo is on the one side and tbl_TEPRecords is on the many
side.
MDACC is the primary key in both tables and is a text field. How do I
incorporate this into the SQL?

Thank you,
Shukla

:

Presumably qry_FirstDate_TEPRecords is intended to return the first
date
on
file for the patient.

You could try the following, but it may be slow if your table is
large:

UPDATE tbl_NewPatientInfo
SET [Reason for referral] = "Initial Fit"
WHERE DateDiff("d", [Date 1st TEP],
DMin("NameOfDateField", "tbl_NewPatientInfo", "[MDACC#] = '" &
[MDACC#] &
"'")) < 30

I've assumed that MDACC# is a text field.

Replace NameOfDateField with the appropriate field name.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thank you

I am still having trouble.
Below is my SQL:
UPDATE tbl_NewPatientInfo INNER JOIN qry_FirstDate_TEPRecords ON
tbl_NewPatientInfo.[MDACC#] = qry_FirstDate_TEPRecords.[MDACC#] SET
tbl_NewPatientInfo.[Reason for referral] = "Initial Fit"
WHERE (((DateDiff("d",[tbl_NewPatientInfo].[Date 1st
TEP],[MinOfDate]))<30));

I keep getting a message saying 'Operation must use an updateable
query'.
The table 'tbl_NewPatientInfo' is on the one side of a one-to-many
relationship. In the above query, it is linked to another qry that
selects
the first date for each ID# (I need to first day to make the
datediff
calculation).

Is there something that I am doing incorrectly.

Thank you!

:

UPDATE MyTable
SET ReasonForVisit = "Initial Fit"
WHERE DateDiff("d", [InitialDate], [DateofSurgery]) < 30

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query with the following information
1. ID#
2. InitialDate
3. DateofSurgery
4. ReasonForVisit
5. Date diff (calculated) = InitalDate-DateofSurgery

I want to update fields in ReasonforVisit such that: If Datediff
is
less
than 30, ReasonForVisit=Initial Fit.

I tried to do this using an update query, but I get a message
saying
that
I
must use an updateable query.

What am i doing wrong?

Thank you,

Shukla