Results 1 to 8 of 8
  1. #1
    ketangarg86 is offline Novice
    Windows 8 Access 2013
    Join Date
    Sep 2014
    Posts
    24

    Access Reports question

    Hi,




    I am new at MS Access and with hit and try I was able to create a report in access. The report has around 15 columns and around 1000 rows. The data in some of the columns is the same. What I mean is that, for example, column 1 is "Division" and has around 200 rows each of the 5 divisions namely HR, IT, Finance, Operation, Supply. Similarly I have some more columns with similar data. Also, the reports last column is attachments which has the attachments.


    The report is generated after user selected the data from a form.


    Could you please help me with the below question:
    1. Is it possible that I can create a textbox or a list box in the report header to show the unique values for the divisions (for example only HR, IT if those were the only ones) generated in the report.
    2. I want to download this report as a pdf and as an excel, how can I also download all the attachments with the report. Right now, if I download the report, the attachments are not included.
    3. Is it possible to give the user permissions so that they can add attachments in the report itself while viewing it.


    Thanks a lot for your help.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Multiple columns with similar data indicates a non-normalized data structure.

    1. A listbox is usually useless on a report. The listbox will not 'grow' and scroll bar will not work. The listbox size would have to be set for maximum expected number of items. So not practical for long list. A VBA procedure can concatenate the multiple items into a single string for display in textbox. http://allenbrowne.com/func-concat.html

    2. Attached objects cannot be downloaded by export to PDF or Excel. Requires VBA code to save the attached object to external folder then manipulate that file. Should be able to export the object name as text. The attachment field is a multi-value type field. A query can expand the multi-value field to individual records and the object name will be displayed in a field. http://office.microsoft.com/en-us/ac...010149297.aspx

    3. No. Attachments cannot be added via report. This is data entry. Use table or form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ketangarg86 is offline Novice
    Windows 8 Access 2013
    Join Date
    Sep 2014
    Posts
    24
    Thanks a lot. Sorry to be a pain. But I have some more questions.

    1. How to fix the formatting in the image below of the report. There is a line in between the page header and the detail in some of the columns. I want to completely remove that line.

    Click image for larger version. 

Name:	error.JPG 
Views:	12 
Size:	11.5 KB 
ID:	18198

    2. How can put a button on the report and give the user the option to download the report in the excel or pdf format.
    3. I downloaded the report in the excel format but the color formatting of some cells in the report is not downloaded. It is downloaded in the pdf format.
    4. Is there a way that I can autofit the cells in the excel report when I download it.
    5. The pdf downloaded version breaks the columns in two pages. How can make sure that all the columns are on the same page. It is difficult to view the data when it is split in two pages.

    Almost there with my project. Thanks a lot for your help.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    1. I don't know what is causing the line. Would have to review the report.

    2. Button on report will not work in print preview, only report view. Have button(s) on a form or teach user to right click on report in print preview.

    3/4. Not without a lot of VBA code.

    5. Not sure, maybe change the report margin settings.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ketangarg86 is offline Novice
    Windows 8 Access 2013
    Join Date
    Sep 2014
    Posts
    24
    Thanks. Here is a more clear picture of the report. The line is still there. I am going nuts over this line.
    Click image for larger version. 

Name:	error.JPG 
Views:	11 
Size:	16.8 KB 
ID:	18217

    - how can I border my report.
    - The pdf print preview and pdf downloaded both shows 7 columns in 1 page, 7 columns in second page and 3 columns in third page. How can I display all columns in one page. Could not find where is that setting

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    ketangarg86 is offline Novice
    Windows 8 Access 2013
    Join Date
    Sep 2014
    Posts
    24
    Thanks. I reduced the font size to make the columns fit in one page. Now I am having the below issue. Hopefully the last issue

    When I print to pdf using the button on the report, there is blank page also printed. I am guessing the empty data in the layout view that I can see. Also, the column attachments is included but it is of no use to me since the attachment is not printed.

    Can I design the print to pdf button such that I can choose what columns to print to pdf?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Essentially, no.

    That would be design edit of the report to remove the controls.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 01-24-2014, 01:36 AM
  2. Replies: 17
    Last Post: 06-25-2013, 05:06 PM
  3. Newbie question on generating reports
    By nadeem in forum Reports
    Replies: 1
    Last Post: 08-17-2011, 09:04 AM
  4. Splitting a Database Question about reports
    By rodeoboy in forum Security
    Replies: 1
    Last Post: 02-15-2011, 10:59 AM
  5. New to reports - Basic Layout question
    By sesproul in forum Reports
    Replies: 1
    Last Post: 02-05-2010, 10:35 AM

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