If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Grouping on certain years - help needed please
Hi
I have a database in Access 2007 that records details of sports clubs and historical results. Records start in 1929 to present date with a break for World War 2 I have reports showing League tables etc. One report shows all the clubs history on one page with club details at the top, then line by line history of results (Played/Won/Drew/For/ Against etc) However, I would like to group these latter results in pre-war (1929-1939) and post-war (1946- ) periods (all on the same report) I'd like advice please as how best to do this Many thanks Neil |
#2
|
|||
|
|||
=
|
#3
|
|||
|
|||
=
On 26 Apr, 01:33, Marshall Barton wrote:
wrote: I have a database in Access 2007 that records details of sports clubs and historical results. Records start in 1929 to present date with a break for World War 2 I have reports showing League tables etc. One report shows all the clubs history on one page with club details at the top, then line by line history of results (Played/Won/Drew/For/ Against etc) However, I would like to group these latter results in pre-war (1929-1939) and post-war (1946- ) periods (all on the same report) Assuming you have no records for 1940-1945, you can just set the Sorting and Grouping expression to =yearfield 1945 In the group header section text box: =IIf(yearfield 1945,"Pre", "Post") & " War" -- Marsh MVP [MS Access] Thanks Perhaps you can advise a little more. I have managed to create 2 reports, one post and one pre war, but I can't figure out how to have both on the same report so that I ge tsomething like Team XYZ Pre-War results 1930 1931 Post-War results 1946 1947 ..... Thanks Neil |
#4
|
|||
|
|||
=
|
#5
|
|||
|
|||
=
On 28 Apr, 17:23, Marshall Barton wrote:
wrote: On 26 Apr, 01:33, Marshall Barton wrote: wrote: I have a database in Access 2007 that records details of sports clubs and historical results. Records start in 1929 to present date with a break for World War 2 I have reports showing League tables etc. One report shows all the clubs history on one page with club details at the top, then line by line history of results (Played/Won/Drew/For/ Against etc) However, I would like to group these latter results in pre-war (1929-1939) and post-war (1946- ) periods (all on the same report) Assuming you have no records for 1940-1945, you can just set the Sorting and Grouping expression to =yearfield 1945 In the group header section text box: =IIf(yearfield 1945,"Pre", "Post") & " War" Perhaps you can advise a little more. I have managed to create 2 reports, one post and one pre war, but I can't figure out how to have both on the same report so that I ge tsomething like Team XYZ Pre-War results 1930 1931 Post-War results 1946 1947 But that's exctly what my previous post does. Did you try it? If so, what about it didn't do what you want? If not, why not. -- Marsh MVP [MS Access]- Hide quoted text - - Show quoted text - OK, I'll explain what I have and did in response to your original post Marsh. It's Access 2007 by the way. My report has Report Header (Contains some explanation text) Page Header (Blank) Team Header (Column Headings) Detail (Obviously where the year by year records print) Team Footer Rather than 'yearfield' I have 'Season' I put a text box in to the Team Header with the IIf statement that you indicated (but modified to my table name) Then in the Group, Sort and Total box at the bottom in design mode I tried to change 'Sort by Season' to Sort by expression using the (again modified) expression you gave. I get the following error when I run the preview 'Data type mismatch in Expression' I'm obviously not putting something in the correct place! Neil |
#6
|
|||
|
|||
=
On 28 Apr, 20:56, "
wrote: On 28 Apr, 17:23, Marshall Barton wrote: wrote: On 26 Apr, 01:33, Marshall Barton wrote: wrote: I have a database in Access 2007 that records details of sports clubs and historical results. Records start in 1929 to present date with a break for World War 2 I have reports showing League tables etc. One report shows all the clubs history on one page with club details at the top, then line by line history of results (Played/Won/Drew/For/ Against etc) However, I would like to group these latter results in pre-war (1929-1939) and post-war (1946- ) periods (all on the same report) Assuming you have no records for 1940-1945, you can just set the Sorting and Grouping expression to =yearfield 1945 In the group header section text box: =IIf(yearfield 1945,"Pre", "Post") & " War" Perhaps you can advise a little more. I have managed to create 2 reports, one post and one pre war, but I can't figure out how to have both on the same report so that I ge tsomething like Team XYZ Pre-War results 1930 1931 Post-War results 1946 1947 But that's exctly what my previous post does. Did you try it? If so, what about it didn't do what you want? If not, why not. -- Marsh MVP [MS Access]- Hide quoted text - - Show quoted text - OK, I'll explain what I have and did in response to your original post Marsh. It's Access 2007 by the way. My report has Report Header (Contains some explanation text) Page Header (Blank) Team Header (Column Headings) Detail (Obviously where the year by year records print) Team Footer Rather than 'yearfield' I have 'Season' I put a text box in to the Team Header with the IIf statement that you indicated (but modified to my table name) Then in the Group, Sort and Total box at the bottom in design mode I tried to change 'Sort by Season' to Sort by expression using the (again modified) expression you gave. I get the following error when I run the preview 'Data type mismatch in Expression' I'm obviously not putting something in the correct place! Neil- Hide quoted text - - Show quoted text - ....just to add, the report uses Group on Team Sort by Season |
#7
|
|||
|
|||
=
|
#8
|
|||
|
|||
=
On 28 Apr, 23:16, Marshall Barton wrote:
wrote: On 28 Apr, 17:23, Marshall Barton wrote: wrote: On 26 Apr, 01:33, Marshall Barton wrote: wrote: I have a database in Access 2007 that records details of sports clubs and historical results. Records start in 1929 to present date with a break for World War 2 I have reports showing League tables etc. One report shows all the clubs history on one page with club details at the top, then line by line history of results (Played/Won/Drew/For/ Against etc) However, I would like to group these latter results in pre-war (1929-1939) and post-war (1946- ) periods (all on the same report) Assuming you have no records for 1940-1945, you can just set the Sorting and Grouping expression to =yearfield 1945 In the group header section text box: =IIf(yearfield 1945,"Pre", "Post") & " War" Perhaps you can advise a little more. I have managed to create 2 reports, one post and one pre war, but I can't figure out how to have both on the same report so that I ge tsomething like Team XYZ Pre-War results 1930 1931 Post-War results 1946 1947 But that's exctly what my previous post does. Did you try it? If so, what about it didn't do what you want? If not, why not. OK, I'll explain what I have and did in response to your original post Marsh. It's Access 2007 by the way. My report has Report Header (Contains some explanation text) Page Header (Blank) Team Header (Column Headings) Detail (Obviously where the year by year records print) Team Footer Rather than 'yearfield' I have 'Season' I put a text box in to the Team Header with the IIf statement that you indicated (but modified to my table name) Then in the Group, Sort and Total box at the bottom in design mode I tried to change 'Sort by Season' to Sort by expression using the (again modified) expression you gave. I get the following error when I run the preview 'Data type mismatch in Expression' I guess I wasn't clear enpugh. You were supposed to add the expression: =Season 1945 to Sorting and Grouping between Team and Season. Specify that you want the group header for the new group. Now use the expression: =IIf(Season 1945,"Pre", "Post") & " War" in a text box in the new group header section. -- Marsh MVP [MS Access]- Hide quoted text - - Show quoted text - OK, I'd actually tried this. I now have Page Header Team Header =[Season]1945 Header (Access added the [ ] as you'll know Detail Team Footer etc I still get a Data type mismatch in criteria expression error My Group Section looks like Group on Team Group on expression =[Season]1945 Sort on Season The Text box has the control source =IIf([Season] 1945,"Pre", "Post") & " War" Again access added the brackets Neil |
#9
|
|||
|
|||
=
On 29 Apr, 09:41, "
wrote: On 28 Apr, 23:16, Marshall Barton wrote: wrote: On 28 Apr, 17:23, Marshall Barton wrote: wrote: On 26 Apr, 01:33, Marshall Barton wrote: wrote: I have a database in Access 2007 that records details of sports clubs and historical results. Records start in 1929 to present date with a break for World War 2 I have reports showing League tables etc. One report shows all the clubs history on one page with club details at the top, then line by line history of results (Played/Won/Drew/For/ Against etc) However, I would like to group these latter results in pre-war (1929-1939) and post-war (1946- ) periods (all on the same report) Assuming you have no records for 1940-1945, you can just set the Sorting and Grouping expression to =yearfield 1945 In the group header section text box: =IIf(yearfield 1945,"Pre", "Post") & " War" Perhaps you can advise a little more. I have managed to create 2 reports, one post and one pre war, but I can't figure out how to have both on the same report so that I ge tsomething like Team XYZ Pre-War results 1930 1931 Post-War results 1946 1947 But that's exctly what my previous post does. Did you try it? If so, what about it didn't do what you want? If not, why not. OK, I'll explain what I have and did in response to your original post Marsh. It's Access 2007 by the way. My report has Report Header (Contains some explanation text) Page Header (Blank) Team Header (Column Headings) Detail (Obviously where the year by year records print) Team Footer Rather than 'yearfield' I have 'Season' I put a text box in to the Team Header with the IIf statement that you indicated (but modified to my table name) Then in the Group, Sort and Total box at the bottom in design mode I tried to change 'Sort by Season' to Sort by expression using the (again modified) expression you gave. I get the following error when I run the preview 'Data type mismatch in Expression' I guess I wasn't clear enpugh. You were supposed to add the expression: =Season 1945 to Sorting and Grouping between Team and Season. Specify that you want the group header for the new group. Now use the expression: =IIf(Season 1945,"Pre", "Post") & " War" in a text box in the new group header section. -- Marsh MVP [MS Access]- Hide quoted text - - Show quoted text - OK, I'd actually tried this. I now have Page Header Team Header =[Season]1945 Header (Access added the [ ] as you'll know Detail Team Footer etc I still get a Data type mismatch in criteria expression error My Group Section looks like Group on Team Group on expression =[Season]1945 Sort on Season The Text box has the control source =IIf([Season] 1945,"Pre", "Post") & " War" Again access added the brackets Neil- Hide quoted text - - Show quoted text - Marsh I figured the problem. The field in the table was a text field (Season), hence 1945 won't work! I've now changed it to number and it works a treat Thanks for your help (and patience!) Regards Neil |
#10
|
|||
|
|||
=
|
Thread Tools | |
Display Modes | |
|
|