How to build cross references in a database

[Thunder Chicken]

Hi,
I need some information on passing up a cross references query in a bunch of tables linked with each other. I need the same to view data in rows and columns type of table. The table consist of a series of row and columns which defines a category. The cross reference will help me to get a output for the proper query. I need a proper explanation on the same. If possible I need some examples with syntax for the same category. Thanks in advance.

[Praetor]

Lets take a exmaple of a sample table. Like you have a list of product and the year they launched or earned good same. So the first column is product and the second is year. In the first one add the product list and the second the sale amount. Then to get pass a cross reference query use this syntax - TRANSFORM aggregate function SELECT PIVOT pivot field [IN (value1 [, value2 [, ...]])].

[Katty]

If we have a product table and another table order, we can see in total of orders per year for a given item, as displayed in the table above post. The syntax for this type of query is: TRANSFORM aggregate function SELECT PIVOT pivot field [IN (value1 [, value2 [, ...]])].
Where:
aggregate function - It's a SQL usually aggregate the functions that operates on the particular data.
select statement - It is a SELECT statement.
pivot field - It is the field or expression that you want to use to build the column headers in the query result.
value1, value2 fixed values are used to create the column headers.
To summarize data using a crosstab query, you will need to select the values of fields or expressions and as per the specified column headings, you can vew the data in more compact form as per the query.

[kelfro]

TRANSFORM is optional but if included then it is the first statement in an SQL string. It precedes the SELECT statement that specifies the fields used as row headings and a GROUP BY clause that specifies row grouping. Optionally you can add other clauses like WHERE, that specifies an additional selection or sorting criteria. The values which are returned in pivot field are usually used as a column headings in the query result. For example, using the sales figures for the month of the sale as a pivot on a crosstab query would create 12 columns. Pivot field can restrict to create headings from fixed values (value1, value2) listed in the optional IN clause.

[Zecho]

TRANSFORM clause indicates the value we want to see in columns that really belong to the query, in this case 1996 and 1997, since the other columns are optional. SELECT specifies the name of the optional columns we want to see, in this case Product, Code, Total and Media, giving the name of the field that we want to show in each column or the value thereof. If we include a calculation based on the result shall be based on data from the current row and not to the total data. FROM specifies the source of the data. The first table to be included is that of where we want to extract the data, this table must contain at least three fields, one for the titles of the line, some for column headers and one for calculating the value of the cells.