Modifying Values in Query

[matzos]

Not sure if I can make this work, or even if I can communicate exactly what it is that I need to do but here we go:

I have a continuous form to list product details that a given user has access to modify. However, the query that I have won't let me update the couple fields I need to. I understand why it won't let me, since the query involves the linking of more than one table, but I'm not sure if there is a way around that.

Any thoughts?

 

[sfreeman]

If I understand you, you answered your own question. Add that table to your quesry and link it.

Am I missing something?

 

[JonFer]

Please post the SQL for your query and the underlying table structures including primary keys.

 

[matzos]

Here is the SQL for the query I'm working with:

SELECT tblSysLists.ListName, tblLiveProjDetail.MoStart, tblLiveProjDetail.ActFS, tblLiveProjDetail.ActAdj
FROM (tblListAssign INNER JOIN tblLiveProjDetail ON tblListAssign.ListNum = tblLiveProjDetail.ListNum) INNER JOIN tblSysLists ON tblLiveProjDetail.ListNum = tblSysLists.ListNum
ORDER BY tblSysLists.ListName, tblLiveProjDetail.MoStart;

I need to be able to modify the values in the ActFS and ActAdj fields in tblLiveProjDetail but the query won't let me. All 3 tables are linked by the index of ListNum.

Thanks!

 

[PHV]

Have you tried to add all the keys in your select list ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[matzos]

I have and haven't had any luck. HEre is the query with all of the keys:

SELECT tblSysLists.ListName, tblLiveProjDetail.MoStart, tblLiveProjDetail.ActFS, tblLiveProjDetail.ActAdj, tblLiveProjDetail.DetailID, tblSysLists.ListNum, tblListAssign.AssignID
FROM (tblListAssign INNER JOIN tblLiveProjDetail ON tblListAssign.ListNum = tblLiveProjDetail.ListNum) INNER JOIN tblSysLists ON tblLiveProjDetail.ListNum = tblSysLists.ListNum
WHERE (((tblLiveProjDetail.MoStart)=#1/1/2004#))
ORDER BY tblSysLists.ListName, tblLiveProjDetail.MoStart;

Could you give me an example using my query to make sure I did it correctly?

 

[matzos]

NOTE: I just noticed that I used the word "update" when describing what I want to do. I'm not using an Update query, I need to modify the field values in the query result. I can do it if I use a single table in the FROM statement but not if I link that tables.

 

[JonFer]

Do the tables all have primary keys defined on them?

What is the relationship between tblSysLists and tblListAssign? Is there one record per ListNum in each?

 

[matzos]

All tables have primary keys defined as follows:

tblSysLists - ListNum
tblLiveProjDetail - DetailID
tblListAssign - AssignID

There will be multiple ListNum references in tblLiveProjDetail & tblListAssign since there could be more than one user that has access to a given list and more than one ProjDetail record for a given ListNum.

 

[PHV]

Have you tried to add tblListAssign.ListNum and tblLiveProjDetail.ListNum to the select list ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

I've been testing with some similar tables. Use the Index button on the Table Design toolbar to add these composite indexes.

1. Unique index on DetailID + ListNum for tblLiveProjDetail
2. Unique index on AssignID + ListNum for tblListAssign

That should work then. I think Access wants ListNum to be part of a unique or primary index before it will allow updates. The fields don't need to be in the Select list. Be careful to not allow new records.

 

[matzos]

Thank you. I've been working with your suggestions for days now but nothing has seemed to work. JonFer, I tried your last suggestion but ListNum can not be unique in those tables because each list will have multiple users and multiple projections. Any other suggestions would be welcome, but for now I have to create some sort of long-winded work around just to bandage the problem for now.

Thanks again!

 

[JonFer]

matzos - Make a unique index for the tables that includes ListNum however you can - you know your data. One option is to add an Autonumber field and include that in the index with ListNum.


John

http://www.interautomateconsulting.com