1. ## Query Manual/Book

I'm having a heck of a time building queries. Things that are simple in Excel--totals and sub-totals seem very difficult in Access. Everything I've read explains the operators etc. but they are all short on examples. I would find it quite helpful to have a reference manual with a lot of queries.

Does such an animal exist?

2. ## Re: Query Manual/Book

why don't you just ask questions about queries and/or reports based upon queries?

3. ## Re: Query Manual/Book

In fact, you normally don't calculate subtotals and totals in queries in Access; you do that in reports. Reports can be grouped by one or more fields, and it is very easy to add subtotals to the group headers or footers. Grand totals go into the report footer.

Microsoft has a series of free sample databases available for download, chock full of tables, queries, forms, etc. I have learned a lot from them. See <post:=493,994>post 493,994</post:> for links.

4. ## Re: Query Manual/Book

Thanks Hans
I'll work my way through these. I'm sure they will be helpful. I agree with you that it's pretty easy to create totals in reports.
But quite often I need quick sub-totals and totals and I struggle creating these within a query.

I'm working on a budget template getting ready for 2007 and I want to use a pivot table. I have captured all the expense detail by expense center, expense account, date and description (who the cheque was writtent to) and I am trying to create a pivot table to analyze this data from detail to summary. I was taught (shown) how to construct pivot tables from a query where the query selected the fields and performed calculations-including some totals.
Now I'm struggling to get totals at all levels of the data to calculate in the pivot table. I love the idea of a pivot table. When I meet with my boss and then with our Business Manager I usually have days of work preparing scenarios in Excel and usually I do a powerpoint to help them see my arguments. A pivot table will reduce the workload and give me a level of detail that I've never had at my finger tips. Capturing activity in my department at the most minute level of detail is going to be very powerful in terms of arguing for my 2007 budget.

I find that the one-day Microsoft Access courses I've taken (3 so far) move very quickly and are long on going through one specific example of an Access skill i.e. buiding a query, but are short on principles and theory. If I have the need to build the same query, I'm good, but that is not very realistic. That's why I'd like to review a lot of queries, understanding what the query was designed to do and how it accomplished that task.

I've built quite a few databases since December and eliminated a lot of paper in our office. We are now capturing data that was previouly discarded and our efficiency has increased dramatically-and the 38 people who use these databases love it! I had some resistance at first, but now no one would go back.

I just keep finding new needs, new reports, more things that Access can do and better ways to structure the databases as I keep learning.

Here's an example, I'm the Home Delivery & Distribution Manager at a newspaper in Canada in a community of over of 500,000 people. We launched a magazine in December that is targeted at specific addresses. Our first two issues required days of my time to prepare all the distribution information and the process was very error-prone and I had to do all the admin myself since I was learning as I was going and wasn't in a position to delegate the unknown.

Now we update one table--which takes about 10 minutes or less and the rest is clicking buttons on a switchboard and printing reports. The entire process is done in less than one hour and anyone can do it with very little training. I had nothing to do with the admin of last two issues.
I can honestly say that Access has changed my life!!!!!!
And I'm just getting started.

Thanks again.

5. ## Re: Query Manual/Book

I'm glad you like Access. It takes getting used to, but it is a very powerful tool.

Again. you don't calculate subtotals in the queries themselves. However, if you switch a query to pivot table view, you can add subtotals and the like. Unfortunately, you won't find much in the way of documentation for pivot tables and pivot charts in Access. They are a relatively recent addition (introduced in Access 2002), and most authors of books about Access added a little chapter with an extremely basic example and left it at that.

Frankly, I'm not a fan of pivot tables and pivot charts in Access, but that is a very personal opinion and I'm sure others will disagree with me. I much prefer pivot tables in Excel - the interface seems both easier to use and more powerful to me. If I want to analyze data interactively, I create a pivot table in Excel based on an Access table or query. It is not necessary to import the data into Excel: the first step in the Pivot Table Wizard in Excel lets you specify an external data source.

6. ## Re: Query Manual/Book

Check out

Google :SQL Queries for Mere Mortals"

http://www.amazon.com/gp/product/020143336...glance&n=283155

HTH

John

Thanks John

8. ## Re: Query Manual/Book

Hans
Man are you right.
I did my pivot table in Excel in less than a minute.

Thanks.....

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•