MS Access - Data Synthesis
Ms access tutorial
MS Access - Data Summary
Aggregate queries are great if you are just looking for a specific number, but want to summarize information like a summary of spreadsheet type, you may consider trying a crosstab query. When you want to restructure the summary data to make it easier to read and understand, consider using a Crosstab Query .
A Crosstab query is a type of Select query . When you run a crosstab query, the results are displayed in a datasheet. This datasheet has a different structure from other types of datasheets.
The structure of a crosstab query can make it easier to read than a simple select query that displays the same data as shown in the screenshot screen sfollowing.
By far the easiest way to create a crosstab query in Access is to simply use the wizard that comes with Access to build your crosstab query . Now let's go to the Create tab, in the query groups and click on the query wizard.
In the above dialog you can see all kinds of special queries. You can create a simple query wizard like we have done so far from the Design mode. The second is the one we want - the crosstab query which displays the data in a compact format worksheet. Now select the crosstab query and click Ok.
The first screen of the wizard will ask you which table or query contains the fields you want for the results of your array query believeé. Click on the Queries radio button and select qryOrdersInformation - this is the one we created earlier that contains subtotal, sales tax, etc. Click on Next .
We will now examine the fields available from this query. you will be asked to enter the field values you want as row headers. Let's say we want a simple list of all of our different book titles. Now select the book title fields and send it to the field box selected and click on Next .
In the above dialog, the question is what you want to use as column headers and it really depends on what you want to rate. Suppose you want to see our sales by date , choose TransactionDate and click on Next .
The question in the dialog above is a specialized question based on the field that we previously selected because we chose a date ti moi column. It asks us if we want to group our date / time column information by specific interval. Select the year option and click Next .
The next question in the dialog box above asks what number do you want to calculate for each column in the row intersection. In this case we can go by quantity of books sold by selecting quantity (QTY) and Sum in the functions and click Next .
The very last screen of your wizard will ask you what you want to name your query and click Finish to View thisyour request .
We now have a book by book information and also total sales information, like when each of those sales has occurred.