Format Report

[Prav]

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-----LOC
10-----PETE-----325----------------------300---------------NORTH----20-----------------EAST-------5-----------------WES
15-----JACK-----30-----------------------20-----------------EAST------10----------------SOUTH------0-----------------na
-

Cheers

 

[Dale Fye]

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.

 

[Michel Walsh]

A little bit different, using a crosstab:

TRANSFORM LAST([count]) AS cell
SELECT id, name, SUM(cell) AS total_count
FROM yourTableName
GROUP BY id, name
PIVOT loc


will give


id name north east south west total_count
10 pete 300 20 5 325
15 jack 20 10 30





Vanderghast, Access MVP