Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 31st, 2006, 02:16 PM
Authorized User
 
Join Date: Oct 2004
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to BigH140
Default Problem with a system

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 :)

 
Old January 31st, 2006, 10:37 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

 
Old February 1st, 2006, 10:10 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old February 6th, 2006, 04:57 PM
Authorized User
 
Join Date: Oct 2004
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to BigH140
Default

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.






Similar Threads
Thread Thread Starter Forum Replies Last Post
'System.InvalidCastException' Problem! Please help CyberGeek ADO.NET 0 March 19th, 2006 03:14 PM
login system problem arakay Beginning PHP 1 June 27th, 2005 09:15 AM
system.mdw problem stoneman Access 7 September 14th, 2004 09:46 AM
problem with system message vubinhsg BOOK: Access 2003 VBA Programmer's Reference 6 July 30th, 2004 01:20 AM
Problem with the System Registry Piccio BOOK: ASP.NET Website Programming Problem-Design-Solution 1 November 30th, 2003 04:00 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.