Troubles setting up a report

[Sophie]

I'm very new to Access Reports and would really appreciate some clues. Every
week, I'm sent an Access table with entries as shown below. (there are about
60 rows). There are more details, but not relevent here.

EmpNum EmpName Location DayCode
123 Joe Smith East Pant 1
234 Sue Jones East Plant 2
456 Denny Chan Main Building 1
555 Val M. West annex 4

I need to present this data in a report that has 15 columns. The first
three columns show EmpNum, EmpName and Location for DayCode = 1, in order of
EmpNum. The next 3 columns show the data for DayCode = 2, again in order of
EmpNum. The 15 columns show all the data for DayCodes 1 to 5.

I currently cut & paste into an Excel WS, because I don't know how to set
this up in Access. Thanks for any suggestions.

Sophie

 

[KARL DEWEY]

Sounds like you need a crosstab query.

But how do you get 15 columns from EmpNum, EmpName, Location, DayCode1,
DayCode2, DayCode3, DayCode4, DayCode5? I count 8.

 

[KARL DEWEY]

Like this --
TRANSFORM Count(Sophie.DayCode) AS CountOfDayCode
SELECT Sophie.EmpNum, Sophie.EmpName, Sophie.Location
FROM Sophie
GROUP BY Sophie.EmpNum, Sophie.EmpName, Sophie.Location
PIVOT "DayCode " & [DayCode] In ("DayCode 1","DayCode 2","DayCode
3","DayCode 4","DayCode 5");

 

[Sophie]

Karl: interesting. Thanks very much. These techniques are new to me.
--
Thanks
Sophie

Like this --
TRANSFORM Count(Sophie.DayCode) AS CountOfDayCode
SELECT Sophie.EmpNum, Sophie.EmpName, Sophie.Location
FROM Sophie
GROUP BY Sophie.EmpNum, Sophie.EmpName, Sophie.Location
PIVOT "DayCode " & [DayCode] In ("DayCode 1","DayCode 2","DayCode
3","DayCode 4","DayCode 5");

--
KARL DEWEY
Build a little - Test a little

I'm very new to Access Reports and would really appreciate some clues. Every
week, I'm sent an Access table with entries as shown below. (there are about
60 rows). There are more details, but not relevent here.

EmpNum EmpName Location DayCode
123 Joe Smith East Pant 1
234 Sue Jones East Plant 2
456 Denny Chan Main Building 1
555 Val M. West annex 4

I need to present this data in a report that has 15 columns. The first
three columns show EmpNum, EmpName and Location for DayCode = 1, in order of
EmpNum. The next 3 columns show the data for DayCode = 2, again in order of
EmpNum. The 15 columns show all the data for DayCodes 1 to 5.

I currently cut & paste into an Excel WS, because I don't know how to set
this up in Access. Thanks for any suggestions.

Sophie