Creating a Report

[Guest]

I want to create a report based on a table that holds the names of all my
students. My table is like this: Year, Firstname, Surname (The year colum
means if the student is Year 1, Year 2 or Year 3)

I want my report to group the students into years so all Year 1s together,
Year 2s etc

BUT i want it to group them so that the report looks like this and the names
are in columns:

Year 1 Year 2
Year 3
Names Follow Names Follow
Names Follow

I have this document in Word, but really its a waste of time if I have all
the data in Access. Does anyone know if this can be done?

 

[Arvin Meyer [MVP]]

I'd first create a query that had all 3 fields in it, concatenating the
names like:

FullName: Firstname & (" " + [Surname])

Then you could create separate queries for each year and make a subreport
for each. Or you might build a crosstab, if there were a row header. There
are several ways it can be done, the easiest (although least elegant) being
4 separate subreports.

 

[Guest]

That got kind of messed up when I tried to illustrate how i wanted it to look.

Year 1 Year 2 Year 3
Names follow Names Follow Names Follow

 

[Guest]

Im not quite sure how to do this. Is this going to leave me with loads of
queries?

Can you tell me the other possibilites also?

Cheers x

I'd first create a query that had all 3 fields in it, concatenating the
names like:

FullName: Firstname & (" " + [Surname])

Then you could create separate queries for each year and make a subreport
for each. Or you might build a crosstab, if there were a row header. There
are several ways it can be done, the easiest (although least elegant) being
4 separate subreports.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I want to create a report based on a table that holds the names of all my
students. My table is like this: Year, Firstname, Surname (The year
colum
means if the student is Year 1, Year 2 or Year 3)

I want my report to group the students into years so all Year 1s together,
Year 2s etc

BUT i want it to group them so that the report looks like this and the
names
are in columns:

Year 1 Year 2
Year 3
Names Follow Names Follow
Names Follow

I have this document in Word, but really its a waste of time if I have all
the data in Access. Does anyone know if this can be done?

 

[Arvin Meyer [MVP]]

I think I did. The crosstab query.

Because you won't have an aggregate for the value, use First.

Again, the simplest way would be to build a query for each year. You could
also use the IIf() function to do all the years in one query, but I think
that would be slower and not yield columns which were continuous. If you
build a query that concatenates the name as I showed previously and uses the
year as a criteria, you'll get a clean list of all the names for that year.
Create a subreport based on the list. Now do the same thing for the next
year, etc. etc. It is also possible to create a set of "rolling" lists,
which just add a new year, and drop off the old one, but you would need to
create an expression for the criteria. Have a look at the DateAdd() function
for use in the expression.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Im not quite sure how to do this. Is this going to leave me with loads
of
queries?

Can you tell me the other possibilites also?

Cheers x

I'd first create a query that had all 3 fields in it, concatenating the
names like:

FullName: Firstname & (" " + [Surname])

Then you could create separate queries for each year and make a subreport
for each. Or you might build a crosstab, if there were a row header.
There
are several ways it can be done, the easiest (although least elegant)
being
4 separate subreports.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I want to create a report based on a table that holds the names of all
my
students. My table is like this: Year, Firstname, Surname (The year
colum
means if the student is Year 1, Year 2 or Year 3)

I want my report to group the students into years so all Year 1s
together,
Year 2s etc

BUT i want it to group them so that the report looks like this and the
names
are in columns:

Year 1 Year 2
Year 3
Names Follow Names Follow
Names Follow

I have this document in Word, but really its a waste of time if I have
all
the data in Access. Does anyone know if this can be done?