Troubles setting up a report

G

Guest

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
 
G

Guest

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.
 
G

Guest

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");
 
G

Guest

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


KARL DEWEY said:
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


Sophie said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivot/Transpose on a Access Report??? (PLEASE HELP) 1
Setting up a columnar report 2
Combo Box (1st) Populating Text Box (2nd) Field 1
Need help setting up multiple columns in landscape report 7
Setting up a report for a DYMO labeller 1
Charts within a Report 2
Print many columns in a Report 1
Arranging a Report to look like a table 2

Top