Re: update query

Tech-Archive recommends: Fix windows errors by optimizing your registry

Dear Jason: Thank you for the guidance. You are correct about the
structure of the database. However I I do not have the current status
of each book in the book database. Only on the transaction data base
do I enter the takeout date and later the return date.
I made the form pointing to the book data base with the fields being
bookbarcode, Author,Title
I made a subform, using the tools on the left side, to make a subform
pointing to the Transaction data base, bookbarcode, studentID, date-
out and put in the combo box.
What shows up are all the books that have ever been taken out, whereas
I would like to see only those that are still out.
Is that possible?
Thanks John

On May 30, 2:15 pm, Jason Lepack <jlep...@xxxxxxxxx> wrote:
Allow me to help here...

From my understanding of the situation this is the current database

structure:

Books:
barcode - primary key(?)
author
title
etc.

Students:
student_id
first_name
last_name
etc.

Transaction:
barcode (related to books.barcode)
student_id (related to students.student_id)
date_out - (date that the book was signed out)
date_in - (date that the book was returned)

Is this right?

Now I gather that you are also storing the current status of each book
in the Books table. This is a bad idea. The status of a book can be
found from checking the last record for each book in the transaction
table. If the last date_in is null then the book is out. If the last
date_in has a value then the book is in.

Create a form and set it's recordsource to the books table. Create a
subform on that form that has Transaction as it's recordsource that
will view records in data*** or continuous forms view. Put a combo
box and use the wizard to make it go to a specific record and fill it
with the barcodes.

Now to use the form, you click in the combobox, scan the barcode, and
then access will show you all transactions related to that book. Go
down to the end of the subform and take a look.

If you have any questions, jsut post back.

Cheers,
Jason Lepack

On May 30, 2:46 pm, "Amy Blankenship"

<Amy_nos...@xxxxxxxxxxxxxxxxxxxxxx> wrote:
"jlydon" <jjly...@xxxxxxxxx> wrote in message

news:1180501801.265712.159800@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On May 29, 9:46 pm, "Amy Blankenship"
How would this work? For example I have a table with a checkout date
in one column, and a return date in another column. The book is
checked out for example on May 10, 2007. When it is returned two
weeks later and I scan that information into a form, how can it find
the original check out entry to add a return date? This would make it
possible to know what books are still out, but I am not sure how you
create this change in the original entry. Thanks for any help. John

I am not really sure how you're making it work now, since I haven't worked
with bar code readers, but presumably it wouldn't be that much more
difficult to pull up an existing record based on a bar code scan than to
create a new one.

Isn't there already software out there that does this?


.

Quantcast