Update Query based on another query.

[Ihussein]

Hello,
I have an update query which is updating table based on another query, in the basic query I have two columns which are Dlooking up integer numbers form another table and it's working fine, but will updating the table the update query is not working for these two columns but returning zero's.
BasicQry
ID Name HH INDV
1 Smith 3 10


2 Sarah 5 17

UpdateQry
'Dlooking up data from BasicQry and it's working.

TblPerDetails
' Data after running update query, changes the integer numbers to zero's.
ID Name HH INDV
1 Smith 0 0
2 Sarah 0 0

Any support please.
Regards

[CJ_London]

really need to see the sql to both queries to suggest a solution

[Ihussein]

All right Here we go,
Dlookup of the BasicQry under column HH
HH: DLookUp("[HouseHold]","TlbLocation","[LocationID]=" & [LocationID_FK])
-------------------------------------------------------------------------
Dlookup of the UpdateQry
DLookUp("[HH]","QryPlanPerLocationAsc")

Just realized that when u switch to datasheet view the UpdateQry in showing zero. them may be there is some mistake in the updateto Expression?
hope it clarifies.

[CJ_London]

sorry, it doesn't clarify - you have shown one bit of the query in the query builder, not the full SQL

with regards switching to datasheet view, it is effectively a select query view - so you will see what is in the field at the moment, not what you want to update it to

[Ihussein]

Sorry for inconvinience made here is sql
BasicQry
UPDATE TblPlanPerLocation SET TblPlanPerLocation.PlanPerLocationID = DLookUp("[PlanPerLocationID]","QryPlanPerLocationAsc"), TblPlanPerLocation.PlanID_FK = DLookUp("[PlanID_FK]","QryPlanPerLocationAsc"), TblPlanPerLocation.AreaID_FK = DLookUp("[AreaID_FK]","QryPlanPerLocationAsc"), TblPlanPerLocation.LocationID_FK = DLookUp("[LocationID_FK]","QryPlanPerLocationAsc"), TblPlanPerLocation.StartDate = DLookUp("[StartDate]","QryPlanPerLocationAsc"), TblPlanPerLocation.EndDate = DLookUp("[EndDate]","QryPlanPerLocationAsc"), TblPlanPerLocation.HH = DLookUp([HH],"QryPlanPerLocationAsc"), TblPlanPerLocation.INDV = DLookUp("[INDV]","QryPlanPerLocationAsc"), TblPlanPerLocation.[Note] = DLookUp("[Note]","QryPlanPerLocationAsc");


UpdateQry
UPDATE TblPlanPerLocation SET TblPlanPerLocation.PlanPerLocationID = DLookUp("[PlanPerLocationID]","QryPlanPerLocationAsc"), TblPlanPerLocation.PlanID_FK = DLookUp("[PlanID_FK]","QryPlanPerLocationAsc"), TblPlanPerLocation.AreaID_FK = DLookUp("[AreaID_FK]","QryPlanPerLocationAsc"), TblPlanPerLocation.LocationID_FK = DLookUp("[LocationID_FK]","QryPlanPerLocationAsc"), TblPlanPerLocation.StartDate = DLookUp("[StartDate]","QryPlanPerLocationAsc"), TblPlanPerLocation.EndDate = DLookUp("[EndDate]","QryPlanPerLocationAsc"), TblPlanPerLocation.HH = DLookUp([HH],"QryPlanPerLocationAsc"), TblPlanPerLocation.INDV = DLookUp("[INDV]","QryPlanPerLocationAsc"), TblPlanPerLocation.[Note] = DLookUp("[Note]","QryPlanPerLocationAsc");

[CJ_London]

both queries look the same to me

[Ihussein]

I do n't know what is happening, hope I'm not bothering u. it seems that I double copied same sql of UpdateQry, here are the sql's of both the queries.Thanks for understanding

BasicQry
SELECT TblPlanPerLocation.PlanPerLocationID, TblPlanPerLocation.AreaID_FK, TblPlanPerLocation.LocationID_FK, TblPlanPerLocation.StartDate, TblPlanPerLocation.EndDate, DLookUp("[HouseHold]","TlbLocation","[LocationID]=" & [LocationID_FK]) AS HH, DLookUp("[Individual]","TlbLocation","[LocationID]=" & [LocationID_FK]) AS INDV, TblPlanPerLocation.Note
FROM TblPlanPerLocation;
---------------------------------------------------------------------------------------------------------------------------------
UpdateQry
UPDATE TblPlanPerLocation SET TblPlanPerLocation.PlanPerLocationID = DLookUp("[PlanPerLocationID]","QryPlanPerLocationAsc"), TblPlanPerLocation.PlanID_FK = DLookUp("[PlanID_FK]","QryPlanPerLocationAsc"), TblPlanPerLocation.AreaID_FK = DLookUp("[AreaID_FK]","QryPlanPerLocationAsc"), TblPlanPerLocation.LocationID_FK = DLookUp("[LocationID_FK]","QryPlanPerLocationAsc"), TblPlanPerLocation.StartDate = DLookUp("[StartDate]","QryPlanPerLocationAsc"), TblPlanPerLocation.EndDate = DLookUp("[EndDate]","QryPlanPerLocationAsc"), TblPlanPerLocation.HH = DLookUp([HH],"QryPlanPerLocationAsc"), TblPlanPerLocation.INDV = DLookUp("[INDV]","QryPlanPerLocationAsc"), TblPlanPerLocation.[Note] = DLookUp("[Note]","QryPlanPerLocationAsc");

[CJ_London]

1. if your basic query is the qryplanperlocactionasc, then you don't need it - all it is doing is renaming some fields.
2. you need to explain what you are trying to achieve with the dlookup's. I think all you want to do is assign a value to a field from another field in the same record, however as you have it at the moment it will be populated with values from fields in the 'first' record of the table - where 'first' is indeterminate - databases have no concept of first, last, next, previous without some sort of order.

[CJ_London]

sorry rereading your code, ignore my previous post

1. you still don't need the basic query, it can be part of your append query
2. you still need to explain what you are trying to achieve with the dlookups
3. you also need to explain why you want to add the descriptions you are looking up to your table - you would normally just look them up when required

[Ihussein]

Thank u so much, u r fully right, I can simply make it apart of append query and to be looked up when required.
Appreciated