Re: update query

Can a books barcode change? Or is it always consistent?

If you want me to look at your database you can email it to me.

I'm putting together a little example to show you.

As for problem 1 you have to type Is Null in the Criteria line for
Date In.

As for problem 2, I'll get back to you.

Cheers,
Jason Lepack

On Jun 5, 12:13 am, jlydon <jjly...@xxxxxxxxx> wrote:
Dear Jason: When I click on the ellipsis what comes up is something
called Expression Builder and not Query Designer. I typed Is Null in
but it would not recognize it. This is problem 1.
Any idea of what I could try next.
Problem 2: One problem is that when I scan a book out it registers
that fact in line 3 of the table bookstatus. When the book comes back
in a week it is registered on line 12 since 9 other books have been
taken out between time. So the same book (same barcode) has in
outdate on line 3 and an indate on line 12 of the BookStatus
database. I presume if I can run the Is Null program, the book that
is registered on line 3 will still show up as missing, since the
indate in on another line of the table. Any help is appreciated.
Thanks
John
On Jun 4, 7:18 am, Jason Lepack <jlep...@xxxxxxxxx> wrote:



In the subform at this point you should see all transactions for the
selected book.

If you would like to filter it then you just select the properties for
the form that is inside the subform and on the recordsource you want
to click the ellipsis (...) and then in the query designer that pops
up change the criteria for the date in to IS Null.

Cheers,
Jason Lepack

On Jun 2, 11:42 pm,jlydon<jjly...@xxxxxxxxx> wrote:

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?- Hide quoted text -

- Show quoted text- Hide quoted text -

- Show quoted text -


.

Quantcast