Update two tables from one form

[dg.smith]

I'm sure this question has been posed before, but I looked and didn't see
it, and didn't understand what I saw on the 'Net.

I don't know SQL, which is the problem, so hoping someone will take mercy on
me.

Can someone tell me the correct syntax for SQL to update a field in one
table from a form that is adding a record in a second table.

Basically, I have my first table, named Members, with these fields: ID,
Category, LastName, FirstName, Company, Address, City, State, Zip,
MemberSince, LastPaid, PayDues

2nd table, named Deposits, with these fields: ID, Member (keyed on LastName
in Members table), Category (from Member table), DepositDate, DepositAmount,
Reason.

When I enter a deposit, I want the DepositDate to update in table Members
the LastPaid field.

Mercy?

 

[Allen Browne]

Yes, this kind of thing does get asked often, and the answer it, Don't store
LastPaid in the Members table.

Firstly, you need a MemberID field in the Deposits table, instead of keying
on LastName. Otherwise too many Cooks (or any other name) will spoil your
deposits.

Then, on the member's form, you can display the LastPaid date with a text
box that has its ControlSource set to:
=DMax("DepositDate", "Desposits", "MemberID = " & Nz([MemberID])

For other techniques on picking up the field values from a related record,
see:
http://www.mvps.org/access/queries/qry0020.htm

 

[dg.smith]

Thanks, but that was ultra Greek to me.. LOL... I have NO business doing
this stuff. However, I'm keying off a different field just because of the
last name problem.

However, I was able to put enough info together to do what I wanted, except
I can't get the two tables to see the same category info. So, it will either
update 0 records or all records.

This is what I have:

DoCmd.RunSQL ("Update Members Set LastPaid = DepositDate WHERE Category =
MemberCategory")

If if place actual data in place for MemberCategory, it'll actually update.
But it doesn't seem to equate the data in the two fields so it can find the
correct record to update.

Yes, this kind of thing does get asked often, and the answer it, Don't store
LastPaid in the Members table.

Firstly, you need a MemberID field in the Deposits table, instead of keying
on LastName. Otherwise too many Cooks (or any other name) will spoil your
deposits.

Then, on the member's form, you can display the LastPaid date with a text
box that has its ControlSource set to:
=DMax("DepositDate", "Desposits", "MemberID = " & Nz([MemberID])

For other techniques on picking up the field values from a related record,
see:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm sure this question has been posed before, but I looked and didn't see
it, and didn't understand what I saw on the 'Net.

I don't know SQL, which is the problem, so hoping someone will take mercy
on
me.

Can someone tell me the correct syntax for SQL to update a field in one
table from a form that is adding a record in a second table.

Basically, I have my first table, named Members, with these fields: ID,
Category, LastName, FirstName, Company, Address, City, State, Zip,
MemberSince, LastPaid, PayDues

2nd table, named Deposits, with these fields: ID, Member (keyed on
LastName
in Members table), Category (from Member table), DepositDate,
DepositAmount,
Reason.

When I enter a deposit, I want the DepositDate to update in table Members
the LastPaid field.

Mercy?

 

[Allen Browne]

DG, what I'm trying to say is that you are creating a great deal of
unnecessary work for yourself by storing this LastPaid field in the client
table.

I'm not where what event you are using to run this query, but have you
considered whether it will wrongly update the LastPaid field if you later
edit one of the old records? What about if the DepositDate is changed? Does
it then update the correct value for the LastPaid field? Even if another
deposit is more recent after this change? And what if a deposit is deleted?
Does it correctly find the most recent deposit date, and replace it with
that one if it was the most recent one that was deteted?

These are practical examples of one of why one of the basic rules of data
normalization says you should not store dependent data. Rather you need to
ask Access to look up the most recent deposit date for the client when you
need it. That way the data can never be wrong.

Sure, there are times when we break the rules and store the data, but that
is the hard way, and you are taking on a significant maintenace load, so we
don't do that unless we have to.

It may help to know in what context you need to know the LastPaid date. Is
this to display on a report? On a form? To use as the basis of a calculation
in a query?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, but that was ultra Greek to me.. LOL... I have NO business doing
this stuff. However, I'm keying off a different field just because of the
last name problem.

However, I was able to put enough info together to do what I wanted,
except
I can't get the two tables to see the same category info. So, it will
either
update 0 records or all records.

This is what I have:

DoCmd.RunSQL ("Update Members Set LastPaid = DepositDate WHERE Category =
MemberCategory")

If if place actual data in place for MemberCategory, it'll actually
update.
But it doesn't seem to equate the data in the two fields so it can find
the
correct record to update.

Yes, this kind of thing does get asked often, and the answer it, Don't store
LastPaid in the Members table.

Firstly, you need a MemberID field in the Deposits table, instead of keying
on LastName. Otherwise too many Cooks (or any other name) will spoil your
deposits.

Then, on the member's form, you can display the LastPaid date with a text
box that has its ControlSource set to:
=DMax("DepositDate", "Desposits", "MemberID = " & Nz([MemberID])

For other techniques on picking up the field values from a related
record,
see:
http://www.mvps.org/access/queries/qry0020.htm

I'm sure this question has been posed before, but I looked and didn't see
it, and didn't understand what I saw on the 'Net.

I don't know SQL, which is the problem, so hoping someone will take mercy
on
me.

Can someone tell me the correct syntax for SQL to update a field in one
table from a form that is adding a record in a second table.

Basically, I have my first table, named Members, with these fields: ID,
Category, LastName, FirstName, Company, Address, City, State, Zip,
MemberSince, LastPaid, PayDues

2nd table, named Deposits, with these fields: ID, Member (keyed on
LastName
in Members table), Category (from Member table), DepositDate,
DepositAmount,
Reason.

When I enter a deposit, I want the DepositDate to update in table Members
the LastPaid field.

Mercy?

 

[dg.smith]

The LastPaid date would be placed on a report. I'm trying to figure how to
make that work.

DG, what I'm trying to say is that you are creating a great deal of
unnecessary work for yourself by storing this LastPaid field in the client
table.

I'm not where what event you are using to run this query, but have you
considered whether it will wrongly update the LastPaid field if you later
edit one of the old records? What about if the DepositDate is changed? Does
it then update the correct value for the LastPaid field? Even if another
deposit is more recent after this change? And what if a deposit is deleted?
Does it correctly find the most recent deposit date, and replace it with
that one if it was the most recent one that was deteted?

These are practical examples of one of why one of the basic rules of data
normalization says you should not store dependent data. Rather you need to
ask Access to look up the most recent deposit date for the client when you
need it. That way the data can never be wrong.

Sure, there are times when we break the rules and store the data, but that
is the hard way, and you are taking on a significant maintenace load, so we
don't do that unless we have to.

It may help to know in what context you need to know the LastPaid date. Is
this to display on a report? On a form? To use as the basis of a calculation
in a query?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, but that was ultra Greek to me.. LOL... I have NO business doing
this stuff. However, I'm keying off a different field just because of the
last name problem.

However, I was able to put enough info together to do what I wanted,
except
I can't get the two tables to see the same category info. So, it will
either
update 0 records or all records.

This is what I have:

DoCmd.RunSQL ("Update Members Set LastPaid = DepositDate WHERE Category =
MemberCategory")

If if place actual data in place for MemberCategory, it'll actually
update.
But it doesn't seem to equate the data in the two fields so it can find
the
correct record to update.

Yes, this kind of thing does get asked often, and the answer it, Don't store
LastPaid in the Members table.

Firstly, you need a MemberID field in the Deposits table, instead of keying
on LastName. Otherwise too many Cooks (or any other name) will spoil your
deposits.

Then, on the member's form, you can display the LastPaid date with a text
box that has its ControlSource set to:
=DMax("DepositDate", "Desposits", "MemberID = " & Nz([MemberID])

For other techniques on picking up the field values from a related
record,
see:
http://www.mvps.org/access/queries/qry0020.htm


I'm sure this question has been posed before, but I looked and didn't see
it, and didn't understand what I saw on the 'Net.

I don't know SQL, which is the problem, so hoping someone will take mercy
on
me.

Can someone tell me the correct syntax for SQL to update a field in one
table from a form that is adding a record in a second table.

Basically, I have my first table, named Members, with these fields: ID,
Category, LastName, FirstName, Company, Address, City, State, Zip,
MemberSince, LastPaid, PayDues

2nd table, named Deposits, with these fields: ID, Member (keyed on
LastName
in Members table), Category (from Member table), DepositDate,
DepositAmount,
Reason.

When I enter a deposit, I want the DepositDate to update in table Members
the LastPaid field.

Mercy?

 

[Allen Browne]

Okay, you have a query to use as the RecordSource of the report.

This query is based on the Members table.

Open it in design view, and type something like this into a fresh column of
the Field row:
LastPaid: ( SELECT Max(DepositDate) FROM Deposits WHERE
Deposits.MemberID = Members.MemberID )

That's called a subquery: a complete SELECT query statement inside another
query.

If you can't get that to work, you could:
- Create a query into both tables, joined on MemberID.
- Depress the Total button on the toolbar (upper Sigma icon).
- Accept Group By in the Total row under all fields from Members.
- choose Max under the DepositDate field from the Deposits table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The LastPaid date would be placed on a report. I'm trying to figure how to
make that work.

DG, what I'm trying to say is that you are creating a great deal of
unnecessary work for yourself by storing this LastPaid field in the
client
table.

I'm not where what event you are using to run this query, but have you
considered whether it will wrongly update the LastPaid field if you later
edit one of the old records? What about if the DepositDate is changed? Does
it then update the correct value for the LastPaid field? Even if another
deposit is more recent after this change? And what if a deposit is deleted?
Does it correctly find the most recent deposit date, and replace it with
that one if it was the most recent one that was deteted?

These are practical examples of one of why one of the basic rules of data
normalization says you should not store dependent data. Rather you need
to
ask Access to look up the most recent deposit date for the client when
you
need it. That way the data can never be wrong.

Sure, there are times when we break the rules and store the data, but
that
is the hard way, and you are taking on a significant maintenace load, so we
don't do that unless we have to.

It may help to know in what context you need to know the LastPaid date.
Is
this to display on a report? On a form? To use as the basis of a calculation
in a query?

Thanks, but that was ultra Greek to me.. LOL... I have NO business
doing
this stuff. However, I'm keying off a different field just because of the
last name problem.

However, I was able to put enough info together to do what I wanted,
except
I can't get the two tables to see the same category info. So, it will
either
update 0 records or all records.

This is what I have:

DoCmd.RunSQL ("Update Members Set LastPaid = DepositDate WHERE Category =
MemberCategory")

If if place actual data in place for MemberCategory, it'll actually
update.
But it doesn't seem to equate the data in the two fields so it can find
the
correct record to update.



Yes, this kind of thing does get asked often, and the answer it, Don't
store
LastPaid in the Members table.

Firstly, you need a MemberID field in the Deposits table, instead of
keying
on LastName. Otherwise too many Cooks (or any other name) will spoil your
deposits.

Then, on the member's form, you can display the LastPaid date with a text
box that has its ControlSource set to:
=DMax("DepositDate", "Desposits", "MemberID = " & Nz([MemberID])

For other techniques on picking up the field values from a related
record,
see:
http://www.mvps.org/access/queries/qry0020.htm


I'm sure this question has been posed before, but I looked and
didn't
see
it, and didn't understand what I saw on the 'Net.

I don't know SQL, which is the problem, so hoping someone will take
mercy
on
me.

Can someone tell me the correct syntax for SQL to update a field in one
table from a form that is adding a record in a second table.

Basically, I have my first table, named Members, with these fields: ID,
Category, LastName, FirstName, Company, Address, City, State, Zip,
MemberSince, LastPaid, PayDues

2nd table, named Deposits, with these fields: ID, Member (keyed on
LastName
in Members table), Category (from Member table), DepositDate,
DepositAmount,
Reason.

When I enter a deposit, I want the DepositDate to update in table
Members
the LastPaid field.

Mercy?

 

[dg.smith]

Kewl, I will give that a shot. But I'm not sure this will work for me
because I need a specific name or parameter. If I'm reading this right
(Members.MemberID), the query will ask me for these. I'm just wanting to put
in only parameter to get the last record.

Or perhaps I just don't understand. Hmmm

Okay, you have a query to use as the RecordSource of the report.

This query is based on the Members table.

Open it in design view, and type something like this into a fresh column of
the Field row:
LastPaid: ( SELECT Max(DepositDate) FROM Deposits WHERE
Deposits.MemberID = Members.MemberID )

That's called a subquery: a complete SELECT query statement inside another
query.

If you can't get that to work, you could:
- Create a query into both tables, joined on MemberID.
- Depress the Total button on the toolbar (upper Sigma icon).
- Accept Group By in the Total row under all fields from Members.
- choose Max under the DepositDate field from the Deposits table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The LastPaid date would be placed on a report. I'm trying to figure how to
make that work.

DG, what I'm trying to say is that you are creating a great deal of
unnecessary work for yourself by storing this LastPaid field in the
client
table.

I'm not where what event you are using to run this query, but have you
considered whether it will wrongly update the LastPaid field if you later
edit one of the old records? What about if the DepositDate is changed? Does
it then update the correct value for the LastPaid field? Even if another
deposit is more recent after this change? And what if a deposit is deleted?
Does it correctly find the most recent deposit date, and replace it with
that one if it was the most recent one that was deteted?

These are practical examples of one of why one of the basic rules of data
normalization says you should not store dependent data. Rather you need
to
ask Access to look up the most recent deposit date for the client when
you
need it. That way the data can never be wrong.

Sure, there are times when we break the rules and store the data, but
that
is the hard way, and you are taking on a significant maintenace load,

so
we

don't do that unless we have to.

It may help to know in what context you need to know the LastPaid date.
Is
this to display on a report? On a form? To use as the basis of a calculation
in a query?


Thanks, but that was ultra Greek to me.. LOL... I have NO business
doing
this stuff. However, I'm keying off a different field just because of the
last name problem.

However, I was able to put enough info together to do what I wanted,
except
I can't get the two tables to see the same category info. So, it will
either
update 0 records or all records.

This is what I have:

DoCmd.RunSQL ("Update Members Set LastPaid = DepositDate WHERE

Category
=

MemberCategory")

If if place actual data in place for MemberCategory, it'll actually
update.
But it doesn't seem to equate the data in the two fields so it can find
the
correct record to update.



Yes, this kind of thing does get asked often, and the answer it, Don't
store
LastPaid in the Members table.

Firstly, you need a MemberID field in the Deposits table, instead of
keying
on LastName. Otherwise too many Cooks (or any other name) will spoil your
deposits.

Then, on the member's form, you can display the LastPaid date with a text
box that has its ControlSource set to:
=DMax("DepositDate", "Desposits", "MemberID = " & Nz([MemberID])

For other techniques on picking up the field values from a related
record,
see:
http://www.mvps.org/access/queries/qry0020.htm


I'm sure this question has been posed before, but I looked and
didn't
see
it, and didn't understand what I saw on the 'Net.

I don't know SQL, which is the problem, so hoping someone will take
mercy
on
me.

Can someone tell me the correct syntax for SQL to update a field

in
one

table from a form that is adding a record in a second table.

Basically, I have my first table, named Members, with these

fields:
ID,

Category, LastName, FirstName, Company, Address, City, State, Zip,
MemberSince, LastPaid, PayDues

2nd table, named Deposits, with these fields: ID, Member (keyed on
LastName
in Members table), Category (from Member table), DepositDate,
DepositAmount,
Reason.

When I enter a deposit, I want the DepositDate to update in table
Members
the LastPaid field.

Mercy?

 

[Allen Browne]

Your Members table should have only one record for each person, i.e.
MemberID should uniquely identify the person. (You could join on the
LastName field, but that's unlikely to be unique for long.)

Use whatever field is the primary key of the Members table, and your
Deposits table should have a matching field as foreign key.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Kewl, I will give that a shot. But I'm not sure this will work for me
because I need a specific name or parameter. If I'm reading this right
(Members.MemberID), the query will ask me for these. I'm just wanting to
put
in only parameter to get the last record.

Or perhaps I just don't understand. Hmmm

Okay, you have a query to use as the RecordSource of the report.

This query is based on the Members table.

Open it in design view, and type something like this into a fresh column of
the Field row:
LastPaid: ( SELECT Max(DepositDate) FROM Deposits WHERE
Deposits.MemberID = Members.MemberID )

That's called a subquery: a complete SELECT query statement inside
another
query.

If you can't get that to work, you could:
- Create a query into both tables, joined on MemberID.
- Depress the Total button on the toolbar (upper Sigma icon).
- Accept Group By in the Total row under all fields from Members.
- choose Max under the DepositDate field from the Deposits table.

The LastPaid date would be placed on a report. I'm trying to figure how to
make that work.

DG, what I'm trying to say is that you are creating a great deal of
unnecessary work for yourself by storing this LastPaid field in the
client
table.

I'm not where what event you are using to run this query, but have you
considered whether it will wrongly update the LastPaid field if you later
edit one of the old records? What about if the DepositDate is changed?
Does
it then update the correct value for the LastPaid field? Even if another
deposit is more recent after this change? And what if a deposit is
deleted?
Does it correctly find the most recent deposit date, and replace it with
that one if it was the most recent one that was deteted?

These are practical examples of one of why one of the basic rules of data
normalization says you should not store dependent data. Rather you
need
to
ask Access to look up the most recent deposit date for the client when
you
need it. That way the data can never be wrong.

Sure, there are times when we break the rules and store the data, but
that
is the hard way, and you are taking on a significant maintenace load, so
we
don't do that unless we have to.

It may help to know in what context you need to know the LastPaid
date.
Is
this to display on a report? On a form? To use as the basis of a
calculation
in a query?


Thanks, but that was ultra Greek to me.. LOL... I have NO business
doing
this stuff. However, I'm keying off a different field just because
of
the
last name problem.

However, I was able to put enough info together to do what I wanted,
except
I can't get the two tables to see the same category info. So, it
will
either
update 0 records or all records.

This is what I have:

DoCmd.RunSQL ("Update Members Set LastPaid = DepositDate WHERE Category
=
MemberCategory")

If if place actual data in place for MemberCategory, it'll actually
update.
But it doesn't seem to equate the data in the two fields so it can find
the
correct record to update.



Yes, this kind of thing does get asked often, and the answer it, Don't
store
LastPaid in the Members table.

Firstly, you need a MemberID field in the Deposits table, instead
of
keying
on LastName. Otherwise too many Cooks (or any other name) will
spoil
your
deposits.

Then, on the member's form, you can display the LastPaid date with
a
text
box that has its ControlSource set to:
=DMax("DepositDate", "Desposits", "MemberID = " &
Nz([MemberID])

For other techniques on picking up the field values from a related
record,
see:
http://www.mvps.org/access/queries/qry0020.htm


I'm sure this question has been posed before, but I looked and
didn't
see
it, and didn't understand what I saw on the 'Net.

I don't know SQL, which is the problem, so hoping someone will take
mercy
on
me.

Can someone tell me the correct syntax for SQL to update a field in
one
table from a form that is adding a record in a second table.

Basically, I have my first table, named Members, with these fields:
ID,
Category, LastName, FirstName, Company, Address, City, State,
Zip,
MemberSince, LastPaid, PayDues

2nd table, named Deposits, with these fields: ID, Member (keyed
on
LastName
in Members table), Category (from Member table), DepositDate,
DepositAmount,
Reason.

When I enter a deposit, I want the DepositDate to update in table
Members
the LastPaid field.

Mercy?