Solved Help with SQL query

[nachumy]

I want Total Lessons to be updated only when the ID's Match

how do I solve this?

 

[arnelgp]

Update PrivateContacts Set [Total Lessons] = Nz(DLookup("[Total Lessons]", "PrivateContacts", "ID=" & [ID]), 0) + 1

 

[nachumy]

Update PrivateContacts Set [Total Lessons] = Nz(DLookup("[Total Lessons]", "PrivateContacts", "ID=" & [ID]), 0) + 1

that made the field [total lessons] increase by 1in all of the records, not just the one that I want.

can you explain what does this supposed to do?

 

[arnelgp]

so you only want 1 ID to update?

Update PrivateContacts Set [Total Lessons] = Nz([Total Lessons], 0) + 1 Where [ID] = [Enter ID number];

// i think you already have this in post#1. just type the id number to update.

 

[nachumy]

so you only want 1 ID to update?

Update PrivateContacts Set [Total Lessons] = Nz([Total Lessons], 0) + 1 Where [ID] = [Enter ID number];

This will work but would require me to enter the ID manually.
I want it to be automated, so I made another query which finds the ID I want (see [ID of last private records]![ID of last record]).
The value that this returns is what I want to put in your suggestion as [Enter ID Number].

how would I do that "automatically"?

 

[arnelgp]

is ID an Autonumber, if not then you need to Add an Autonumber field to your table.
then your query will be.

i think it is better to show your table, rather than guessing what you have.

if you continue on the same path, you will also update the "Last record" with same ID.

 

[arnelgp]

Update PrivateContacts Set [Total Lessons] = Nz([Total Lessons], 0) + 1 Where [ID] =DLast("ID", "PrivateContacts")

 

[nachumy]

PrivateContacts!ID is an autonumber, [ID of last private records]![ID of last record] is not an autonumber

does this help?

Total lesson field in PrivateContacts:

Query ID of last private records

 

[arnelgp]

how do you compare Autonumber with non-autonumber?
does not make sense to me.

Update PrivateContacts Set [Total Lessons] = Nz([Total Lessons], 0) + 1 Where [ID] = DLookup("[ID of last record]","[ID of last private records]")

 

[nachumy]

how do you compare Autonumber with non-autonumber?
does not make sense to me.

Update PrivateContacts Set [Total Lessons] = Nz([Total Lessons], 0) + 1 Where [ID] = DLookup("[ID of last record]","[ID of last private records]")

UPDATE PrivateContacts SET PrivateContacts.[Total Lessons] = PrivateContacts.[Total Lessons]+1
WHERE (((PrivateContacts.ID) In
(SELECT Last(PrivateRecords.[Name Of Student]) AS ContactID
FROM PrivateRecords
ORDER BY Last(PrivateRecords.ID))));


That did what I wanted.. Thanks for your help!