Printing items only given certain conditions

[Fred Boer]

Hello!

I have a report that prints out "Results Sheets" for a school field day.
Most field day events require tracking first, second, third, and best
results; consequently the report has a column for each of these. However,
some events don't require this, and only need a single column ("Best"). Long
jump might be an example of the former; 1500 metre run, of the latter. So,
ideally, if the event only needs one column, only the "Best" column will
print and vice versa...

Now, the existing form works fine, but today someone asked me, couldn't you
customize the results sheets based on the specific event? I said "sure!". Of
course, as you might guess, I haven't actually a clue how to begin to do
this. Now, I know how to manage forms reasonably well, but I've little
experience with reports. Would someone be so kind as to point me in the
proper direction? I wonder if this is something that even *can* be done
within a report...

Many thanks!
Fred Boer

 

[Duane Hookom]

Provide the table structures, some data, and your desired display in the
report and I bet someone will be able to help you. The first thing you need
is a field in your event table that stores the number of results to print.

 

[Fred Boer]

Dear Mr. Hookom:

Thanks for responding! I have included some more information below. The
application, as it has been developed to this point, does not actually store
the results, it simply creates printed forms which are filled in manually.
So, using the sample data below, a report would be printed for the event of
ShotPut, grouped by Sex and Category. (i.e. Title=Shotput, Sex=Male,
Category=Senior). The report would have columns for 1st, 2nd, 3rd, Best,
Ranking.

The event of 1500 meter run, would only require the "Best" and "Ranking"
columns, and for this event's report, I'd like to automatically have the
"1st", "2nd" and "3rd" columns removed.

Thanks!
Fred


tblStudent tblStudentEvent (Transaction Table)

StudentID StudentEventID
LastName StudentID
FirstName EventID
Sex
CategoryID (Lookup to tblCategory)
VisionID (Lookup to tblVision)

tblEvent tblCategory tblVision

EventID CategoryID SexID
Event Category Male

Data:

StudentID: 568 LastName: Conrad FirstName: Jeff CategoryID: 3 (Lookup to
tblCategory....)
EventID: 12 Event: Shotput
EventID: 13 Event: 1500 Metre run
CategoryID: 3 Category: Senior
StudentEventID: 6 StudentID: 568 EventID: 12
StudentEventID: 7 StudentID: 568 EventID: 13

 

[Duane Hookom]

Apparently the report doesn't require tblStudent , tblVision, or
tblStudentEvent (right?)

I expected you to provide a sample layout of the report like it would
appear. I also suggested you add a field to a table that defines the number
of results you want printed.

 

[Fred Boer]

Hello, again!

My apologies for not providing you with the information you needed.. The
report actually does need tblStudent, tblVision and tblStudentEvent, so I
have obviously not done a good job of explaining myself. Please bear with me
and I'll try again:

I also suggested you add a field to a table that defines the number
of results you want printed.

I should have addressed this: I am sorry, but I do not understand this
suggestion. There are 15 events; 4 categories (Midget, Junior, Intermediate,
Senior); 2 sexes.. So, for example, I need a printout/result sheet that
lists all intermediate females who are competing in the 1500 meter run,
another printout/result sheet for all intermediate males who are competing
in 1500 meter run, and so on, for all the possible combinations.

As to the layout.. I'm not sure how to do that in a text post, but here
goes...

Page Header: Field Day: 2004
Event Header: Event label; tblEvent.Event
Category Header: Category label; tblCategory.Category
Sex Header: Sex label; tblStudent.Sex, labels to display "1st","2nd","3rd",
etc...
Detail: tblStudent.Fullname, tblVision.Vision, Vertical lines positioned
below "1st", "2nd", 3rd", etc... to create the columns
Sex Footer: Empty
Page Footer: Copyright notice label

So, it looks like:

FIELD DAY: 2004

Event: 60m
Category: Intermediate
Sex: Female 1st 2nd 3rd Best Place
Student Name/Vision
Doe, Jane B1 | | | | |
|
Harry, Deb B2 | | | | |
|
Smith, Jane B1 | | | | |
|

....................New Page..........New Page.............New Page....

FIELD DAY: 2004

Event: 60m
Category: Intermediate
Sex: Male 1st 2nd 3rd Best Place
Student Name/Vision
Doe, Joe B2 | | | | |
|
Harry, Bob B1 | | | | |
|
Smith, Ted B1 | | | | |
|

....................New Page..........New Page.............New Page....

FIELD DAY: 2004

Event: Shot Put
Category: Intermediate
Sex: Female 1st 2nd 3rd Best Place
Student Name/Vision:
Doe, Jane B1 | | | | |
|
Harry, Deb B2 | | | | |
|
Smith, Jane B1 | | | | |
|


The recordsource for the report is a query. The SQL for the query is
reproduced below:

SELECT tblEvent.Event, tblCategory.Category, tblSex.Sex,
tblStudent.FirstName, tblStudent.LastName, [LastName] & ", " & [FirstName]
AS StudentName
FROM tblSex INNER JOIN ((tblCategory INNER JOIN tblStudent ON
tblCategory.Category_ID = tblStudent.Category) INNER JOIN (tblEvent INNER
JOIN tblStudentEvent ON tblEvent.Event_ID = tblStudentEvent.Event_ID) ON
tblStudent.Student_ID = tblStudentEvent.Student_ID) ON tblSex.Sex_ID =
tblStudent.Sex
ORDER BY tblEvent.Event, tblCategory.Category, tblSex.Sex, [LastName] & ", "
& [FirstName];

I hope that this helps!
Thanks very much!
Fred

 

[Duane Hookom]

Great reply! Your layout is exactly the type of information that takes the
guess-work out of answering.
You need to add a field [Attempts] to tblEvent to store the number of
"attempt" columns. A value of 3 would display 1st, 2nd, and 3rd while a
value of 0 would not show any of these columns.
Add a text box in the section containing the column labels
Name: txtAttempts
Control Source: [Attempts]
Then assuming you name the column labels for 1st, 2nd, and 3rd as lbl1st,
lbl2nd, and lbl3rd, you can use code in the On Format event of the section
containing the labels:
Me.lbl1st.Visible = txtAttempts>=1
Me.lbl2nd.Visible = txtAttempts>=2
Me.lbl3rd.Visible = txtAttempts>=3
You can do something similar in the detail section to hide or display the
"|".

--
Duane Hookom
MS Access MVP
--

Hello, again!

My apologies for not providing you with the information you needed.. The
report actually does need tblStudent, tblVision and tblStudentEvent, so I
have obviously not done a good job of explaining myself. Please bear with me
and I'll try again:

I also suggested you add a field to a table that defines the number
of results you want printed.

I should have addressed this: I am sorry, but I do not understand this
suggestion. There are 15 events; 4 categories (Midget, Junior, Intermediate,
Senior); 2 sexes.. So, for example, I need a printout/result sheet that
lists all intermediate females who are competing in the 1500 meter run,
another printout/result sheet for all intermediate males who are competing
in 1500 meter run, and so on, for all the possible combinations.

As to the layout.. I'm not sure how to do that in a text post, but here
goes...

Page Header: Field Day: 2004
Event Header: Event label; tblEvent.Event
Category Header: Category label; tblCategory.Category
Sex Header: Sex label; tblStudent.Sex, labels to display "1st","2nd","3rd",
etc...
Detail: tblStudent.Fullname, tblVision.Vision, Vertical lines positioned
below "1st", "2nd", 3rd", etc... to create the columns
Sex Footer: Empty
Page Footer: Copyright notice label

So, it looks like:

FIELD DAY: 2004

Event: 60m
Category: Intermediate
Sex: Female 1st 2nd 3rd Best Place
Student Name/Vision
Doe, Jane B1 | | | | |
|
Harry, Deb B2 | | | | |
|
Smith, Jane B1 | | | | |
|

...................New Page..........New Page.............New Page....

FIELD DAY: 2004

Event: 60m
Category: Intermediate
Sex: Male 1st 2nd 3rd Best Place
Student Name/Vision
Doe, Joe B2 | | | | |
|
Harry, Bob B1 | | | | |
|
Smith, Ted B1 | | | | |
|

...................New Page..........New Page.............New Page....

FIELD DAY: 2004

Event: Shot Put
Category: Intermediate
Sex: Female 1st 2nd 3rd Best Place
Student Name/Vision:
Doe, Jane B1 | | | | |
|
Harry, Deb B2 | | | | |
|
Smith, Jane B1 | | | | |
|


The recordsource for the report is a query. The SQL for the query is
reproduced below:

SELECT tblEvent.Event, tblCategory.Category, tblSex.Sex,
tblStudent.FirstName, tblStudent.LastName, [LastName] & ", " & [FirstName]
AS StudentName
FROM tblSex INNER JOIN ((tblCategory INNER JOIN tblStudent ON
tblCategory.Category_ID = tblStudent.Category) INNER JOIN (tblEvent INNER
JOIN tblStudentEvent ON tblEvent.Event_ID = tblStudentEvent.Event_ID) ON
tblStudent.Student_ID = tblStudentEvent.Student_ID) ON tblSex.Sex_ID =
tblStudent.Sex
ORDER BY tblEvent.Event, tblCategory.Category, tblSex.Sex, [LastName] & ", "
& [FirstName];

I hope that this helps!
Thanks very much!
Fred

Apparently the report doesn't require tblStudent , tblVision, or
tblStudentEvent (right?)

I expected you to provide a sample layout of the report like it would
appear. I also suggested you add a field to a table that defines the number
of results you want printed.

--
Duane Hookom
MS Access MVP


event
of (Lookup
to column
will

begin

to in
the

 

[Fred Boer]

Thanks so much! Your response is clear and I think I understand what to do
now. It seems much less complicated than I thought it would be... I'll give
it a try as soon as I can.

And next time, I'll try to compose a better formatted question the first
time!

Cheers!
Fred

Great reply! Your layout is exactly the type of information that takes the
guess-work out of answering.
You need to add a field [Attempts] to tblEvent to store the number of
"attempt" columns. A value of 3 would display 1st, 2nd, and 3rd while a
value of 0 would not show any of these columns.
Add a text box in the section containing the column labels
Name: txtAttempts
Control Source: [Attempts]
Then assuming you name the column labels for 1st, 2nd, and 3rd as lbl1st,
lbl2nd, and lbl3rd, you can use code in the On Format event of the section
containing the labels:
Me.lbl1st.Visible = txtAttempts>=1
Me.lbl2nd.Visible = txtAttempts>=2
Me.lbl3rd.Visible = txtAttempts>=3
You can do something similar in the detail section to hide or display the
"|".

--
Duane Hookom
MS Access MVP
--

Hello, again!

My apologies for not providing you with the information you needed.. The
report actually does need tblStudent, tblVision and tblStudentEvent, so I
have obviously not done a good job of explaining myself. Please bear

with

me
and I'll try again:

I also suggested you add a field to a table that defines the number
of results you want printed.

I should have addressed this: I am sorry, but I do not understand this
suggestion. There are 15 events; 4 categories (Midget, Junior, Intermediate,
Senior); 2 sexes.. So, for example, I need a printout/result sheet that
lists all intermediate females who are competing in the 1500 meter run,
another printout/result sheet for all intermediate males who are competing
in 1500 meter run, and so on, for all the possible combinations.

As to the layout.. I'm not sure how to do that in a text post, but here
goes...

Page Header: Field Day: 2004
Event Header: Event label; tblEvent.Event
Category Header: Category label; tblCategory.Category
Sex Header: Sex label; tblStudent.Sex, labels to display "1st","2nd","3rd",
etc...
Detail: tblStudent.Fullname, tblVision.Vision, Vertical lines positioned
below "1st", "2nd", 3rd", etc... to create the columns
Sex Footer: Empty
Page Footer: Copyright notice label

So, it looks like:

FIELD DAY: 2004

Event: 60m
Category: Intermediate
Sex: Female 1st 2nd 3rd Best Place
Student Name/Vision
Doe, Jane B1 | | | | |
|
Harry, Deb B2 | | | | |
|
Smith, Jane B1 | | | | |
|

...................New Page..........New Page.............New Page....

FIELD DAY: 2004

Event: 60m
Category: Intermediate
Sex: Male 1st 2nd 3rd Best Place
Student Name/Vision
Doe, Joe B2 | | | | |
|
Harry, Bob B1 | | | | |
|
Smith, Ted B1 | | | | |
|

...................New Page..........New Page.............New Page....

FIELD DAY: 2004

Event: Shot Put
Category: Intermediate
Sex: Female 1st 2nd 3rd Best Place
Student Name/Vision:
Doe, Jane B1 | | | | |
|
Harry, Deb B2 | | | | |
|
Smith, Jane B1 | | | | |
|


The recordsource for the report is a query. The SQL for the query is
reproduced below:

SELECT tblEvent.Event, tblCategory.Category, tblSex.Sex,
tblStudent.FirstName, tblStudent.LastName, [LastName] & ", " & [FirstName]
AS StudentName
FROM tblSex INNER JOIN ((tblCategory INNER JOIN tblStudent ON
tblCategory.Category_ID = tblStudent.Category) INNER JOIN (tblEvent INNER
JOIN tblStudentEvent ON tblEvent.Event_ID = tblStudentEvent.Event_ID) ON
tblStudent.Student_ID = tblStudentEvent.Student_ID) ON tblSex.Sex_ID =
tblStudent.Sex
ORDER BY tblEvent.Event, tblCategory.Category, tblSex.Sex, [LastName] &

",

"
& [FirstName];

I hope that this helps!
Thanks very much!
Fred

Apparently the report doesn't require tblStudent , tblVision, or
tblStudentEvent (right?)

I expected you to provide a sample layout of the report like it would
appear. I also suggested you add a field to a table that defines the number
of results you want printed.

--
Duane Hookom
MS Access MVP


Dear Mr. Hookom:

Thanks for responding! I have included some more information below. The
application, as it has been developed to this point, does not actually
store
the results, it simply creates printed forms which are filled in manually.
So, using the sample data below, a report would be printed for the event
of
ShotPut, grouped by Sex and Category. (i.e. Title=Shotput, Sex=Male,
Category=Senior). The report would have columns for 1st, 2nd, 3rd, Best,
Ranking.

The event of 1500 meter run, would only require the "Best" and "Ranking"
columns, and for this event's report, I'd like to automatically have the
"1st", "2nd" and "3rd" columns removed.

Thanks!
Fred


tblStudent tblStudentEvent (Transaction Table)

StudentID StudentEventID
LastName StudentID
FirstName EventID
Sex
CategoryID (Lookup to tblCategory)
VisionID (Lookup to tblVision)

tblEvent tblCategory tblVision

EventID CategoryID SexID
Event Category Male

Data:

StudentID: 568 LastName: Conrad FirstName: Jeff CategoryID: 3 (Lookup
to
tblCategory....)
EventID: 12 Event: Shotput
EventID: 13 Event: 1500 Metre run
CategoryID: 3 Category: Senior
StudentEventID: 6 StudentID: 568 EventID: 12
StudentEventID: 7 StudentID: 568 EventID: 13




Provide the table structures, some data, and your desired display

in
the

report and I bet someone will be able to help you. The first thing you
need
is a field in your event table that stores the number of results to
print.

--
Duane Hookom
MS Access MVP


Hello!

I have a report that prints out "Results Sheets" for a school field
day.
Most field day events require tracking first, second, third, and best
results; consequently the report has a column for each of these.
However,
some events don't require this, and only need a single column
("Best").
Long
jump might be an example of the former; 1500 metre run, of the latter.
So,
ideally, if the event only needs one column, only the "Best" column
will
print and vice versa...

Now, the existing form works fine, but today someone asked me,
couldn't
you
customize the results sheets based on the specific event? I said
"sure!".
Of
course, as you might guess, I haven't actually a clue how to

begin

to
do
this. Now, I know how to manage forms reasonably well, but I've little
experience with reports. Would someone be so kind as to point me in
the
proper direction? I wonder if this is something that even *can* be
done
within a report...

Many thanks!
Fred Boer

 

[Fred Boer]

Just thought you might like to know that I've successfully implemented your
solution, and it is working great!

Thanks again!

Fred

Great reply! Your layout is exactly the type of information that takes the
guess-work out of answering.
You need to add a field [Attempts] to tblEvent to store the number of
"attempt" columns. A value of 3 would display 1st, 2nd, and 3rd while a
value of 0 would not show any of these columns.
Add a text box in the section containing the column labels
Name: txtAttempts
Control Source: [Attempts]
Then assuming you name the column labels for 1st, 2nd, and 3rd as lbl1st,
lbl2nd, and lbl3rd, you can use code in the On Format event of the section
containing the labels:
Me.lbl1st.Visible = txtAttempts>=1
Me.lbl2nd.Visible = txtAttempts>=2
Me.lbl3rd.Visible = txtAttempts>=3
You can do something similar in the detail section to hide or display the
"|".

--
Duane Hookom
MS Access MVP
--

Hello, again!

My apologies for not providing you with the information you needed.. The
report actually does need tblStudent, tblVision and tblStudentEvent, so I
have obviously not done a good job of explaining myself. Please bear

with

me
and I'll try again:

I also suggested you add a field to a table that defines the number
of results you want printed.

I should have addressed this: I am sorry, but I do not understand this
suggestion. There are 15 events; 4 categories (Midget, Junior, Intermediate,
Senior); 2 sexes.. So, for example, I need a printout/result sheet that
lists all intermediate females who are competing in the 1500 meter run,
another printout/result sheet for all intermediate males who are competing
in 1500 meter run, and so on, for all the possible combinations.

As to the layout.. I'm not sure how to do that in a text post, but here
goes...

Page Header: Field Day: 2004
Event Header: Event label; tblEvent.Event
Category Header: Category label; tblCategory.Category
Sex Header: Sex label; tblStudent.Sex, labels to display "1st","2nd","3rd",
etc...
Detail: tblStudent.Fullname, tblVision.Vision, Vertical lines positioned
below "1st", "2nd", 3rd", etc... to create the columns
Sex Footer: Empty
Page Footer: Copyright notice label

So, it looks like:

FIELD DAY: 2004

Event: 60m
Category: Intermediate
Sex: Female 1st 2nd 3rd Best Place
Student Name/Vision
Doe, Jane B1 | | | | |
|
Harry, Deb B2 | | | | |
|
Smith, Jane B1 | | | | |
|

...................New Page..........New Page.............New Page....

FIELD DAY: 2004

Event: 60m
Category: Intermediate
Sex: Male 1st 2nd 3rd Best Place
Student Name/Vision
Doe, Joe B2 | | | | |
|
Harry, Bob B1 | | | | |
|
Smith, Ted B1 | | | | |
|

...................New Page..........New Page.............New Page....

FIELD DAY: 2004

Event: Shot Put
Category: Intermediate
Sex: Female 1st 2nd 3rd Best Place
Student Name/Vision:
Doe, Jane B1 | | | | |
|
Harry, Deb B2 | | | | |
|
Smith, Jane B1 | | | | |
|


The recordsource for the report is a query. The SQL for the query is
reproduced below:

SELECT tblEvent.Event, tblCategory.Category, tblSex.Sex,
tblStudent.FirstName, tblStudent.LastName, [LastName] & ", " & [FirstName]
AS StudentName
FROM tblSex INNER JOIN ((tblCategory INNER JOIN tblStudent ON
tblCategory.Category_ID = tblStudent.Category) INNER JOIN (tblEvent INNER
JOIN tblStudentEvent ON tblEvent.Event_ID = tblStudentEvent.Event_ID) ON
tblStudent.Student_ID = tblStudentEvent.Student_ID) ON tblSex.Sex_ID =
tblStudent.Sex
ORDER BY tblEvent.Event, tblCategory.Category, tblSex.Sex, [LastName] &

",

"
& [FirstName];

I hope that this helps!
Thanks very much!
Fred

Apparently the report doesn't require tblStudent , tblVision, or
tblStudentEvent (right?)

I expected you to provide a sample layout of the report like it would
appear. I also suggested you add a field to a table that defines the number
of results you want printed.

--
Duane Hookom
MS Access MVP


Dear Mr. Hookom:

Thanks for responding! I have included some more information below. The
application, as it has been developed to this point, does not actually
store
the results, it simply creates printed forms which are filled in manually.
So, using the sample data below, a report would be printed for the event
of
ShotPut, grouped by Sex and Category. (i.e. Title=Shotput, Sex=Male,
Category=Senior). The report would have columns for 1st, 2nd, 3rd, Best,
Ranking.

The event of 1500 meter run, would only require the "Best" and "Ranking"
columns, and for this event's report, I'd like to automatically have the
"1st", "2nd" and "3rd" columns removed.

Thanks!
Fred


tblStudent tblStudentEvent (Transaction Table)

StudentID StudentEventID
LastName StudentID
FirstName EventID
Sex
CategoryID (Lookup to tblCategory)
VisionID (Lookup to tblVision)

tblEvent tblCategory tblVision

EventID CategoryID SexID
Event Category Male

Data:

StudentID: 568 LastName: Conrad FirstName: Jeff CategoryID: 3 (Lookup
to
tblCategory....)
EventID: 12 Event: Shotput
EventID: 13 Event: 1500 Metre run
CategoryID: 3 Category: Senior
StudentEventID: 6 StudentID: 568 EventID: 12
StudentEventID: 7 StudentID: 568 EventID: 13




Provide the table structures, some data, and your desired display

in
the

report and I bet someone will be able to help you. The first thing you
need
is a field in your event table that stores the number of results to
print.

--
Duane Hookom
MS Access MVP


Hello!

I have a report that prints out "Results Sheets" for a school field
day.
Most field day events require tracking first, second, third, and best
results; consequently the report has a column for each of these.
However,
some events don't require this, and only need a single column
("Best").
Long
jump might be an example of the former; 1500 metre run, of the latter.
So,
ideally, if the event only needs one column, only the "Best" column
will
print and vice versa...

Now, the existing form works fine, but today someone asked me,
couldn't
you
customize the results sheets based on the specific event? I said
"sure!".
Of
course, as you might guess, I haven't actually a clue how to

begin

to
do
this. Now, I know how to manage forms reasonably well, but I've little
experience with reports. Would someone be so kind as to point me in
the
proper direction? I wonder if this is something that even *can* be
done
within a report...

Many thanks!
Fred Boer

 

[Duane Hookom]

Thanks for the reply. It's good to hear that your report is working
correctly.

--
Duane Hookom
MS Access MVP
--

Just thought you might like to know that I've successfully implemented your
solution, and it is working great!

Thanks again!

Fred

Great reply! Your layout is exactly the type of information that takes the
guess-work out of answering.
You need to add a field [Attempts] to tblEvent to store the number of
"attempt" columns. A value of 3 would display 1st, 2nd, and 3rd while a
value of 0 would not show any of these columns.
Add a text box in the section containing the column labels
Name: txtAttempts
Control Source: [Attempts]
Then assuming you name the column labels for 1st, 2nd, and 3rd as lbl1st,
lbl2nd, and lbl3rd, you can use code in the On Format event of the section
containing the labels:
Me.lbl1st.Visible = txtAttempts>=1
Me.lbl2nd.Visible = txtAttempts>=2
Me.lbl3rd.Visible = txtAttempts>=3
You can do something similar in the detail section to hide or display the
"|".

--
Duane Hookom
MS Access MVP
--

Hello, again!

My apologies for not providing you with the information you needed.. The
report actually does need tblStudent, tblVision and tblStudentEvent,

so

I

have obviously not done a good job of explaining myself. Please bear

with

me
and I'll try again:

I also suggested you add a field to a table that defines the number
of results you want printed.

I should have addressed this: I am sorry, but I do not understand this
suggestion. There are 15 events; 4 categories (Midget, Junior, Intermediate,
Senior); 2 sexes.. So, for example, I need a printout/result sheet that
lists all intermediate females who are competing in the 1500 meter run,
another printout/result sheet for all intermediate males who are competing
in 1500 meter run, and so on, for all the possible combinations.

As to the layout.. I'm not sure how to do that in a text post, but here
goes...

Page Header: Field Day: 2004
Event Header: Event label; tblEvent.Event
Category Header: Category label; tblCategory.Category
Sex Header: Sex label; tblStudent.Sex, labels to display "1st","2nd","3rd",
etc...
Detail: tblStudent.Fullname, tblVision.Vision, Vertical lines positioned
below "1st", "2nd", 3rd", etc... to create the columns
Sex Footer: Empty
Page Footer: Copyright notice label

So, it looks like:

FIELD DAY: 2004

Event: 60m
Category: Intermediate
Sex: Female 1st 2nd 3rd Best Place
Student Name/Vision
Doe, Jane B1 | | | | |
|
Harry, Deb B2 | | | | |
|
Smith, Jane B1 | | | | |
|

...................New Page..........New Page.............New Page....

FIELD DAY: 2004

Event: 60m
Category: Intermediate
Sex: Male 1st 2nd 3rd Best Place
Student Name/Vision
Doe, Joe B2 | | | | |
|
Harry, Bob B1 | | | | |
|
Smith, Ted B1 | | | | |
|

...................New Page..........New Page.............New Page....

FIELD DAY: 2004

Event: Shot Put
Category: Intermediate
Sex: Female 1st 2nd 3rd Best Place
Student Name/Vision:
Doe, Jane B1 | | | | |
|
Harry, Deb B2 | | | | |
|
Smith, Jane B1 | | | | |
|


The recordsource for the report is a query. The SQL for the query is
reproduced below:

SELECT tblEvent.Event, tblCategory.Category, tblSex.Sex,
tblStudent.FirstName, tblStudent.LastName, [LastName] & ", " & [FirstName]
AS StudentName
FROM tblSex INNER JOIN ((tblCategory INNER JOIN tblStudent ON
tblCategory.Category_ID = tblStudent.Category) INNER JOIN (tblEvent INNER
JOIN tblStudentEvent ON tblEvent.Event_ID = tblStudentEvent.Event_ID) ON
tblStudent.Student_ID = tblStudentEvent.Student_ID) ON tblSex.Sex_ID =
tblStudent.Sex
ORDER BY tblEvent.Event, tblCategory.Category, tblSex.Sex, [LastName]

&

",

"
& [FirstName];

I hope that this helps!
Thanks very much!
Fred

Apparently the report doesn't require tblStudent , tblVision, or
tblStudentEvent (right?)

I expected you to provide a sample layout of the report like it would
appear. I also suggested you add a field to a table that defines the
number
of results you want printed.

--
Duane Hookom
MS Access MVP


Dear Mr. Hookom:

Thanks for responding! I have included some more information

below.
The

application, as it has been developed to this point, does not actually
store
the results, it simply creates printed forms which are filled in
manually.
So, using the sample data below, a report would be printed for the event
of
ShotPut, grouped by Sex and Category. (i.e. Title=Shotput, Sex=Male,
Category=Senior). The report would have columns for 1st, 2nd, 3rd, Best,
Ranking.

The event of 1500 meter run, would only require the "Best" and "Ranking"
columns, and for this event's report, I'd like to automatically

have
the

"1st", "2nd" and "3rd" columns removed.

Thanks!
Fred


tblStudent tblStudentEvent (Transaction Table)

StudentID StudentEventID
LastName StudentID
FirstName EventID
Sex
CategoryID (Lookup to tblCategory)
VisionID (Lookup to tblVision)

tblEvent tblCategory tblVision

EventID CategoryID SexID
Event Category Male

Data:

StudentID: 568 LastName: Conrad FirstName: Jeff CategoryID: 3 (Lookup
to
tblCategory....)
EventID: 12 Event: Shotput
EventID: 13 Event: 1500 Metre run
CategoryID: 3 Category: Senior
StudentEventID: 6 StudentID: 568 EventID: 12
StudentEventID: 7 StudentID: 568 EventID: 13




Provide the table structures, some data, and your desired

display

in thing
you me
in

*can*

be