I am creating a system for a local toy library that i work for, and they have requested that I make the system keep a record of everyone who visits the toy library each day (wednesday and thursdays). At the moment, all this information is just kept in a diary under the desk, but they would like it computerised along with everything else.

Now the only plausable way i can think of doing this would be to have one table, with one field with the date, and another field with the names of the visitors seperated by commas in another field. However, i am unaware of using a parameter query to search IN a field, rather than FOR a field. Is there a way of using this? I am unfamiliar with SQL, but when i move onto the implementation stage i will probably look into it to see if i would find it of any use to me.

Thanks in advance for any help :)

If you start with a solid database design you will not have to deal with "searching for visitors names separated by commas" in a field.

If you prefer a class to the book selections previously suggested you may want to check out the Database Design class offered through ED2GO.com.

HTH,
Loralee

Actually, Big, you almost never have a field with data in it that separated by a comma. If what people put down in the book is (for example) date, name, address, phone, and e-mail then your simplest table would look something like this

tblVisitors
lngID, type LONG, autonumber
dtmDOV, type DATE/TIME (DOV means Date of Visit)
strLastName, type TEXT
strFirstName, type TEXT
strAddress1, type TEXT
strAddress2, type TEXT
strState, type TEXT
strZIP, type TEXT
strPhone, type TEXT
strEmail, type TEXT

Names should be separated by first and last name, not stored together.

The address fields (two of them in case their addresses take up two lines) are text and those can have punctuation. Some people even separate out the numerical part of the address in its own field, but my preference is NOT to do that.

The phone and ZIP code fields are text because you're not going to perform mathematical operations on them. Any field (even if it holds numbers) that you do not do math on usually should be type TEXT. Believe it or not it makes formatting them easier, especially if leading zeroes are involved, e.g. NJ ZIP codes start with 0.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

No, all that is stored is a list of names against one date. This isnt linked through a relationship to any other table, or database, it is only used for reference purposes and, for instance, searching through to see when someone last visited.