Custom Report Requires Four(4) Rows of Detail (1 Viewer)

snlodge

New member
Local time
Today, 06:06
Joined
Jul 3, 2014
Messages
9
I'm looking for assistance in understanding how to make a standardized four row (record), per page, Access report, even if there is only one(1) or two(2) record(s) related to each grouping. Essentially, if there is one(1) record, the query would add the required blank rows, to total four rows on the page and would do this for each additional grouping until there are no more records.

Plausible situation: Let's say that I had to inspect something once a week and the report was for a month (4 weeks) based on the inspected item. If my inspected item changed in the month, then the first inspected item would have so many records (inspections), and the second inspected item would have so many records (inspections). My need is that the first inspected item be detailed and the remaining weeks not inspected be added as blanks to the report, and the second inspected item follow the same outcome. This example would total 2 pages with four(4) rows each page, and a total of eight(8) rows combined.

Currently, some of the pages show only one week and we all know there are 4 business weeks to a month. Each week is one record, in this report.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:06
Joined
Oct 29, 2018
Messages
21,358
Hi. It might be easier to offer suggestions if you could post a sample copy of your db. Just a thought...
 

zeroaccess

Active member
Local time
Today, 08:06
Joined
Jan 30, 2020
Messages
671
You would have an Inspection form with a subform, forming a one-to-many relationship. You would add the 4 records to the subform, but only complete one of them. Next week, you would complete the 2nd, and so on. Your report is based on this.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:06
Joined
May 21, 2018
Messages
8,463
You would have an Inspection form with a subform, forming a one-to-many relationship. You would add the 4 records to the subform, but only complete one of them. Next week, you would complete the 2nd, and so on. Your report is based on this

I would not do it that way, it would require creating real empty records.

I would make a table of dates that has the date for monday for each week. You can do this is could for 10s of years. In the inspection table I assume there is a date inspected. I would make a query calculated field to return the monday of that week in order to link it to the date table. Then do an outer join from the date table to the inspection query and get a record for every matching date and a blank record for non matching dates. You will probably need an additional cartesian query of inspection Items and dates first. As DBGuy said, would be a lot easier to see the data.
 

zeroaccess

Active member
Local time
Today, 08:06
Joined
Jan 30, 2020
Messages
671
I would not do it that way, it would require creating real empty records.
But they won't remain empty. They will fill the space on the report, but you'll add the data later. I thought that was his requirement.

Keep in mind we are trying to cater to the OP's stated goals...I personally would not see the need to display the 3 future inspections as blank. I would just show the ones completed so far.
 
Last edited:

Micron

AWF VIP
Local time
Today, 09:06
Joined
Oct 20, 2018
Messages
3,476
There is an accepted solution here

I tried it with a table and top4 but didn't have a report to alter. Sometimes a hidden textbox with running sum and code to make a page break visible is also required if you want to break your x number of records onto separate pages. You seem to need a new page after 4 records, so you're trying to do 2 things here - pad records plus a page break.
 

snlodge

New member
Local time
Today, 06:06
Joined
Jul 3, 2014
Messages
9
Ok, first I do want to apologize for taking so long to get back to you guys, you know the deal, just busy. I would like to say thank you for some of the helpful input so far. That being said, I believe a little back story and purpose is in order. The form that I'm deriving this from, is a standard four week form that I'm attempting to copy because the formatting has gotten skewed over the repetitive coping of the form, and there is no original to go back to. I understand that I could just make this form in Word, but I'm attempting to do two things by creating a logging database and a template at the same time.

At the request of 'theDBguy' I took the time this morning to throw together an example database that revolves around what I'm looking for. When the DB first opens, it will open to the inspections form with some fields and a button for the report that I'm trying to modify. The report has 4 pages. Page One has 2 weeks, page Two has 1 week, page Three has the full 4 weeks that I'm looking for, and the last page has 3 weeks. I'm looking to have all four pages have 4 weeks on each page. Just that the missing weeks are blanks. I did use the running sum as the week number.
 

Attachments

  • Example.zip
    34.9 KB · Views: 113

snlodge

New member
Local time
Today, 06:06
Joined
Jul 3, 2014
Messages
9
Based on the limited response, I've got to deduce that this is not possible. I was just trying to combine everything in to one step. However, I will just create the empty template within word and transfer the completed information to the access database. Thank you for all of your help. Take care.
 

zeroaccess

Active member
Local time
Today, 08:06
Joined
Jan 30, 2020
Messages
671
Based on the limited response, I've got to deduce that this is not possible.
I'm not sure how you come to this conclusion. This thread has several possible solutions to get the result you desire.

I haven't had time to look at your example - maybe someone will.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:06
Joined
May 21, 2018
Messages
8,463
I do not see an easy way to do this with a query. If it was me i would have a temp table that looks basically the same as your inspection table. I would have a form that would ask for report range (start date to end date). Then I would enter records in to the temp table for every monday and every zone within the range. Then read your inspections table and fill in the table where applicable (date and zone match). Now there will be a blank record for each zone for each monday where there was no information.
 

snlodge

New member
Local time
Today, 06:06
Joined
Jul 3, 2014
Messages
9
I'm not sure how you come to this conclusion. This thread has several possible solutions to get the result you desire.

I haven't had time to look at your example - maybe someone will.
At the point of my previous post, adding blank records is not an option for me. Therefore I deduced that there was no viable option left. However, MajP just made a interesting suggestion that made me think, and take the solved status off.
 

snlodge

New member
Local time
Today, 06:06
Joined
Jul 3, 2014
Messages
9
I do not see an easy way to do this with a query. If it was me i would have a temp table that looks basically the same as your inspection table. I would have a form that would ask for report range (start date to end date). Then I would enter records in to the temp table for every monday and every zone within the range. Then read your inspections table and fill in the table where applicable (date and zone match). Now there will be a blank record for each zone for each monday where there was no information.
I want to thank you. I’ll check this out.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:06
Joined
May 21, 2018
Messages
8,463
I will see if I can demo it.
This works for me. I just did not build the form to allow you to select a range.

Code:
Public Sub CreateTemp(startDate As Date, endDate As Date)
  Dim strSql As String
  Dim zoneRS As DAO.Recordset
  Dim tempRS As DAO.Recordset
  Dim tempDate As Date
  Dim strStart As String
  Dim strEnd As String
  Dim i As Integer
  strStart = Format(startDate, "MM/DD/yyyy")
  strEnd = Format(endDate, "MM/DD/yyyy")
  strSql = "Select distinct zone from inspections where idate between #" & strStart & "# AND #" & strEnd & "#"
  Set zoneRS = CurrentDb.OpenRecordset(strSql)
  strSql = "Select * from inspections where idate between #" & strStart & "# AND #" & strEnd & "#"
  Set tempRS = CurrentDb.OpenRecordset(strSql)
  CurrentDb.Execute "Delete * from tempInspections"
  tempDate = startDate
  Do
    zoneRS.MoveFirst
    Do While Not zoneRS.EOF
       strStart = "#" & Format(tempDate, "MM/DD/yyyy") & "#"
       strSql = "Insert Into TempInspections (Idate, zone) values (" & strStart & ", '" & zoneRS!Zone & "')"
       CurrentDb.Execute strSql
      zoneRS.MoveNext
     Loop
    tempDate = tempDate + 7
  Loop Until tempDate > endDate
End Sub
Public Sub FillTemp(startDate As Date, endDate As Date)
  Dim rsTemp As DAO.Recordset
  Dim rsInsp As DAO.Recordset
  Dim strStart As String
  Dim strEnd As String
  Dim IDate As Date
  Dim Zone As String
  strStart = "#" & Format(startDate, "MM/DD/yyyy") & "#"
  strEnd = "#" & Format(endDate, "MM/DD/yyyy") & "#"
 
  Set rsInsp = CurrentDb.OpenRecordset("select * from Inspections where IDate BETWEEN " & strStart & " AND " & strEnd)
  Set rsTemp = CurrentDb.OpenRecordset("Tempinspections", dbOpenDynaset)
  Do While Not rsInsp.EOF
    IDate = rsInsp!IDate
    Zone = rsInsp!Zone
    rsTemp.FindFirst "idate = #" & Format(IDate, "mm/dd/yyyy") & "# AND Zone = '" & Zone & "'"
    With rsTemp
      .Edit
      !Inspector = rsInsp!Inspector
      !Zone = rsInsp!Zone
      !Item1 = rsInsp!Item1
      !Item2 = rsInsp!Item2
      !Item3 = rsInsp!Item3
      .Update
    End With
    rsInsp.MoveNext
  Loop
End Sub

Public Sub testFill()
  CreateTemp DateSerial(2020, 1, 6), DateSerial(2020, 1, 27)
  FillTemp DateSerial(2020, 1, 6), DateSerial(2020, 1, 27)
End Sub
 

Attachments

  • Example.zip
    34.9 KB · Views: 121
Last edited:

zeroaccess

Active member
Local time
Today, 08:06
Joined
Jan 30, 2020
Messages
671
At the point of my previous post, adding blank records is not an option for me. Therefore I deduced that there was no viable option left. However, MajP just made a interesting suggestion that made me think, and take the solved status off.
Well Access reports show records, so I'm not sure what else you would do other than draw lines where they will eventually be.
 

Users who are viewing this thread

Top Bottom