Updateable Query

F

FBxiii

Hi.

I have a form containing a sub-form. When a 'Table' is selected from the
form, the information is displayed on the sub-form from the selected table.
The sub form allows the user to update a couple of fields with a reject code
and comments.

I have modified the query slightly to include a Complete_Date from a
'tracking' table, linked by a field called MPRN. The query has now become
un-updateable!

If I remove the Complete_Date field I can update the records again.

Ive used DISTINCTROW which normally seems to fix this type of problem but it
hasnt worked.

My SQL is:

SELECT DISTINCTROW A.Record_ID, A.MPRN,
Format((DateSerial((Right(A.Date_of_Transfer,4)),(Mid(A.Date_of_Transfer,3,2)),(Left(A.Date_of_Transfer,2)))),'dd/mm/yyyy')
AS [Date], A.Proposed_Read, A.Actual_Estimate, A.Reject_Code, A.Comments,
tblSAR_Tracker.Complete_Date FROM *TableName* AS A INNER JOIN tblSAR_Tracker
ON A.MPRN = tblSAR_Tracker.MPR WHERE (((A.Reject_Code)='0') AND
((left(tblSAR_Tracker.File_Name,Len(tblSAR_Tracker.File_Name)-4))='*TableName*')) ORDER BY A.Record_ID;

Does anyone have any ideas how to make this updateable?

Cheers,
Steve.
 
D

Duane Hookom

Is this a relationship between a primary and foreign key?
*TableName*.MPRN = tblSAR_Tracker.MPR
 
F

FBxiii

Thanks for the reply.

The *TableName* tables do not have a primary key (each table is created by
the App as a file is imported).

tblSAR_Tracker has a Primary key but it is not the MPR field.

There is a large amount of Tables within the DB so setting up relationships
between the tblSAR_Tracker table would not be feasable in this case.

Would indexing help?

Cheers,
Steve.


Duane Hookom said:
Is this a relationship between a primary and foreign key?
*TableName*.MPRN = tblSAR_Tracker.MPR
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


FBxiii said:
Hi.

I have a form containing a sub-form. When a 'Table' is selected from the
form, the information is displayed on the sub-form from the selected table.
The sub form allows the user to update a couple of fields with a reject code
and comments.

I have modified the query slightly to include a Complete_Date from a
'tracking' table, linked by a field called MPRN. The query has now become
un-updateable!

If I remove the Complete_Date field I can update the records again.

Ive used DISTINCTROW which normally seems to fix this type of problem but it
hasnt worked.

My SQL is:

SELECT DISTINCTROW A.Record_ID, A.MPRN,
Format((DateSerial((Right(A.Date_of_Transfer,4)),(Mid(A.Date_of_Transfer,3,2)),(Left(A.Date_of_Transfer,2)))),'dd/mm/yyyy')
AS [Date], A.Proposed_Read, A.Actual_Estimate, A.Reject_Code, A.Comments,
tblSAR_Tracker.Complete_Date FROM *TableName* AS A INNER JOIN tblSAR_Tracker
ON A.MPRN = tblSAR_Tracker.MPR WHERE (((A.Reject_Code)='0') AND
((left(tblSAR_Tracker.File_Name,Len(tblSAR_Tracker.File_Name)-4))='*TableName*')) ORDER BY A.Record_ID;

Does anyone have any ideas how to make this updateable?

Cheers,
Steve.
Click to expand...
Click to expand...
 
D

Duane Hookom

Can you make the MPR field a unique or primary key? If not, I am not aware of
how the records could be updateable.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


FBxiii said:
Thanks for the reply.

The *TableName* tables do not have a primary key (each table is created by
the App as a file is imported).

tblSAR_Tracker has a Primary key but it is not the MPR field.

There is a large amount of Tables within the DB so setting up relationships
between the tblSAR_Tracker table would not be feasable in this case.

Would indexing help?

Cheers,
Steve.


Duane Hookom said:
Is this a relationship between a primary and foreign key?
*TableName*.MPRN = tblSAR_Tracker.MPR
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


FBxiii said:
Hi.

I have a form containing a sub-form. When a 'Table' is selected from the
form, the information is displayed on the sub-form from the selected table.
The sub form allows the user to update a couple of fields with a reject code
and comments.

I have modified the query slightly to include a Complete_Date from a
'tracking' table, linked by a field called MPRN. The query has now become
un-updateable!

If I remove the Complete_Date field I can update the records again.

Ive used DISTINCTROW which normally seems to fix this type of problem but it
hasnt worked.

My SQL is:

SELECT DISTINCTROW A.Record_ID, A.MPRN,
Format((DateSerial((Right(A.Date_of_Transfer,4)),(Mid(A.Date_of_Transfer,3,2)),(Left(A.Date_of_Transfer,2)))),'dd/mm/yyyy')
AS [Date], A.Proposed_Read, A.Actual_Estimate, A.Reject_Code, A.Comments,
tblSAR_Tracker.Complete_Date FROM *TableName* AS A INNER JOIN tblSAR_Tracker
ON A.MPRN = tblSAR_Tracker.MPR WHERE (((A.Reject_Code)='0') AND
((left(tblSAR_Tracker.File_Name,Len(tblSAR_Tracker.File_Name)-4))='*TableName*')) ORDER BY A.Record_ID;

Does anyone have any ideas how to make this updateable?

Cheers,
Steve.
Click to expand...
Click to expand...
Click to expand...
 
F

FBxiii

It would be possible to make the MPR field the primary key if I used another
field aswell.

I will give it a try and let you know.

Thanks,
Steve.

Duane Hookom said:
Can you make the MPR field a unique or primary key? If not, I am not aware of
how the records could be updateable.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


FBxiii said:
Thanks for the reply.

The *TableName* tables do not have a primary key (each table is created by
the App as a file is imported).

tblSAR_Tracker has a Primary key but it is not the MPR field.

There is a large amount of Tables within the DB so setting up relationships
between the tblSAR_Tracker table would not be feasable in this case.

Would indexing help?

Cheers,
Steve.


Duane Hookom said:
Is this a relationship between a primary and foreign key?
*TableName*.MPRN = tblSAR_Tracker.MPR
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Hi.

I have a form containing a sub-form. When a 'Table' is selected from the
form, the information is displayed on the sub-form from the selected table.
The sub form allows the user to update a couple of fields with a reject code
and comments.

I have modified the query slightly to include a Complete_Date from a
'tracking' table, linked by a field called MPRN. The query has now become
un-updateable!

If I remove the Complete_Date field I can update the records again.

Ive used DISTINCTROW which normally seems to fix this type of problem but it
hasnt worked.

My SQL is:

SELECT DISTINCTROW A.Record_ID, A.MPRN,
Format((DateSerial((Right(A.Date_of_Transfer,4)),(Mid(A.Date_of_Transfer,3,2)),(Left(A.Date_of_Transfer,2)))),'dd/mm/yyyy')
AS [Date], A.Proposed_Read, A.Actual_Estimate, A.Reject_Code, A.Comments,
tblSAR_Tracker.Complete_Date FROM *TableName* AS A INNER JOIN tblSAR_Tracker
ON A.MPRN = tblSAR_Tracker.MPR WHERE (((A.Reject_Code)='0') AND
((left(tblSAR_Tracker.File_Name,Len(tblSAR_Tracker.File_Name)-4))='*TableName*')) ORDER BY A.Record_ID;

Does anyone have any ideas how to make this updateable?

Cheers,
Steve.
Click to expand...
Click to expand...
Click to expand...
 
F

FBxiii

OK, Ive looked and it will not be possible to use MPR as a Unique/Primary key
as it is possible to have multiple MPR's within 1 file (MPR is our companies
reference number).

I will have to use an update query instead of trying to update the recordset
directly.

Thanks for the help anyway,
Steve.


Duane Hookom said:
Can you make the MPR field a unique or primary key? If not, I am not aware of
how the records could be updateable.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


FBxiii said:
Thanks for the reply.

The *TableName* tables do not have a primary key (each table is created by
the App as a file is imported).

tblSAR_Tracker has a Primary key but it is not the MPR field.

There is a large amount of Tables within the DB so setting up relationships
between the tblSAR_Tracker table would not be feasable in this case.

Would indexing help?

Cheers,
Steve.


Duane Hookom said:
Is this a relationship between a primary and foreign key?
*TableName*.MPRN = tblSAR_Tracker.MPR
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Hi.

I have a form containing a sub-form. When a 'Table' is selected from the
form, the information is displayed on the sub-form from the selected table.
The sub form allows the user to update a couple of fields with a reject code
and comments.

I have modified the query slightly to include a Complete_Date from a
'tracking' table, linked by a field called MPRN. The query has now become
un-updateable!

If I remove the Complete_Date field I can update the records again.

Ive used DISTINCTROW which normally seems to fix this type of problem but it
hasnt worked.

My SQL is:

SELECT DISTINCTROW A.Record_ID, A.MPRN,
Format((DateSerial((Right(A.Date_of_Transfer,4)),(Mid(A.Date_of_Transfer,3,2)),(Left(A.Date_of_Transfer,2)))),'dd/mm/yyyy')
AS [Date], A.Proposed_Read, A.Actual_Estimate, A.Reject_Code, A.Comments,
tblSAR_Tracker.Complete_Date FROM *TableName* AS A INNER JOIN tblSAR_Tracker
ON A.MPRN = tblSAR_Tracker.MPR WHERE (((A.Reject_Code)='0') AND
((left(tblSAR_Tracker.File_Name,Len(tblSAR_Tracker.File_Name)-4))='*TableName*')) ORDER BY A.Record_ID;

Does anyone have any ideas how to make this updateable?

Cheers,
Steve.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

If the MPR field is not unique then how does this join know which MPR value
to join? If there are duplicate MPR, then this query might return multiple
records from tblSAR_Tracker for a single record in *TableName*

FROM *TableName* AS A INNER JOIN tblSAR_Tracker
ON A.MPRN = tblSAR_Tracker.MPR

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


FBxiii said:
OK, Ive looked and it will not be possible to use MPR as a Unique/Primary key
as it is possible to have multiple MPR's within 1 file (MPR is our companies
reference number).

I will have to use an update query instead of trying to update the recordset
directly.

Thanks for the help anyway,
Steve.


Duane Hookom said:
Can you make the MPR field a unique or primary key? If not, I am not aware of
how the records could be updateable.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


FBxiii said:
Thanks for the reply.

The *TableName* tables do not have a primary key (each table is created by
the App as a file is imported).

tblSAR_Tracker has a Primary key but it is not the MPR field.

There is a large amount of Tables within the DB so setting up relationships
between the tblSAR_Tracker table would not be feasable in this case.

Would indexing help?

Cheers,
Steve.


:

Is this a relationship between a primary and foreign key?
*TableName*.MPRN = tblSAR_Tracker.MPR
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Hi.

I have a form containing a sub-form. When a 'Table' is selected from the
form, the information is displayed on the sub-form from the selected table.
The sub form allows the user to update a couple of fields with a reject code
and comments.

I have modified the query slightly to include a Complete_Date from a
'tracking' table, linked by a field called MPRN. The query has now become
un-updateable!

If I remove the Complete_Date field I can update the records again.

Ive used DISTINCTROW which normally seems to fix this type of problem but it
hasnt worked.

My SQL is:

SELECT DISTINCTROW A.Record_ID, A.MPRN,
Format((DateSerial((Right(A.Date_of_Transfer,4)),(Mid(A.Date_of_Transfer,3,2)),(Left(A.Date_of_Transfer,2)))),'dd/mm/yyyy')
AS [Date], A.Proposed_Read, A.Actual_Estimate, A.Reject_Code, A.Comments,
tblSAR_Tracker.Complete_Date FROM *TableName* AS A INNER JOIN tblSAR_Tracker
ON A.MPRN = tblSAR_Tracker.MPR WHERE (((A.Reject_Code)='0') AND
((left(tblSAR_Tracker.File_Name,Len(tblSAR_Tracker.File_Name)-4))='*TableName*')) ORDER BY A.Record_ID;

Does anyone have any ideas how to make this updateable?

Cheers,
Steve.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

What makes a query updateable? 2
Updateable querie 2
updateable table errors...help 1
Query non-updateable when composite key field used. 1
RecordsetNot Updateable 22
Linked Query not Updateable 2
Help creating updateable query 6
Operation must use updateable query problem 9

Top