Updateable Query

  • Thread starter Thread starter FBxiii
  • Start date Start date
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.
 
Is this a relationship between a primary and foreign key?
*TableName*.MPRN = tblSAR_Tracker.MPR
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top