Give each line in group the same number on a report line 1

[GPnew]

I have a report that typically has 3 or 4 groups each with 2 sub groups ..ie group within the groups
I am trying to assign a number to each subgroup and show it on the report .
eg I need to show the number 1 in the first column on each line in the first sub group, number 2 in the first column on each line in the second sub group, number 3 in the first column on each line in the third subgroup ....and so on..

There are a variable numbers of items in each sub group

I have played around with running sums but that wasn't much help

I would appreciate any suggestions

Thanks

 

[dhookom]

Can we assume you are using the Sorting and Grouping in your report? You should be able to create the running sum over group within the group header and detail sections.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[GPnew]

Yes sorting and grouping used ....but I must have it set up wrong.

Rather than adding the same number to each "detail" line in the subgroup , it adds an increasing number to each line... eg 1 to the first line on the subgroup ..then 2 to the next line in the subgroup.., The (increasing) count starts again in the next sub group.. which is not what I am trying to do ?
What am I doing wrong ...FYI the text box in which I have the running sum set up on is on the detail line

Thanks

 

[dhookom]

I am confused (probably just my age) and don't understand clearly what you expect your numbers to do. Can you either type into a reply (using the Pre tag) or add a screen shot of what you expect to display?



Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[GPnew]

Sure

The same number for each line in the subgroup. FYI the number is in the first column of the report

Sub Groups (based on date) . FYI in my actual report there is no gap between groups

1 2/2/2022 100 subgroup 1
1 2/2/2022 200 subgroup 1
1 2/2/2022 300 subgroup 1

2 3/2/2022 4000 subgroup 2
2 3/2/2022 8000 subgroup 2
2 3/2/2022 100 subgroup 2

3 6/2/2022 500 subgroup 3
3 6/2/2022 200 subgroup 3
3 6/2/2022 700 subgroup 3

Cheers

 

[dhookom]

I think the only way (without some advanced SQL or code) is to add a group header section with a text box:
Name: txtGroupSequence
Control Source: =1
Running Sum: Over All

Then add a text box to your detail section:
Name: txtGroup
Control Source: txtGroupSequence

You can set the height of the group header section and its controls to 0"

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[GPnew]

Thanks it works a treat

 

[dhookom]

If it works, you should close this thread by clicking the Great Post! link of the answer.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[GPnew]

I ended up creating a table query , then making another table and some vba to tidy it up as I couldn't get the output in the right order on the output and the group numbering to work.

One things I found out was that in creating the table the query was deleted ..so had to be re installed

Ie save the query

QueryCode = CurrentDb.QueryDefs(QueryName).SQL
Then resaving it at then end of the process
CurrentDb.QueryDefs(QueryName).SQL = QueryCode
As below

Private Sub OK_Click()
On Error GoTo OK_Click_Err
Dim QueryName As String
Dim QueryCode As String
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMakeTestJNLtable", acViewNormal, acEdit
QueryName = "qryMakeTestJNLtableFinal"
'Access has a bug where it sometimes erases the SQL code of the Query it's sending as a spreadsheet
'This stores the code in a string, so that we can restore it if it's been erased
QueryCode = CurrentDb.QueryDefs(QueryName).SQL

DoCmd.OutputTo acOutputQuery, QueryName, acFormatXLS, "C:\Journal.xls"

'If the SQL code was erased, restore it
If CurrentDb.QueryDefs(QueryName).SQL <> QueryCode Then
Debug.Print "SQL Code Missing, restoring it now."
CurrentDb.QueryDefs(QueryName).SQL = QueryCode
End If

MsgBox "Report Exported"
DoCmd.SetWarnings True
OK_Click_Exit:
Exit Sub

OK_Click_Err:
MsgBox Error$
Resume OK_Click_Exit

End Sub

 

[dhookom]

In over 25 years of developing in Access and reading through forums, I have never seen an issue with the SQL property of a query changing without user or code updates.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[GPnew]

Yep I couldn't work out what was happening. I couldn't run the process twice as the query kept on being blown away.
I found the answer on Google ..there is a bit about the bug there ..appears to only happen when exporting to excel . It could related to poor code on my behalf ....but at least the work around works