Append and delete stock from tables

[Omar319]

Hiya,
I'm making a database with the tables tblItem, tblBorrower, tblPostcode (this
is done through normalisation 3NF) and tblLoan. In tblItem I have a list of
items with a unique ID number, for example I have 20 books each with an ID
bk01, bk02, bk03 etc. I've made a query (qryOnHandStock) to calculate how
many items are left in stock after they've been loaned. I've also made this
an append query to update the records in a table called tblOnHandStock (made
using a make-table query) (this table isn't related to any other tables and I
cannot make ItemID in this table a primary key to make the relationship).

I'm fine up to this point but I want to be able to delete the items from
tblItem using the data from tblOnHandStock using a delete query so that the
item cannot be selected from the form once it's been loaned out.

I'd also like to do the reverse - have a returns form so that when an item is
returned it is taken off the loan table (tblLoan) and put back into the item
table (tblItem).

Can anyone tell me how this is done?

Thanks,
Omar

 

[Steve]

Hello Omar,

Consider a simpler system:

TblItem
ItemID
Item

TblLoanedOut
LoanedOutID
ItemID
LoanedOutDate
BorrowerFirstName
BorrowerLastName
BorrowerAddress
BorrowerTelephoneNumber
ReturnedDate

An item is not loaned out if:
1. An ItemID from TblItem does not appear in TblLoanedOut. You can use an
unmatched query for this, OR
2. An ItemID from TblLoanedOut has a LoanedOurDate but no ReturnedDate.
You can use a select query for this by setting the criteria for ReturnedDate
to Null.

Steve

 

[Steve]

Correction for #2:

2. An ItemID from TblLoanedOut has a LoanedOurDate and a ReturnedDate.
You can use a select query for this by setting the criteria for
ReturnedDate
to Is Not Null.

Steve

 

[Keith Wilby]

Hiya,
I'm making a database with the tables tblItem, tblBorrower, tblPostcode
(this
is done through normalisation 3NF) and tblLoan. In tblItem I have a list
of
items with a unique ID number, for example I have 20 books each with an ID
bk01, bk02, bk03 etc. I've made a query (qryOnHandStock) to calculate how
many items are left in stock after they've been loaned. I've also made
this
an append query to update the records in a table called tblOnHandStock
(made
using a make-table query) (this table isn't related to any other tables
and I
cannot make ItemID in this table a primary key to make the relationship).

I'm fine up to this point but I want to be able to delete the items from
tblItem using the data from tblOnHandStock using a delete query so that
the
item cannot be selected from the form once it's been loaned out.

I'd also like to do the reverse - have a returns form so that when an item
is
returned it is taken off the loan table (tblLoan) and put back into the
item
table (tblItem).

Can anyone tell me how this is done?

Deleting and reinstating records, and appending tables all contribute to
file size bloat so it's worth avoiding if possible. Have you considered
flagging records in the Items table when they are not available? You could
do this by using either a check box bound to a Boolean field or have a "Date
Out" text box bound to a date field. You could then query the table with
the appropriate filter criteria to return only the available items.

Keith.
www.keithwilby.co.uk