Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    jlorence is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2020
    Posts
    11

    How do I add blank rows on a report, based on the number (quantities) of a response in a form/table

    I am new to Access. My table/form have several categories of employees by position, then the data being entered is the quantity of people needed. So for example:
    Position - Qty
    Electricians - 2
    Sound - 4
    Video - 0
    Laborer - 3

    On the report I need it to look like this (or similar) based on the values above:
    Electricians
    1.
    2.
    Sound
    1.
    2.
    3.
    4.
    Laborer
    1.
    2.
    3.
    How can I get the report to create a blank line for whatever quantities are entered, then push the next line down further?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,032
    What is the purpose of the report you intend to produce?

    Do you have a requirements list? Some tables? Sample data?
    How would this report get data/employees by category? There must be some logic you could describe to give readers some context for the report.

  3. #3
    jlorence is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2020
    Posts
    11

    Orange - purpose and form

    Quote Originally Posted by orange View Post
    What is the purpose of the report you intend to produce?

    Do you have a requirements list? Some tables? Sample data?
    How would this report get data/employees by category? There must be some logic you could describe to give readers some context for the report.

    We produce labor calls, schedules to a union business agent. They then schedule personnel based on how many people we request from each position. They do not have a software program to do this. They literally write names on pieces of paper. So I am trying to at least print out a report that saves them the time of re-writing our requests over and over again with blank lines for them to write peoples names. We have a form like this:

    Click image for larger version. 

Name:	IMG_0655.jpg 
Views:	33 
Size:	89.3 KB 
ID:	40685

    and would like it to print out with grouped by all the event/time/location/date as a header, then all the positions in a list under each grouping, like this:
    Deckhands
    1.
    2.
    3.
    4.
    Electricians

    1.
    2.
    Sound
    1.
    Fork Op
    1.
    2.

    Something like this below:
    Attachment 40686

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,607
    Can you upload a sample db (with just one or two dummy records) so we could see the report? There are some different ways of doing this:
    https://access-programmers.co.uk/for...=284844&page=2

    Another would be to add some code to the button that opens the report to populate a temporary table with the required records for each position (using a simple loop and an append query/statement) then add a grouping by position to your list (report).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,032
    So, you create a blank form with a few titles/categories. Do you actually have a database with tables etc? It seems you are doing the automation, but the union rep(s) still manually fill in the "blanks" with appropriate names. Do you record/store to a database any of the info?

    Doesn't seem like something you'd buy Access for specifically. You could make (word processor/editor ) a txt file as a template, and edit a copy when necessary to add/remove categories/blank lines.

    But if you have Access, and are intent on producing such report(s), then Vlad's post has info worth pursuing.

    Good luck.

  6. #6
    jlorence is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2020
    Posts
    11
    Hi,

    I inherited an Access table/form for us to create the labor calls. Simple form and process, then we print a report for the union rep. He then handwrites everything out in a layout on paper the way he likes it (different from the report we have been providing). I am just trying to duplicate the layout he likes, with a report. I like the form we use to create the requests, but nothing from the union rep goes back into the table/form. I have little to no knowledge of Access, just enough to play around, not enough to write any code.

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,607
    So can you post a copy (zip it first to reduce the size) of your database - remove any sensitive/personal information and leave just one or two sample records.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    jlorence is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2020
    Posts
    11

    trying to attach DB

    New Format Test.zip

    Trying to attach the DB here.

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,607
    See if this is what you wanted. I have wrote the code explicitly so you could understand what it does step by step, but all of the separate positions could be handled by one VBA function.

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    jlorence is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2020
    Posts
    11

    Thank you Gicu, not sure if it did

    Quote Originally Posted by Gicu View Post
    See if this is what you wanted. I have wrote the code explicitly so you could understand what it does step by step, but all of the separate positions could be handled by one VBA function.

    Cheers,
    Thank you for helping! it looks right, but its just giving the same number of blank lines each time no matter what the numbers are in the table for each position.

    I'm sorry I really don't know the first thing about Access. I work with Excel a ton, so I assume there are some similar concepts but I don't even know where to start.

    Any recommendations for an open market to hire someone to build the database?

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,607
    Are you clicking the new button on the form to open the new report?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,607
    Here are some screen shots:
    Click image for larger version. 

Name:	Positions.png 
Views:	25 
Size:	97.6 KB 
ID:	40710
    Click image for larger version. 

Name:	PositionsList.png 
Views:	25 
Size:	51.7 KB 
ID:	40711
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,607
    Have you had a chance to review my last post?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #14
    jlorence is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2020
    Posts
    11

    Report

    Quote Originally Posted by Gicu View Post
    Have you had a chance to review my last post?

    Cheers,
    Hi, Yes, thank you. It will pull up the correct numbers for whichever record you are on, but it seems to include other record's heading data into the report, then it duplicates the numbers on another heading after that. So it doesn't just do the record you are on, it does the first record, or previous record as well each time. We would just want the one record you are on.

    OR - Ultimately we would want to be able to run a report for a date range, that would put all records within that date range together in one file, with each record having the proper # of lines for their own positions.

    Like this old report we have, asks what date range you want to look at, you enter date range, then it runs all the records within that range. We ultimately want the same, but with the report you built with the added lines, instead of this:
    Click image for larger version. 

Name:	IMG_0676.jpg 
Views:	18 
Size:	170.3 KB 
ID:	40725

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,607
    Give me a couple of hours...

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Create New Rows based on number entered
    By AMJADJ in forum Programming
    Replies: 7
    Last Post: 09-25-2018, 07:43 PM
  2. Replies: 4
    Last Post: 11-07-2014, 04:25 PM
  3. Replies: 3
    Last Post: 07-12-2011, 02:02 PM
  4. Insert x number of rows based on value
    By opopanax666 in forum Programming
    Replies: 4
    Last Post: 10-26-2010, 03:26 AM
  5. Hiding rows when blank in a report.
    By ser01 in forum Reports
    Replies: 0
    Last Post: 02-27-2010, 10:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums