Pivot table in Report

[Dolphinv4]

Hi,

I have a query created as below:

Date Compnay Account Currency Balance
1/1/08 ABC 12345 AUD 100
1/2/08 ABC 12345 AUD 150
1/3/08 ABC 12345 AUD 175
1/1/08 ABC 55555 NZD 200
1/2/08 ABC 55555 NZD 150
1/3/08 ABC 55555 NZD 175

1/1/08 XYZ 9876 USD 500
1/2/08 XYZ 9876 USD 450
1/3/08 XYZ 9876 USD 300

I want to have in my report, something like a pivot table, that looks like
this:

ABC ABC XYZ
Date 12345 55555 9876
1/1/08 100 200 500
1/2/08 150 150 450
1/3/08 175 175 300

Please tell me how to do it or guide me as to where I can find more to read
up on. Thanks!

Dolphin

 

[John Spencer]

TRANSFORM Sum(Balance) as TheBalance
SELECT T.Date
FROM YourTable As T
GROUP By T.Date
PIVOT Company & Chr(13) & Chr(10) & Account

In query design view
-- Open a new query
-- Select your table
-- Select Query: Crosstab from the menu
-- Add the date field and the balance field and the Company field to the
fields
-- Change Group by to Sum under balance and set crosstab to Value
-- Under Date set Crosstab to Row Heading
-- Under Company set crosstab to Column Heading
-- NOW modify Company to read [Company] & Chr(13) & Chr(10) & [Account]

You now have a query with the requisite information. Using it as a
record source for a a report will prove to be problematic since
depending on the date range specified you may have more or fewer columns
or different columns returned.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Dolphinv4]

Hi,
sorry i'm very new to access...don't understand what do you mean by

TRANSFORM Sum(Balance) as TheBalance
SELECT T.Date
FROM YourTable As T
GROUP By T.Date
PIVOT Company & Chr(13) & Chr(10) & Account

do I have to do something with the above?

Also, how should I do the below? Put in the "Criteria" field?

-- NOW modify Company to read [Company] & Chr(13) & Chr(10) & [Account]

Thanks,
Dolphin

TRANSFORM Sum(Balance) as TheBalance
SELECT T.Date
FROM YourTable As T
GROUP By T.Date
PIVOT Company & Chr(13) & Chr(10) & Account

In query design view
-- Open a new query
-- Select your table
-- Select Query: Crosstab from the menu
-- Add the date field and the balance field and the Company field to the
fields
-- Change Group by to Sum under balance and set crosstab to Value
-- Under Date set Crosstab to Row Heading
-- Under Company set crosstab to Column Heading
-- NOW modify Company to read [Company] & Chr(13) & Chr(10) & [Account]

You now have a query with the requisite information. Using it as a
record source for a a report will prove to be problematic since
depending on the date range specified you may have more or fewer columns
or different columns returned.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi,

I have a query created as below:

Date Compnay Account Currency Balance
1/1/08 ABC 12345 AUD 100
1/2/08 ABC 12345 AUD 150
1/3/08 ABC 12345 AUD 175
1/1/08 ABC 55555 NZD 200
1/2/08 ABC 55555 NZD 150
1/3/08 ABC 55555 NZD 175

1/1/08 XYZ 9876 USD 500
1/2/08 XYZ 9876 USD 450
1/3/08 XYZ 9876 USD 300

I want to have in my report, something like a pivot table, that looks like
this:

ABC ABC XYZ
Date 12345 55555 9876
1/1/08 100 200 500
1/2/08 150 150 450
1/3/08 175 175 300

Please tell me how to do it or guide me as to where I can find more to read
up on. Thanks!

Dolphin

 

[John Spencer]

Use the second part of the instructions to build a query.

OR to use the SQL query string,
--open a new query, don't add any tables
--Switch to SQL view
--Paste the query string into the window
--Replace YourTable with the name of your table and if the field names
are different with the names of your fields. If your field names or
table names contain space surround them with square brackets [].

Or just use the following.
TRANSFORM Sum([Balance]) as TheBalance
SELECT T.[Date]
FROM [YourTable] As T
GROUP By T.[Date]
PIVOT [Company] & Chr(13) & Chr(10) & [Account]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi,
sorry i'm very new to access...don't understand what do you mean by

TRANSFORM Sum(Balance) as TheBalance
SELECT T.Date
FROM YourTable As T
GROUP By T.Date
PIVOT Company & Chr(13) & Chr(10) & Account

do I have to do something with the above?

Also, how should I do the below? Put in the "Criteria" field?

-- NOW modify Company to read [Company] & Chr(13) & Chr(10) & [Account]

Thanks,
Dolphin

TRANSFORM Sum(Balance) as TheBalance
SELECT T.Date
FROM YourTable As T
GROUP By T.Date
PIVOT Company & Chr(13) & Chr(10) & Account

In query design view
-- Open a new query
-- Select your table
-- Select Query: Crosstab from the menu
-- Add the date field and the balance field and the Company field to the
fields
-- Change Group by to Sum under balance and set crosstab to Value
-- Under Date set Crosstab to Row Heading
-- Under Company set crosstab to Column Heading
-- NOW modify Company to read [Company] & Chr(13) & Chr(10) & [Account]

You now have a query with the requisite information. Using it as a
record source for a a report will prove to be problematic since
depending on the date range specified you may have more or fewer columns
or different columns returned.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi,

I have a query created as below:

Date Compnay Account Currency Balance
1/1/08 ABC 12345 AUD 100
1/2/08 ABC 12345 AUD 150
1/3/08 ABC 12345 AUD 175
1/1/08 ABC 55555 NZD 200
1/2/08 ABC 55555 NZD 150
1/3/08 ABC 55555 NZD 175

1/1/08 XYZ 9876 USD 500
1/2/08 XYZ 9876 USD 450
1/3/08 XYZ 9876 USD 300

I want to have in my report, something like a pivot table, that looks like
this:

ABC ABC XYZ
Date 12345 55555 9876
1/1/08 100 200 500
1/2/08 150 150 450
1/3/08 175 175 300

Please tell me how to do it or guide me as to where I can find more to read
up on. Thanks!

Dolphin