 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / July 2006Update Field from Another Table
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. >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. [quoted text clipped - 10 lines] >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 [quoted text clipped - 13 lines] > >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 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. [quoted text clipped - 49 lines] > > John W. Vinson[MVP] 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. [quoted text clipped - 49 lines] > > John W. Vinson[MVP] >John: >In a report, I created a text box in a group header and put the DSUM formula [quoted text clipped - 6 lines] >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] 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 >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. [quoted text clipped - 38 lines] > > I know that this is cumbersome but please help if you can. Thank you. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.