Results 1 to 7 of 7
  1. #1
    JOC is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    3

    See all fields from all tables on a report even if they don't have data in them

    I am trying to set up a complicated access report to reflect the contents of an official application form. The fields in the database hold the entries that need to go into the official application form/access report. As such the generated report needs to reflect a certain format and contain box/field outlines for all the fields even if the fields in particular linked sub-tables don't contain data. i.e. there will sometimes be applications which require data to be held in every field and every table for some applications, and others aren't as complicated and maybe only half the tables will hold data related to the main record. However, in all cases I want the report to print out all the possible fields and their borders so that the application form looks complete. I have quite a number of sub-reports in the full report and any solution will need to work across all these too.

    I have been looking on line and can find much about suppressing the appearance of fields and sub-reports which don't contain data, but hardly anything that deals with getting access to include areas which legitimately don't contain data, but which need to be seen as empty fields on the final outputs. Is it possible to do this please? I hope this makes sense as a question.

    (NB. In terms of expertise - I can just about handle a macro, but can't write my own Access basic for event procedures - though I could edit something someone else has written to change field names etc.)

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You don't need to know programming ,instead use report (or query) wizard.
    you build a query with the tables needed, then in query design, change the query relation into an OUTER JOIN.
    this allows you to show ALL parent records and some child records.

    in query design, dbl-click the join line on the tables,
    select ALL records in parent table, some in child,
    save query.

    now make a report using this query,
    all parent records will show even if they don't have children.

  3. #3
    JOC is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    3
    ranman256, many thanks - I see where you are coming from and am familiar with that outer join function in queries. The slight hiccup with building said Outer Join query is that a number (most!) of the sub-reports are being generated from their own specific tables (I.e. just a single table in their source query) and hence there are no linked tables to build an outer join into. The main Report (which all the sub-reports are in) again just works from a single table. This is due to the complicated nature of the queries that have to go on in order to build the data for the sub-reports. I am therefore not 100% certain that I can use the knowledge of the Outer Join query to make your suggestion happen. What do you think?

  4. #4
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    How does the data relate to each other? I'm assuming it does

  5. #5
    JOC is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    3
    It does all relate, but in lots of different ways, some is direct one to many relationship, on some of the many tables these themselves relate to a many data situation which are linked via a cross-referenced unique ID field in a third table, some of these many data also have a number of different variables. Imagine:

    A 'Product' - which has a degree of 'product' based one to one data which is in the same table
    The Product is comprised of many 'bits'
    Each of these 'bits' could be present in more than one product
    Each of these 'bits' requires a related dataset because each bit has a number of different 'qualities' that need referencing
    Some of these 'qualities' exist on a one to one basis with the 'bit' (and are in the same table), however in some cases the 'qualities' exist on a one to many relationship with the 'bits'.
    The product also has a number of other data points which also exist in a one to many relationship with it, but are not in the same category as 'bits' - these are dealt with in a separate cross referenced one to many table.
    The database is to my knowledge correctly set-up and internally referenced as it should be.
    The report needs to populate all areas with fields whether these have data in the tables or not.
    The Application report needs to show all the fields for all the tables whether the product has an entry in each one or not.

  6. #6
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Its defiantly possible but hard to understand. Each report should have something that defines it. Like does it all relate to one order? in the query for the report you need to include all relevant tables and fields also.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Not sure I understand your need, but it seems there are a few things involved.
    To get a list of all fields from tables that are needed in a report, you could get the recordsource for the report (and any subreports) and list the fields and their sources along with definitions/descriptions. Typically held in a data dictionary/repository.
    To show a mock up of a report (format), you could create the design and capture as a jpg(s). And/or using some sample data show/print/file a version of the report.
    To communicate the purpose of the report and how to interpret the info, a training document of some sort, whether video, power point/graphics, verbiage, at one/some/several levels of detail may be appropriate.

    It also seems that a model of the business identifying products, any hierarchy and component parts etc may be more useful than "bits".

    More info may get you more focused responses.

    Good luck.

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

Similar Threads

  1. Replies: 9
    Last Post: 05-26-2015, 07:36 AM
  2. Replies: 2
    Last Post: 03-28-2013, 06:21 PM
  3. Replies: 27
    Last Post: 08-14-2012, 09:05 AM
  4. Replies: 3
    Last Post: 02-06-2012, 10:34 AM
  5. Replies: 1
    Last Post: 03-31-2009, 09:03 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