Update query not working?

[llgreen]

Hello!
Sorry, I'm relatively new to Access, but I've encountered a problem when creating a database for a charity's stock control.

I have two tables - titled 'Stock List' and 'Stock Lend Register' - which are linked by the unique field 'Item ID.'

As part of a checking out process, I've created an update query so that whenever someone checks out an item, a yes/no field in 'Stock List' is updated to 'no', and vice-versa for checking in (via searching for the Item ID)

But I've noticed that this query doesn't work unless the item has been previously checked out. I assume this is because the query cannot find a record to update if there is no record in the first place. Is there any way I could fix this?

Thanks!

 

[plog]

Generally you wouldn't use action queries to mark the status of items, instead you would use SELECT queries to determine the status of items.

I'm assuming Stock Lend Register tells you when something was checked out and by whom? Correct? If so, you should be able to use that to logically determine where every item is at.

Can you provide the structure of your tables and some sample data to demonstrate what you are working with?

 

[llgreen]

Of course!

Yes - and the Stock List has a field called 'Availability' that I would like to update to 'no' if an item is checked out, and 'yes' if an item is checked in.

Here's a little sample database I've been working on: ufile.io/8b5a3

 

[plog]

Again, you don't update a field with this information, you determine it. You need to work on your tables a little though:


1. Remove all non-alpha-numeric characters from table/field names. This means spaces. It just makes coding and querying harder. So [Stock List] should become [StockList] and all fields should eliminate spaces.

2. Available shouldn't be a field in any table. You are going to look at StockLendRegister and determine that logically.

You have a lot wrong in StockLendRegister, so I am going to just tell you what this structure needs to be:

StockLendRegister
RegisterID, autonumber, primary key
ItemID, number, foreign key to StockList
StaffID, number, foreign key to Staff table
RegisterActionID, number, foreign key to RegisterActions table
RegisterDate, date/time, will hold both Date and Time this action occured.
RegisterCondition, short text, same as existing field
RegisterComments, short text, same as existing field

You will need a new table to hold your staff data. You don't want to allow free form entry into this field as a text field. Because when Stephanie returns a book and the person types in "Stefany" you now have a mismatch in data. Better to assign Stephanie a number and just use that for her.

You will need a new table for actions. This table will have at least 2 actions (Lend and Return). But this can also allow you to better track items (e.g. Sent for Repair, Removed from Circulation, etc.).

You shouldn't seperate date and time of an event into 2 fields. It's a date/time field--it can hold all that data in one field. That's what RegisterDate represents.

Lastly, you will no longer use 1 record for a lending and a return. Upon lending you add a record with a RegisterActionID that corresponds to a lend, then when they return the book you add another record with a RegisterActionID that corresponds to a return. That structure will allow you to create a query to tell you the availability of every single Item.