Creating a Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

Arvin Meyer said:
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

1foxi said:
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?
Click to expand...
Click to expand...
 
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

1foxi said:
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

Arvin Meyer said:
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

1foxi said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top