Extremely Newbish Question

[Tredici]

First off, I've gotta apologise for how totally poor I am with Access. I know very little so please retract your laughter if my methods are completely stupid, haha. Basically, I'm using Access to pretty much automate a medium sized businesses accounts with reports, but on top of that, I'm using queries to search for various entries in the database.

The DB consists of only one table. I was told specifically to do it this way because the owner of the company is useless with Access and believes it's easier this way, even though, to be honest, it'd be easier with a few relational tables. Anywho, in this one table are details regarding Clientele and their Maintenance Contract information. There is one field entitled 'Name' which is where the customers' name is kept, obviously. I was also told specifically to keep Forenames, middle names and Surnames all in this 'Name' field, which quite obviously leads to a few complications.

At the moment I'm using queries to search specific fields. I use a select query and then call on user input to determine the search criteria in a specific field. For example, viewing the select query in design view, there's the field 'Name', then under this field, next to 'Criteria' is "[Input Customer Name]". Upon executing the query, the user then inputs the customer name and results are displayed. Firstly, is this the most sensible and efficient way to search for a customer by their name? Are their easier ways to achieve this?

My second question. Say, for example, I have a record with a value of 'D. Smith' under the 'Name' field. Say after a phone call to this person they've told me their name is 'Mr. Smith', then obviously, if I were to search for his information, I'd input 'Smith' as search criteria upon query execution. However, Mr Smith's details will not be called upon, not unless the exact value 'D. Smith' is inputted. Is there anyway to return data if it part-matches a search criteria? It'd streamline the search process, that's for damn sure.

Sorry for blabbering on, but I'm not down with the ol' Access lingo so I've had to explain stuff in excess to ensure people actually get what I'm trying to say, haha. Cheers for any help in advance, I'd appreciate it. David.

 

[Banana]

First, I'd tell your boss that if he want to have one table, he should be using Excel.

Second, you are strongly advised to read up on normalization, database design theory and relationship. It sounds like the database you have isn't normalized and that will make queries and other function much more problematic.

Thirdly, to directly answer your question, there's a sample database forum that has a database that allows you to do a dynamic search similar to what you just described. Look it over.

HTH.

 

[Tredici]

Cheers for the response Banana. To be honest with you mate, I have read up on Normalization and the like. I understand how stupid it is to have a field for 'Name' and have spacing in values and inconsistent input, and you're right mate, it is much more problematic!! Hence the reason I'm here, seeking help haha. Do you recommend I tell the boss to invest in some training and create the database the proper way, or is there a relatively simple solution to the problem I have? Also mate, what do you mean by a 'sample database forum that has a database'? Sorry, I don't follow.

Cheers, David.

 

[Banana]

To clarify, do you know that nobody but the devleoper is supposed to be looking at the table and queries? All end users, including your boss, should be only looking at forms?

I'd just tell your boss that if he lets you, you can create a easy to use form and let Access/the developer do all the dirty work of handling data in proper normalized way.

I guess forum isn't the best word; but that's what they call it; I suppose it shoudl be a sub-forum. It's over http://www.access-programmers.co.uk/forums/forumdisplay.php?f=64. You can search the forum using "Forum Tools" to find the search form I cited above.

HTH.

 

[Tredici]

Thanks a lot for that mate, and about the use of forms, to be honest, I have little experience with those but at very least know that they are used for end user input and what not. However, the company's a little shabby and the boss is too tight to splash out on a developer as such, and I, with very little but hopefully sufficient knowledge, have opted to do the work for him. He's the kinda person that wants to be real involved with the nitty gritty, so forms probably wouldn't cut it for him. You know, he's not particularly generous with the delegation, haha. Cheers for your help again Banana, appreciate it! David.

 

[elbweb]

something quick and easy would probably be just using the form wizards, just create a new form, use the wizard, select the parts of the table you want to see and you should be good to go from there.

you could be able to right click on a field and use a search/filter option to find what you want, in that field, and it would be a very easy way to get something better to look at.

i'd be willing, if you would like, to create something different that would more then likely give you exactly what you wanted, if you gave a empty copy of the table, and what you would like to be able to search by, and what you would like to show, its simple, but a little complicated to just explain. i'd be glad to comment my heart out and give you something to read and learn up on though.

 

[Tredici]

Geez, thanks for your willingness to help elbweb! I've sent you a PM so I look forward to hearing back from you. Cheers again, David.