Update Query?

[ccs2]

I have a dbase that records the loan of instruments to band members. Each instrument is recorded in the Instrument table and is, of course, unique. When one is loaned out and recorded in the loan table I want it to be automatically deleted from the Instrument table. I can live with having to manually re-enter it into the Instrument table when it is returned if that makes things simpler.

Cavan

 

[pkailas]

Wouldn't it be easier to just add a column called "CheckedOut"? then when you query your inventory just make ser you filer out the ones that have that flag set...

_______
I love small animals, especially with a good brown gravy....

 

[ccs2]

Regarding working with filtered records.
I take what you say about flagging up then filtering on that. In fact I should be able to filter by date. I know I can filter by selection on any field and have viewed the code in the Form Properties but I want this to apply each time the form is opened. Help?

Cavan

 

[pkailas]

Basically, my suggestion would entail separate queries.
1. Query to loan out instrument
"SELECT * FROM [INVENTORY] WHERE LOANED = 0"

2. Query to return instrument to inventory
"SELECT * FROM [INVENTORY] WHERE LOANED = 1"

3. Query to list all inventory
"SELECT * FROM [INVENTORY]"

You could make a listbox with the three:
Loan out an Instrument
List Intruments on Loan
List all Instruments

now, the question is this.. where are these forms? Are you making an Acess front end, a web based application or a VB client?



_______
I love small animals, especially with a good brown gravy....

 

[ccs2]

Its an Access front end. The Form is based on a Select Query (qryLoans)which takes details from:

tblLoan (LoanID, MemberID, InstrumentID, DateLoaned, DateReturned, Condition etc.)
tblMember(MemberID etc.)
tblInstrument(InstrumentID, NameofInstrument)

On the form MemberID and InstrumentID are both used as a lookups for other details.

thanks for your patience

Cavan

 

[pkailas]

ahh.. well, my bad. I've never used Access for my front end. I always write my own.

_______
I love small animals, especially with a good brown gravy....

 

[ccs2]

Thanks anyway! Next time I'll make it clear.

 

[PHV]

To have a list of not loaned instruments:
SELECT InstrumentID, NameofInstrument FROM tblInstrument
WHERE Not InstrumentID In (SELECT InstrumentID FROM tblLoan WHERE DateReturned Is Null)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244