Basic Query Question (Access XP)

Topic

New Reply

I don’t really know how to ask this question. Being a novice when it comes to Access I want to be able to use a form that will allow me to filter and sort my database rather than have to build ten different queries to ask all the questions I want. Is there a way to do this? For example I have a huge database of sales information from my point of sale software. I would like to build one query that will allow me to filter and sort by several different fields. The questions I have are “How many of item named ‘Small Fry’ did I sell today?” or “What item did we sell the most of today?”.

To me that is what a database should do for me and I shouldn’t have to “crawl inside it” to build a query.

The frustration I often feel while trying to get at all this information I have usually just sends me home with a headache.

thanks for listening to me vent.

Bret

Reply | Quote

Replies

Brett,

I’m not sure about doing this in access (Other than running filters and queries on a single table or a query datasheet) but if you build a basic query that will contains records with all the info you need in big flat table you can export it to Excel where you can use the Data–Filter tools to answer quick questions and perform quick sorts all from the field name quick Pull Downs. Here is an example

Reply | Quote

Thanks Ed,

I’ll give this a try. It is just so frustrating that all this information seems to be right at my finger tips and all it is is so much fluff.

Thanks again,

Bret

Reply | Quote

Another thought.

build some basic queries and put paramater criteria in each field. any left blank will return all records and you can enter the criteria for only the fields you want to limit.

Reply | Quote

Here is an example of one of the things that frustrates me. I would like to be able to simply ask “How many of item X did we sell between these two dates?” I want it to be able to be asked by me or my manager who is a complete novice to computing. therefor it will really require a form that has all the different sort criteria available. Here is the rub, the datatime field that is in the database tracks each individual sale by data and time. So unless I use the DateValue function I get fifty entries that return all with one sales each.

I tried the Excel import and the database is to big.

Thanks for all the help, you will all be hearing from me more. I will not be defeated in my quest. I just get frustrated and think that I must be going about things in the wrong way.

Thanks a lot,

Bret

Reply | Quote

I’m not against Excel, but you have a limit of 64k records with Excel 2000, and you say you have a huge database of sales information.

How many different fields are you likely to be querying? Also how many different ways do you wish to sort these details?

What you could do is build the SQL on the fly depending what fields have been chosen, and you could also dynamically build the ORDER BY part of the query. I have done this kind of thing in the past, it’s very useful.

Pat

Reply | Quote

Pat you are right the database is to large for Excel.

Now, where do I turn to learn SQL? Are there any good sources on the web? Books?

Thanks,

Bret

Reply | Quote

As a book, I would suggest you try “Alison Balter’s Mastering Access 2002 Desktop Development” published by SAMS. It assumes you have some basic knowledge about computers and databases, but walks you through the entire process. In addition, it contains lots of practical on-the-job knowledge. There are a number of other good books as well – if you are going to be a serious developer, you will want to have a copy of “The Access Developer’s Handbook” among others.

As to your question about determing counts, doing drill downs, and that sort of thing, the answer about filtering by form is spot on, and Access is the right product. With only a simple form connected to a table, you can with a right click on the product, restrict the recordset to a specific product by choosing Filter By Selection. Then in the sale date, another right click and entry of a >1/1/2003 will give you all the sales of that product since the first of the year. Another right click in the sale date, and entry of <4/1/2003 and you will have the sales of the selected product during the first quarter of 2003. This assumes that your form contains the standard navigation controls on the bottom right which give you a record count – you can make it a continuous form that displays several records if that makes it easier to understand. (Also note that you can do that sort of thing at the table level, but it isn't recommended for anyone other than database administrators.) If your boss isn't trainable to that degree then I wouldn't hold much hope of developing something s/he can use that is more sophisticated.

You can get much more sophisticated of course, and build a form that lets you do all sorts of drill down scenarios, but for getting started, filter by form should do the trick and ease your frustration considerably.

Reply | Quote

Thanks Wendell,

I have had more success with queries today so maybe I was just brain dead last night. I still have tons to learn. I will see if I can find the book you recommend. Thanks.

I have a question about forms that I will pose on another thread.

Thanks again,

Bret

Reply | Quote

This sounds a bit like a little DB I did for work. What I did was create a form with a number of fields which queries looked to for criteria. And then created different types of report, opened by buttons on the same form. Here’s a cut down version for you to look at.
Good luck

Reply | Quote

If you create a form displaying all your data, you can use Filter By Form, Filter By Selection, Filter Excluding Selectionand Advanced FilterSort…
These options are in Records | Filter. Among them, you can do quite a lot.

But please remember that Access is not a data mining tool, and not really meant as an end-user database. It is a development tool that database programmers use to create database applications.

Reply | Quote

Thanks Hans,

I just get so frustrated. I know I have a wealth of info here and I can’t really use it. Do you know of any end user Data mining software that will do what I want. Doing a web search doesn’t seem to help. I have purchased Crystal reports and find it just as confusing as Access. I have probably wasted thousands of dollars in software and time trying to get the answers I want.

I hear how wonderful Access is and I can believe it, I see a lot of programs based on it. I just wish I could really use the data I have stored.

I will keep struggling, buying books and asking for help.

Thans again, I’ll be back for more help.

Bret

Reply | Quote