query ?

[Alex]

If we have a table named Table1:

Date Product Dollar
------------------------
1/2002 1 $100
2/2002 2 $55
3/2002 5 $40
4/2002 1 $15
5/2002 2 $100
1/2003 1 $20
2/2003 3 $50
3/2003 3 $45
4/2003 6 $30
5/2003 8 $10


What kind of query could I use to get:

Product 2002 2003
-------------------------
1 $115 $20
2 $155 $ 0
3 $ 0 $95
5 $ 50 $ 0
6 $ 0 $30
8 $ 0 $ 0


I'm trying to use the following:

SELECT Product, Sum(Table1.Dollar) As 2002,
Sum(Table1_1.Dollar) As 2003 FROM Table1, Table1_1
FROM Table1 INNER JOIN Table1 AS Table1_1 ON
Table1.Product = Table1_1.Product
WHERE ((Table1.Date >=#1/2002# AND Table1.Date <=#5/2002#)
AND (Table1_1.Date >=#1/2003# AND Table1_1.Date
<=#5/2003#))
GROUP BY Product,Table1.Date, Table1_1.Date;

I would appreciate if anybody could help me out.
Thanks

 

[Allen Browne]

This is easy with a crosstab query:

1. Create a query into this table.

2. Change it to a Crosstab query: Crosstab on Query menu.
This adds a crosstab row to the query.

3. Under Product in the Crosstab row, choose:
Row Heading.

4. Under Dollar in the Crosstab row, enter:
Value
and in the Group By row:
Sum

5. In a fresh column of the field row, enter this expression:
WotYear: Year([Table1].[Date])
In the Crosstab row of this calculated field, choose:
Column Heading

Your query now shows the products on each row, the years as column headings,
and the sum of the dollar amount as the value at each intersection.

If you really do have a field named "Date", consider changing its name
throughout the database. Date is a reserved word in VBA (for the system
date), and so your program may misunderstand what is intended.

 

[Alex]

Thank you very much, Allen. It's working perfectly.
If I have a fiscal year from Aug 1 to Jul 31, how could I
change this expression:
WotYear: Year([Table1].[Date])

to get it by fiscal year?

Thanks

-----Original Message-----
This is easy with a crosstab query:

1. Create a query into this table.

2. Change it to a Crosstab query: Crosstab on Query menu.
This adds a crosstab row to the query.

3. Under Product in the Crosstab row, choose:
Row Heading.

4. Under Dollar in the Crosstab row, enter:
Value
and in the Group By row:
Sum

5. In a fresh column of the field row, enter this expression:
WotYear: Year([Table1].[Date])
In the Crosstab row of this calculated field, choose:
Column Heading

Your query now shows the products on each row, the years as column headings,
and the sum of the dollar amount as the value at each intersection.

If you really do have a field named "Date", consider changing its name
throughout the database. Date is a reserved word in VBA (for the system
date), and so your program may misunderstand what is intended.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

If we have a table named Table1:

Date Product Dollar
------------------------
1/2002 1 $100
2/2002 2 $55
3/2002 5 $40
4/2002 1 $15
5/2002 2 $100
1/2003 1 $20
2/2003 3 $50
3/2003 3 $45
4/2003 6 $30
5/2003 8 $10


What kind of query could I use to get:

Product 2002 2003
-------------------------
1 $115 $20
2 $155 $ 0
3 $ 0 $95
5 $ 50 $ 0
6 $ 0 $30
8 $ 0 $ 0


I'm trying to use the following:

SELECT Product, Sum(Table1.Dollar) As 2002,
Sum(Table1_1.Dollar) As 2003 FROM Table1, Table1_1
FROM Table1 INNER JOIN Table1 AS Table1_1 ON
Table1.Product = Table1_1.Product
WHERE ((Table1.Date >=#1/2002# AND Table1.Date <=#5/2002#)
AND (Table1_1.Date >=#1/2003# AND Table1_1.Date
<=#5/2003#))
GROUP BY Product,Table1.Date, Table1_1.Date;

I would appreciate if anybody could help me out.
Thanks

.

 

[Allen Browne]

You want to subtract 5 months from the date, and consider it to be in that
"year"?

FiscalYear: Year(DateAdd("m", -5, [Table1].[Date]))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you very much, Allen. It's working perfectly.
If I have a fiscal year from Aug 1 to Jul 31, how could I
change this expression:
WotYear: Year([Table1].[Date])

to get it by fiscal year?

Thanks

-----Original Message-----
This is easy with a crosstab query:

1. Create a query into this table.

2. Change it to a Crosstab query: Crosstab on Query menu.
This adds a crosstab row to the query.

3. Under Product in the Crosstab row, choose:
Row Heading.

4. Under Dollar in the Crosstab row, enter:
Value
and in the Group By row:
Sum

5. In a fresh column of the field row, enter this expression:
WotYear: Year([Table1].[Date])
In the Crosstab row of this calculated field, choose:
Column Heading

Your query now shows the products on each row, the years as column headings,
and the sum of the dollar amount as the value at each intersection.

If you really do have a field named "Date", consider changing its name
throughout the database. Date is a reserved word in VBA (for the system
date), and so your program may misunderstand what is intended.

If we have a table named Table1:

Date Product Dollar
------------------------
1/2002 1 $100
2/2002 2 $55
3/2002 5 $40
4/2002 1 $15
5/2002 2 $100
1/2003 1 $20
2/2003 3 $50
3/2003 3 $45
4/2003 6 $30
5/2003 8 $10


What kind of query could I use to get:

Product 2002 2003
-------------------------
1 $115 $20
2 $155 $ 0
3 $ 0 $95
5 $ 50 $ 0
6 $ 0 $30
8 $ 0 $ 0


I'm trying to use the following:

SELECT Product, Sum(Table1.Dollar) As 2002,
Sum(Table1_1.Dollar) As 2003 FROM Table1, Table1_1
FROM Table1 INNER JOIN Table1 AS Table1_1 ON
Table1.Product = Table1_1.Product
WHERE ((Table1.Date >=#1/2002# AND Table1.Date <=#5/2002#)
AND (Table1_1.Date >=#1/2003# AND Table1_1.Date
<=#5/2003#))
GROUP BY Product,Table1.Date, Table1_1.Date;

I would appreciate if anybody could help me out.
Thanks

 

[Alex]

Allen,

Thank you very much again.

Alex

-----Original Message-----
You want to subtract 5 months from the date, and consider it to be in that
"year"?

FiscalYear: Year(DateAdd("m", -5, [Table1].[Date]))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you very much, Allen. It's working perfectly.
If I have a fiscal year from Aug 1 to Jul 31, how could I
change this expression:
WotYear: Year([Table1].[Date])

to get it by fiscal year?

Thanks

-----Original Message-----
This is easy with a crosstab query:

1. Create a query into this table.

2. Change it to a Crosstab query: Crosstab on Query menu.
This adds a crosstab row to the query.

3. Under Product in the Crosstab row, choose:
Row Heading.

4. Under Dollar in the Crosstab row, enter:
Value
and in the Group By row:
Sum

5. In a fresh column of the field row, enter this expression:
WotYear: Year([Table1].[Date])
In the Crosstab row of this calculated field, choose:
Column Heading

Your query now shows the products on each row, the

years
as column headings,

and the sum of the dollar amount as the value at each intersection.

If you really do have a field named "Date", consider changing its name
throughout the database. Date is a reserved word in VBA (for the system
date), and so your program may misunderstand what is intended.

If we have a table named Table1:

Date Product Dollar
------------------------
1/2002 1 $100
2/2002 2 $55
3/2002 5 $40
4/2002 1 $15
5/2002 2 $100
1/2003 1 $20
2/2003 3 $50
3/2003 3 $45
4/2003 6 $30
5/2003 8 $10


What kind of query could I use to get:

Product 2002 2003
-------------------------
1 $115 $20
2 $155 $ 0
3 $ 0 $95
5 $ 50 $ 0
6 $ 0 $30
8 $ 0 $ 0


I'm trying to use the following:

SELECT Product, Sum(Table1.Dollar) As 2002,
Sum(Table1_1.Dollar) As 2003 FROM Table1, Table1_1
FROM Table1 INNER JOIN Table1 AS Table1_1 ON
Table1.Product = Table1_1.Product
WHERE ((Table1.Date >=#1/2002# AND Table1.Date <=#5/2002#)
AND (Table1_1.Date >=#1/2003# AND Table1_1.Date
<=#5/2003#))
GROUP BY Product,Table1.Date, Table1_1.Date;

I would appreciate if anybody could help me out.
Thanks

.