To Access database or Not to????? 1

[curlybighair]

I'm working with a spreadsheet that is 2,000 rows down by 100 columns long. I've been asked to either create a database to query by form or to use a switch board. The probem is that I have to make it user friendly, due to most users will not know anything about Access. Queries will be made at random using some or all the information on the spread sheet. I need to know the best program to use and if it's Access which way to go with the set up. Should I do query by form or use the switchboard. I use Access for all my database needs but this is a little bigger then what I'm use too!

Thanks inadvance for the help!
Dolores

 

[KUZZ]

ACCESS is going to give you the best and fastest results Good luck,
Kuzz

"Time spent debating the impossible subtracts from the time during which you
can try to accomplish it."

 

[JeremyNYC]

Dolores,

This sounds like a great use of Access. There's an article on my website, in teh developers' section that goes into the fundamentals of how to design your tables. I suggest you read that, as you will surely want to break that spreadsheet into a few tables, and the article will guide you through that process.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[curlybighair]

Thank you Kuzz and JeremyNYC. It looks like I'll be using Access database. Now to switchboard or to query by form? I will defently be checking out your websit Jeremy for the information that you suggested. My boss will be happy to hear that I'm finally starting on his database.

Thanks a bunch!
Dolores

 

[randysmid]

Hi Dolores,
I would suggest creating your own main menu form, instead of using the switchboard. This is actually much easier than it seems. You would simply put labels on your forms that describe the form or report that the user will run. For instance, this could be "Master Employee Report". Then, to the left of that, you can insert a command button. The "clicked" event for the command button will simply run your report using some VBA (Visual Basic for Applications) code. This might look like this:
DoCmd.OpenReport "Master Employee Report", acViewPreview

Of course, your menu form will also have a button for Exiting the system. The code for that will be:
DoCmd.Quit (also goes on the clicked event)

If you need help getting into some of these things, be sure to reply to this email, or post a new message each time you encounter a new problem. HTH,
Randy Smith
California Teachers Association

 

[curlybighair]

Randy:

This is what I'm thinking: I would like to create a form that the user will go into and to pick the criteria they want in order to run a query. I want to place the query button on the form so that after picking all the information they want they can run there own query. How do I do that. I keep reading about query by form and haven't found any good examples to show me excatly how to do it. I'm a little over my head with all this, I know Access and some VB but this seems a little too advance for me. Could you suggest a good book to help me with all of this.

Thanks
Dolores

 

[LittleSmudge]

Query By Form is fine for relativly experienced users that can cope with clicking a button on the toolbar and having the form totally change its function and use whilst still looking almost identical to how it did before.


For 'inexperienced' user - I'd prefer a more prescripted query form.

Have unbound text box controls for each of the fields that they might want to select by.

Then have a tick box by each one that's a text data type so they can indicate whether the search is 'match exactly' or 'like'

Then have a 'go do the query' button

The button's OnClick event then does a
For Each txtBox Where Not IsNull(txtBox)
and collects each of the criteria into a big WHERE clause
( Using Like '" & txtBox & "*'" OR " = '" & txtBox & "'" depending on the status of the tick boxes )

Then use this WHERE clause to open a Report or a View Form of the selected data.

Make Form_Activate event contain DoCmd.Maximise and then it fills their field of view. When they close the form - loe & behold the query form is 'magically' there with all their old filtering data as it was - for them to modify and try again if they didn't like what they got first time.



'ope-that-'elps.

G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[randysmid]

Dolores,
Please send me your email address, and I will send you a small sample Access database with a few samples inside.
You can write to me directly at rsmith@cta.org

HTH,
Randy Smith
California Teachers Association