I have 2 tables:
item (itemID, category)
loan (loanID, itemID)
the item table stores all the items, the loan table stores items that are loaned. how can i create a query or SQL to show items that are not loaned?
many thanx in advance

Try this:

SELECT item.itemID
FROM item
WHERE item.itemID NOT IN
(SELECT loan.itemID FROM loan);

Suc6,

RV

thanx, it worked http://www.access-programmers.co.uk/ubb/smile.gif

i have created another table:
repair (itemID, repair date)
how can i get it so it displays the itemIDs that are not in both repair and loan?

One solution is this:

SELECT item.itemID
FROM item
WHERE item.itemID NOT IN
(SELECT loan.itemID FROM loan)
AND item.itemID NOT IN
(SELECT repair.itemID FROM repair);


RV

thanx very much

You don't need separate tables for these three status codes. You'll save yourself a lot of work by just adding a code to your original table that says whether an item is available, on loan, or being repaired.

i'm sorry, i'm not that good with databases, so can you tell me how?

Add another field to the item table, call it Status, set Format to Text, in the display control select ComboBox, Select Value List as the control source, type in the values you want ie."Loaned";"Under Repair"
HTH

i can't do that, cos the problem i posted is just part of my system. i need all three tables separate otherwise it would cos redundant data. what i'm thinking now is in the item form i have a text box for each item showing the status of that item, so if that item appears in the loan table then the box should display "on loan" etc. How can i do that though? SQL?

You don't need three tables, two maybe, tblItem
ItemID PK
Category

tblStatus
StatusID PK
ItemId FK
StatusDate
CurrentStatus (look up previously suggested)


tblStatus has a one to many relationship with tblItem assuming you want to see the status at any given time.
This is a very simple layout although it doesn't take into account who the item is loaned to.
Post furthe details of your table designs links etc if you want further help.
HTH

i can send you my database if you want...

Providing it's relatively small, and in 97ver then OK. Please compact and zip it first
Rich@richies50.fsnet.co.uk

sorry mate it's a 2k version

i'll describe the database to you. i have 5 tables:
item (itemID, item)
cat (item, replacementcost)
loan (loanID, issuedate, datedue, patientID, itemID)
patient (patientID, name, wardnumber)
repair (itemID, inrepair, repairdate)

* inrepair is a tickbox

there is a form made for each table. loan is a subform within the patient form. what i want to do now is: in the item form i have made another text box for each item, it will be used to show that item's status. e.g. if that item is on loan (ie is part of the loan record) then the box will say "on loan", for repair it will say "in repair", else it will say "available". But i'm stuck!!!

You have the option of saving an a2k db to an earlier version, can't advise you how though.

i've converted the database, and have just sent you it.

I've looked briefly at your table structures, there are several problems, if I look at Items table most items are listed several times say crutch(adult)has 5 entries. I assume that's how many you have in store to start with, if so that's not the way to enter/store data in access. Try taking a look at some of the excellent samples provided such as Orders, Northwind etc. they should give you a better idea of how to construct your db.
HTH

where can i get Northwind etc

also that doesn't solve my problem

still though thanx Rich for pointing out. But i don't have time to do the whole database again, it's due in in 3 days time

please help....

thanx everybody, i've sorted it out myself http://www.access-programmers.co.uk/ubb/smile.gif this is my expression formula

=IIf(DLookUp("[loan]![ItemID]","[loan]","[loan]![ItemID]=[Item ID]"),"on loan",IIf(DLookUp(" [repair]![ItemID]","[repair]"," [repair]![ItemID]= [Item ID]"),"in repair","available"))