You have used SQL as the discussion title, but I don't see any particular need to use it for the issue at hand.
The first problem that I see is that you seem to have one table where you need two.
I looks to me, based on limited information, that you need at least two tables: Equipment, where you have one record for each item of equipment that can be checked out. and Log--a table where each record records the loan of a single item of equipment with a date checked out and a date returned. The same item checked out and returned an indefinite number of times.
A count of items currently "out" would be a count of all the Log records that have a checked out field but no checked in. A count of items currently "in" would be a count of the equipment records where their "last" log record has data in the "checked in" field.
Im with Phil on this. Not a lot to go on, but assuming you have a Log table maybe something like this should work using SQL.
"SELECT COUNT(id) FROM Log WHERE CheckOut IS NOT NULL AND CheckIn IS NULL"
I got the "equipment Inventory" table set up with the serial number of the item, the model, date checked out, and date checked in. I am not to much of an advanced user in FileMaker, so how would I get the fields to automatically import the data from the "log" table into the Inventory table when it fields are entered on the "Log" table?
And then what would you recommend me use as a script to run a count to show what is checked out on my main view and what is not checked out. I haven't gotten a lot of time using scripts and am not very good with writing script.
Thanks for all of your help in pointing me in the right direction.
so how would I get the fields to automatically import the data from the "log" table into the Inventory table when it fields are entered on the "Log" table?
You wouldn't import any such thing. There will be many entries in the log table for any given record in the inventory table. That's why you need two tables in the first place. What you can do is put a portal to Log on the Inventory table to show all the log entries for that Inventory item. You could also just show fields from the most recent log entry for a given inventory item as a way to show whether a given item is checked out or in. This last option can be done with a one row portal if you sort the portal (or the relationship) by the date checked out field in descending order.