S
steve_doc
Hi all
I hope that some kind soul is able to point me in the right direction.
Currently working on my Access project and have hit a small stumbling block.
What I am attempting to do is to update records in 1 table based on criteria
in another table. So onto the details.
Table 1 (tblBays - the table where the records will be updated)
Table 2 (tblWaitList - the table that provides the criteria)
Details are held in the waiting list till I am able to offer a parking bay
to the person in question. When a bay becomes available, I flag one of the
fields (Offer checkbox), which triggers an offer letter to be produced as a
report.
If the person accepts the offer, I check another field (accepted) which
triggers the update process. This is where the problem lies.
The update process is macro based and is made up of the following steps:
1 - Append query to post persons details to tblTenants
2 - Append query to post vehicle details to tblVehicles
3 - Update query - assign the persons ID to the offered parking Bay (see
details below)
4 - Append query - add any Notes to tblNotes
Details of Update Query
tblBays is a complete listing of every parking bay that I manage, even if
the bay is empty, the following is a list of fields in this table:
Bay_ID, Text, Primary Key, Indexed (No Duplicates), Sample AVON01
T_ID, Number (long integer), Tenant Identifier
S_ID, Number (long integer) - number representing what Scheme bay belongs to
BikeSpace, Yes/No - indicates size of bay
The following SQL statement provides the record(s) that I want to update:
SELECT tblWaitList.T_ID, tblWaitList.OfferedBay, tblWaitList.Accepted
FROM tblWaitList
WHERE (((tblWaitList.Accepted)=-1));
Above Result:
14055, KENL02, Yes
As the table to be updated already has the Field – Bay_ID, it is only the
T_ID field that needs to be updated.
I hope I have articulated this correctly and clearly.
Any ideas on how to do this will be greatly appreciated
I hope that some kind soul is able to point me in the right direction.
Currently working on my Access project and have hit a small stumbling block.
What I am attempting to do is to update records in 1 table based on criteria
in another table. So onto the details.
Table 1 (tblBays - the table where the records will be updated)
Table 2 (tblWaitList - the table that provides the criteria)
Details are held in the waiting list till I am able to offer a parking bay
to the person in question. When a bay becomes available, I flag one of the
fields (Offer checkbox), which triggers an offer letter to be produced as a
report.
If the person accepts the offer, I check another field (accepted) which
triggers the update process. This is where the problem lies.
The update process is macro based and is made up of the following steps:
1 - Append query to post persons details to tblTenants
2 - Append query to post vehicle details to tblVehicles
3 - Update query - assign the persons ID to the offered parking Bay (see
details below)
4 - Append query - add any Notes to tblNotes
Details of Update Query
tblBays is a complete listing of every parking bay that I manage, even if
the bay is empty, the following is a list of fields in this table:
Bay_ID, Text, Primary Key, Indexed (No Duplicates), Sample AVON01
T_ID, Number (long integer), Tenant Identifier
S_ID, Number (long integer) - number representing what Scheme bay belongs to
BikeSpace, Yes/No - indicates size of bay
The following SQL statement provides the record(s) that I want to update:
SELECT tblWaitList.T_ID, tblWaitList.OfferedBay, tblWaitList.Accepted
FROM tblWaitList
WHERE (((tblWaitList.Accepted)=-1));
Above Result:
14055, KENL02, Yes
As the table to be updated already has the Field – Bay_ID, it is only the
T_ID field that needs to be updated.
I hope I have articulated this correctly and clearly.
Any ideas on how to do this will be greatly appreciated