Update Field from Another Table

[Elaine]

I have an inherited database set up in a way that I find confusing. I hope
someone can help as I don't have the expertise to normalize this database.

There are three tables, in particular, that are giving me trouble.

One called tblBooks (and this is imported monthly from Excel) has several
fields but the pertinent ones are PurchaseID (PK), Book and Copies.
Sample data
PurchaseID Book Copies
1 Book1 2
2 Book2 1
3 Book2 4
4 Book3 1
5 Book1 2

I have another table tblTotCopies with relevant fields Book(PK) and Copies
Book Copies
Book1 4
Book2 5
Book3 1

tblTotCopies is the parent table to a daughter table tblOffice with fields
Book (Foreign Key) OfficeNum

Book OfficeNum
Book1 L44
Book2 J106
Book2 J103
Book2 J105

Is there any way that I can easily update the Copies field in tblTotCopies?
I can update the Books field through an Append Query but I don't know how to
update the Book copies field. Append Queries don't work on the Copies field
as the same books are purchased over and over again. My real difficulty is
that tblTotCopies is a parent table and I am not able to delete it completely
each month as it has related fields in tblOffice.

I know that this is cumbersome but please help if you can. Thank you.

 

[John Vinson]

I have an inherited database set up in a way that I find confusing. I hope
someone can help as I don't have the expertise to normalize this database.

There are three tables, in particular, that are giving me trouble.

One called tblBooks (and this is imported monthly from Excel) has several
fields but the pertinent ones are PurchaseID (PK), Book and Copies.
Sample data
PurchaseID Book Copies
1 Book1 2
2 Book2 1
3 Book2 4
4 Book3 1
5 Book1 2
ok...

I have another table tblTotCopies with relevant fields Book(PK) and Copies
Book Copies
Book1 4
Book2 5
Book3 1

The field Copies *SHOULD NOT EXIST* in this table, since it can be
calculated on demand.

tblTotCopies is the parent table to a daughter table tblOffice with fields
Book (Foreign Key) OfficeNum

Book OfficeNum
Book1 L44
Book2 J106
Book2 J103
Book2 J105

Is there any way that I can easily update the Copies field in tblTotCopies?
I can update the Books field through an Append Query but I don't know how to
update the Book copies field. Append Queries don't work on the Copies field
as the same books are purchased over and over again. My real difficulty is
that tblTotCopies is a parent table and I am not able to delete it completely
each month as it has related fields in tblOffice.

I know that this is cumbersome but please help if you can. Thank you.

I'd simply remove the Copies field from tblTotCopies - which really
should be just a Books table, with the Book as its primary key;
calculate the total number of copies by either using a Totals query on
tblBooks, grouping on Book and summing Copies; or use a textbox on
your form or report with a controlsource like

=DSum("[Copies]", "[tblBooks]", "[Book] = '" & Me.Book & "'")


John W. Vinson[MVP]

 

[Michel Walsh]

Hi,


UPDATE TotCopies INNER JOIN Purchases ON TotCopies.Book = Purchases.Book
SET TotCopies.Copies = TotCopies.Copies + Purchases.Copies




or


UPDATE TotCopies INNER JOIN Purchases ON TotCopies.Book = Purchases.Book
SET TotCopies.Copies = Purchases.Copies





The first one add the purchased copies, the second one just replace the
number of copies.



Hoping it may help,
Vanderghast, Access MVP

 

[Elaine]

John and Michael:

Thank you very much for your responses. I really appreciate the tips that
you have shared -- both on how to normalize the database and how to update a
field in a table.

I tried working on it yesterday without success. I was really stuck but I
think that I have somewhere to go given your detailed responses. Again, thank
you very much for your kind help.

Elaine

I have an inherited database set up in a way that I find confusing. I hope
someone can help as I don't have the expertise to normalize this database.

There are three tables, in particular, that are giving me trouble.

One called tblBooks (and this is imported monthly from Excel) has several
fields but the pertinent ones are PurchaseID (PK), Book and Copies.
Sample data
PurchaseID Book Copies
1 Book1 2
2 Book2 1
3 Book2 4
4 Book3 1
5 Book1 2
ok...

I have another table tblTotCopies with relevant fields Book(PK) and Copies
Book Copies
Book1 4
Book2 5
Book3 1

The field Copies *SHOULD NOT EXIST* in this table, since it can be
calculated on demand.

tblTotCopies is the parent table to a daughter table tblOffice with fields
Book (Foreign Key) OfficeNum

Book OfficeNum
Book1 L44
Book2 J106
Book2 J103
Book2 J105

Is there any way that I can easily update the Copies field in tblTotCopies?
I can update the Books field through an Append Query but I don't know how to
update the Book copies field. Append Queries don't work on the Copies field
as the same books are purchased over and over again. My real difficulty is
that tblTotCopies is a parent table and I am not able to delete it completely
each month as it has related fields in tblOffice.

I know that this is cumbersome but please help if you can. Thank you.

I'd simply remove the Copies field from tblTotCopies - which really
should be just a Books table, with the Book as its primary key;
calculate the total number of copies by either using a Totals query on
tblBooks, grouping on Book and summing Copies; or use a textbox on
your form or report with a controlsource like

=DSum("[Copies]", "[tblBooks]", "[Book] = '" & Me.Book & "'")


John W. Vinson[MVP]

 

[Elaine]

John:
In a report, I created a text box in a group header and put the DSUM formula
provided below:

Formula in unbound textbox:
=DSum("[Copies]", "[tblBooks]", "[Book] = '" & Me.Book & "'")

I get an error message that says "Enter Parameter Value Me"
BTW, what is the difference between the ' and the "?
Thanks.


PurchaseID Book Copies
1 Book1 2
2 Book2 1
3 Book2 4
4 Book3 1
5 Book1 2

I have an inherited database set up in a way that I find confusing. I hope
someone can help as I don't have the expertise to normalize this database.

There are three tables, in particular, that are giving me trouble.

One called tblBooks (and this is imported monthly from Excel) has several
fields but the pertinent ones are PurchaseID (PK), Book and Copies.
Sample data
PurchaseID Book Copies
1 Book1 2
2 Book2 1
3 Book2 4
4 Book3 1
5 Book1 2
ok...

I have another table tblTotCopies with relevant fields Book(PK) and Copies
Book Copies
Book1 4
Book2 5
Book3 1

The field Copies *SHOULD NOT EXIST* in this table, since it can be
calculated on demand.

tblTotCopies is the parent table to a daughter table tblOffice with fields
Book (Foreign Key) OfficeNum

Book OfficeNum
Book1 L44
Book2 J106
Book2 J103
Book2 J105

Is there any way that I can easily update the Copies field in tblTotCopies?
I can update the Books field through an Append Query but I don't know how to
update the Book copies field. Append Queries don't work on the Copies field
as the same books are purchased over and over again. My real difficulty is
that tblTotCopies is a parent table and I am not able to delete it completely
each month as it has related fields in tblOffice.

I know that this is cumbersome but please help if you can. Thank you.

I'd simply remove the Copies field from tblTotCopies - which really
should be just a Books table, with the Book as its primary key;
calculate the total number of copies by either using a Totals query on
tblBooks, grouping on Book and summing Copies; or use a textbox on
your form or report with a controlsource like

=DSum("[Copies]", "[tblBooks]", "[Book] = '" & Me.Book & "'")


John W. Vinson[MVP]

 

[John Vinson]

John:
In a report, I created a text box in a group header and put the DSUM formula
provided below:

Formula in unbound textbox:
=DSum("[Copies]", "[tblBooks]", "[Book] = '" & Me.Book & "'")

I get an error message that says "Enter Parameter Value Me"
BTW, what is the difference between the ' and the "?
Thanks.

Sorry, my mistake! Just us [Book] instead of Me.Book.

Since Book is (presumably) a text field, it must be delimited with
quotemarks when you're searching for it. In this case you're using
DSum to search for all the copies of this book in tblBooks. The third
argument of DSum needs to be a valid SQL Where clause such as

[Book] = '0-7821-2853-X'

assuming you have ISBN codes in the field Book.

If you have titles, and the title might contain an apostrophe, you
can't use apostrophes as a delimiter: use " instead. To do so you need
to enclose TWO " marks within the string delimited by " marks:

=DSum("[Copies]", "[tblBooks]", "[Book] = """ & [Book] & """")


John W. Vinson[MVP]