|
|
|
date: Tue, 26 Aug 2008 23:27:00 -0700,
group: microsoft.public.access.queries
back
RE: Format Report
You could create a cross-tab query, although your column headings would read
(ID, Name, Total_Count, North, South, East, West.
1. Start out by creating a new query.
2. Pull in the ID, Name, Count, LOC and Count (yes, add this one twice)
fields to your query.
3. Change the query to a Crosstab query. In the query grid, you will now
have rows labeled Field, Table, Total, and Crosstab. These rows should look
like:
Field: ID Name Count LOC Count
Table:
Total: Group By Group By Sum Group By Sum
Crosstab:Row Row Row Column Value
Actually the table row will contain your table name, and the Crosstab row
will read "Row Heading" or "Column Heading"
If you want the locations to show up in a particular order, you will need to
add a Pivot clause to the end of the query, which should look similar to:
TRANSFORM Sum(Count) AS Count_Total
SELECT ID, Name, Sum(Count) AS Total_Count
FROM tbl_Test
GROUP BY ID, Name
PIVOT LOC In ("North","South","East","West");
BTW, [Name] is a reserved word in Access, and I would recommend not using it
as a field name. You might use [Name_First], [Name_Last], [Name_POC],
[Name_Contact], [Name_Employee], or something like that, but I would strongly
recommend against using reserved words as either table of field names.
--
HTH
Dale
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
"Prav" wrote:
> Hi, how do it translate this data
>
> ID-----NAME-----COUNT-----LOC
> 10-----PETE-----300----------NORTH
> 10-----PETE-----20-----------EAST
> 10-----PETE-----5------------WEST
> 15-----JACK-----20----------EAST
> 15-----JACK-----10----------SOUTH
>
> into this format
>
> ID-----NAME-----TOTAL_COUNT-----COUNT_LOC1-----LOC1-----COUNT_LOC2-----LOC2----COUNT_LOC3-----LOC3
> 10-----PETE-----325----------------------300---------------NORTH----20-----------------EAST-------5-----------------WEST
> 15-----JACK-----30-----------------------20-----------------EAST------10----------------SOUTH------0-----------------na
> -
>
> Cheers
date: Wed, 27 Aug 2008 06:00:01 -0700
author: Dale Fye
|
|
