Query Designer

[bkelly]

I am following the book Microsoft Access 2010 Step by Step. On page 225 the instructions are:
Open the Query Designer, and add the Products field list to the diagram pane.
Upon opening the database and selecting Create, the options under Queries are Query Wizard and Query Design, but no Query DesignER. The example database has a table called Products with a number of fields to include ProductID and UnitPrice. After trying and failing both paths let’s presume Query Design is the correct path. Please correct if this is wrong.

What does the author intend by “… add the Products field list to the diagram pane.” The best I can figure is: Select the Products table to display in the Query form, close the Show Table form, then select all the fields of table Product and drop them into the Query form in the bottom of the list producing a query that selects from all of the fields in table Products.
Presuming all that is correct the next step is: In the Products field list, double-click ProductID, and then double click UnitPrice.
What is accomplished by that? All I see is select one field, then another.

I am far enough into probably error paths that I must stop here. Maybe when someone clarifies what the author intended so far the remainder will fall in place.

 

[SydneyGeek]

Yes the dialog refers to Query Design but it's called the Query Designer as well...

When you are adding fields to a query you can drag and drop them from any tables that you have in the view, or you can double-click. Same result.
If you add more than one table to a query, and they are related, you will see a join line between them.
You can also add a pre-existing query to the query you are designing. Sometimes daisy-chaining them is the best way to get the result you need.

Does that answer your questions?

Denis

 

[bkelly]

Hello Denis,
I think that does, but I am having a problem. Amazon has a Kindle for PC program and the book has a Kindle edition. When I wrote the post the next few steps had me working with the sumation function. Things did not turn out right. I proceeded ahead to the following section while waiting for a reply.

When I tried to page back to 225 to compare your statements and the book, I could not get to it. It jumped from 224 to 226 and the instructions that had been puzzeling me are no longer visible. What!!?? So I used the goto function and got to 225 again. The instructions are different and the use of sumation is not present.

That was unexpected. Next time I will close and open the book a few times before posting questions.

This PC Kindle reader has some difficulites. It will not scroll pages and jumps whole pages at a time. When I change focus from Access back to the reader, an errant click can jump multiple pages. I must take care to click some place clearly "non-special" just to restore focus in order to turn the page.

Now that I have dual monitors (for several years) I will do everything I can to avoid a hard copy book, particular a technical book. It is so much easier to show it on a big screen and never have pages flip by themselves. Access on one screen, book on the other, Just Great. But this PC Kindle leaves something to be desired. None the less, much better than paper for this task.

I think I am ok now. Thank you for taking the time to post.

 

[SydneyGeek]

Hello Denis,
I think that does, but I am having a problem. Amazon has a Kindle for PC program and the book has a Kindle edition. When I wrote the post the next few steps had me working with the sumation function. Things did not turn out right. I proceeded ahead to the following section while waiting for a reply.

When I tried to page back to 225 to compare your statements and the book, I could not get to it. It jumped from 224 to 226 and the instructions that had been puzzeling me are no longer visible. What!!?? So I used the goto function and got to 225 again. The instructions are different and the use of sumation is not present.

That was unexpected. Next time I will close and open the book a few times before posting questions.

This PC Kindle reader has some difficulites. It will not scroll pages and jumps whole pages at a time. When I change focus from Access back to the reader, an errant click can jump multiple pages. I must take care to click some place clearly "non-special" just to restore focus in order to turn the page.

Now that I have dual monitors (for several years) I will do everything I can to avoid a hard copy book, particular a technical book. It is so much easier to show it on a big screen and never have pages flip by themselves. Access on one screen, book on the other, Just Great. But this PC Kindle leaves something to be desired. None the less, much better than paper for this task.

I think I am ok now. Thank you for taking the time to post.

Ain't technology grand?

A couple of quick things about using Totals queries.

1. By default, each new field that you add will Group By. You can change this to any of several other functions (Sum, Avg, Max, Min...). The fewer Group By fields you have in the query, the more highly summarised the result will be.

2. If you want to summarise sales by category using Category and Amount fields, you could do this --

• Add Category. Add Amount TWICE.
• Click the Sigma to go convert to a Totals query.
• Leave Category as Group By.
• Change the first Amount field to Sum
• Change the second Amount field to Count.

When you run the query the fields will display as SumOfAmount and CountOfAmount. You can change this in Design view by typing the desired label before the actual field name, followed by a colon.
eg -- Total Sales:Amount and Total Transactions:Amount

Denis