update query

[jlydon]

I have a form which has on it the field for entering the barcode of a
book, a field for the student ID and a date field for returning of
book. When I put this information in I want it to run a query that
updates another table where I have the barcode of the book and the
date the book was taken out so that it registers that the book taken
out is now returned. Any help possible for this. Thanks.

 

[Amy Blankenship]

I have a form which has on it the field for entering the barcode of a
book, a field for the student ID and a date field for returning of
book. When I put this information in I want it to run a query that
updates another table where I have the barcode of the book and the
date the book was taken out so that it registers that the book taken
out is now returned. Any help possible for this. Thanks.

Could you be a bit more specific about what your table structure is and how
your forms are accessing it? It sounds like you could probably accomplish
this by designing your tables and forms correctly.

-Amy

 

[jlydon]

Could you be a bit more specific about what your table structure is and how
your forms are accessing it? It sounds like you could probably accomplish
this by designing your tables and forms correctly.

-Amy

Thank you for the reply. I have a table which has all the books of a
library entered in, including its barcode, title, author, etc. I have
another table with all of the student ID information. I made a form
based on a third table that is book-status, for when a student takes
out a book, I scan in the book barcode, then scan in the student ID,
and the date for taking the book out is automatically entered. When
they bring the book back in a week or so, I scan in the book barcode,
the student ID and the return date. So I need it to update the book-
status table so that the same book that was taken out is now
registered as returned.

 

[Amy Blankenship]

Thank you for the reply. I have a table which has all the books of a
library entered in, including its barcode, title, author, etc. I have
another table with all of the student ID information. I made a form
based on a third table that is book-status, for when a student takes
out a book, I scan in the book barcode, then scan in the student ID,
and the date for taking the book out is automatically entered. When
they bring the book back in a week or so, I scan in the book barcode,
the student ID and the return date. So I need it to update the book-
status table so that the same book that was taken out is now
registered as returned.

So it worked for setting the status to checked out or whatever, but for some
reason you're not able to repeat that to get it to change the status back?
I'm not understanding what the issue is.

 

[jlydon]

So it worked for setting the status to checked out or whatever, but for some
reason you're not able to repeat that to get it to change the status back?
I'm not understanding what the issue is.

What happens is that when a book is checked out, the date is entered
in the table of book-status with the barcode of the book and the
student ID, but when a week later the book is returned the date of
returned in entered into a different line on the table, so when I run
a report it still shows the book out because the original information
is not updated, rather a new entry is created.

 

[Amy Blankenship]

What happens is that when a book is checked out, the date is entered
in the table of book-status with the barcode of the book and the
student ID, but when a week later the book is returned the date of
returned in entered into a different line on the table, so when I run
a report it still shows the book out because the original information
is not updated, rather a new entry is created.

It sounds like your actual problem is that whatever you're using to pull the
current status of the book is not limiting its returned data to the most
recent record in the status table. You should not update the older record,
because the status it showed was correct for the event that occurred.

You could also go with a different table design that shows check out begin
date and end date + studentID. Books with a begin date but no end date in
the most recent record are then checked out by implication.

HTH;

Amy

 

[jlydon]

It sounds like your actual problem is that whatever you're using to pull the
current status of the book is not limiting its returned data to the most
recent record in the status table. You should not update the older record,
because the status it showed was correct for the event that occurred.

You could also go with a different table design that shows check out begin
date and end date + studentID. Books with a begin date but no end date in
the most recent record are then checked out by implication.

HTH;

Amy

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

 

[Amy Blankenship]

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?

 

[Jason Lepack]

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 datasheet 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

 

[jlydon]

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

 

[Jason Lepack]

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

 

[jlydon]

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

 

[Jason Lepack]

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

 

[jlydon]

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

Dear Jason: I replied a week ago to your message when you asked if I
could send you a copy of the database and asked where to send it. I
didn't get a reply so I was wondering if you could still help me out
here. The barcode for each book is unique as you asked. Many thanks.
John

 

[Jason Lepack]

That means that you sent me an email and didn't post here as well. It
went into my junk box never to be seen again. But since I now know
that you're sending it to me, post here once you've sent it and I'll
retrieve it and get back to you.

I would love to help you out.

Cheers,
Jason Lepack

 

[jlydon]

That means that you sent me an email and didn't post here as well. It
went into my junk box never to be seen again. But since I now know
that you're sending it to me, post here once you've sent it and I'll
retrieve it and get back to you.

I would love to help you out.

Cheers,
Jason Lepack

Dear Jason: Just to let you know that I sent the database to your
email. I hope it was received.
John

 

[Jason Lepack]

I received it.

I don't read spanish but I will do my best and get back to you.

Cheers,
Jason Lepack

 

[jlydon]

I received it.

I don't read spanish but I will do my best and get back to you.

Cheers,
Jason Lepack

Dear Jason: Greetings. Any luck with figuring out how to update the
entry in the database?
Thanks.
John