Updating Yes/No field in a table via a query

[immortal_dranzer]

I am making a Library Management System as my O'Level Coursework (and i do not know much about access) but still, somehow I have created all necessary tables, queries and forms (except for login like employees table etc.).

The System should be updated when a book is borrowed (it is a yes/no field). I know that an update query has to be used and i have tried to make it. The image below shows all tables and the update query and the issue.





I am sorry if my English is bad but i am in a little hurry as i haven't even started on the documentation.

[June7]

Why should you have to check these two fields against each other? Why is this field even in both tables?

Not sure what you want but try simply: = [Borrow Table]![Borrowed?]

Have you looked at the Lending Library template for ideas?

BTW, advise avoid spaces, special characters, punctuation (underscore is exception) in names or reserved words as names. Better would be BorrowTable or Borrow_Table and IsBorrowed or Is_Borrowed.

[immortal_dranzer]

i'm sorry, it looks like i havent told it right...what i want to do is.....if the book is borrowed and then ticked borrowed in the Borrow Table, That same book must be ticked as borrowed in the books table....the reason why this field is in both tables is so that a transactions log can be maintained where we can see which member took which book when.....and i tried your method....it says "Syntax error in update statement"

Also, will it not create an issue if i change the spaces and "?'s in table, query names now?

[June7]

Having the Yes/No in both tables is a duplication of data. Duplication of data violates basic relational database principle. I understand the need for transaction history. However, maintaining a yes/no field is not needed at all, in either table. Whether or not a book is in/out can be determined by joining the Books table to the Transactions table (your borrow and return tables combined or you can leave them separate) and calculating the status. This is demonstrated in the Lending Library template at http://office.microsoft.com/en-us/te...010206883.aspx

[immortal_dranzer]

how do we know whether if the book is borrowed in the template? also how do i calculate status of the book without a field about it on the transactions table?

[immortal_dranzer]

I saw a similar coding in an update query in the internet and so i tried to do the same, but for me it says that "Syntax error in UPDATE statement"

Heres My Code:

UPDATE [Books] INNERJOIN [Borrow Table] ON [Books]![BkCode_No] = [Borrow Table]![BkCode_No] SET [Books]![Borrowed?] = Yes;

Also as i said before, if i change the spaces in table names now, will it affect the database?

[orange]

I would try this syntax(untested)

UPDATE [Books] INNERJOIN [Borrow Table] ON [Books].[BkCode_No] = [Borrow Table].[BkCode_No] SET [Books].Borrowed?] = -1

but I would never, ever have spaces or special characters in field or object names. (as was pointed out in post #2)

You should really work on normalizing your tables. Or, adapting the Lending Library Template, or parts of it, to your needs. That template has been tested and is offered as a general solution/model to help you.

[June7]

Use of the elements I described in names means must remember to enclose []. Access will usually try to provide them when needed but can sometimes be confused. VBA will never provide them so must remember to type. When forgotten is annoying bug to track down.

Even when following the advised naming convention, there are times the [] will still be required and Access won't provide them. For instance, use of a domain aggregate function (DLookup, DSum, DCount) in a query. If the DLookup references a field in the query as criteria source, Access will not automatically recognize the field name and will treat it as text string, placing " marks instead []. Will have to type the [] to fix.

[immortal_dranzer]

I am renaming all fields and table names now....also, earlier you said that

Having the Yes/No in both tables is a duplication of data. Duplication of data violates basic relational database principle.

, so does that mean that having B_Name in the borrow table is also duplication of data? if it is, and say i remove it, when i make a form for it it wont be there, and since the form is supposed to be user friendly, the book code no itself alone would not be okay to make a transaction right?

[June7]

Borrow Table should not have either Name or B_Name, only UCA_No and BkCode_No. You would retrieve the names by joining the tables in a query on the primary/foreign keys. Comboboxes would be multi-column. Review tutorials at http://datapigtechnologies.com/AccessMain.htm especially the 3 on comboboxes in the Access Forms: Control Basics section.

[immortal_dranzer]

i followed your instructions and it worked! now when i borrow a book and run update query, its updated! so with that i wanted to update the no. of books that a member borrowed as well, but i dont know how to type that into the update field

[June7]

You want to save a value of how many books member has checked out? This is even more ill-advised than the yes/no fields. Determine how many books member has checked by calculating whenever needed. Build a report that shows this info. Use the report group & sorting with aggregate calcs to display detail records and summary calcs (such as total books).

[immortal_dranzer]

thanx for the help!.....I would have kept going in circles if it wasn't for this forum!