Results 1 to 15 of 15
  1. #1
    e-support is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    9

    Generate report with blank linked table fields


    I have created a report in Access 2013. The data is generated by three linked tables. But what I have found is that if one of the linked table/fields does not have data, the report will not generate any information at all. If the linked table does have data, it does generate a report. Data is collected in subform within a Form. Any information of what has gone wrong here would be much appreciated.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you cannot link blank data

  3. #3
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Make them left joins. In the query design, change the link between the tables to say take all records from this table regardless of whether there is data in the other table or not.

  4. #4
    e-support is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    9
    Great, thank you. Joining from left to right works. The only thing now is that I had 2 parameters/prompts (date & ID) to generate a specific report, but now it is asking for several other parameters to be entered from one of the linked tables.

  5. #5
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Parameters are fields with square brackets []. When Access does not recognize the field name then it treats it like a user prompt. Those fields aren't valid table field names.

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Note: it is better to have a form with text boxes/combo boxes where the user can enter their selections, it is easier to control and more friendly.

  7. #7
    e-support is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    9
    Quote Originally Posted by aytee111 View Post
    Note: it is better to have a form with text boxes/combo boxes where the user can enter their selections, it is easier to control and more friendly.
    The form/s do have a number of text boxes/combo boxes - it's a little difficult to explain, but these are follow-up details for future appointments (date/apt with ???/time/location). Some of these are list/combo boxes. But they are now popping up as parameters unnecessarily when generating the report.

  8. #8
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Are they supposed to be coming form the form or a table /query? Post the SQL.

  9. #9
    e-support is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    9
    Quote Originally Posted by aytee111 View Post
    Are they supposed to be coming form the form or a table /query? Post the SQL.

    The report details are generated through tables in a query. I'm really an intermediate user of Access. How do I post the SQL?

  10. #10
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Go to design view of the query, then in the top left select SQL view, copy and paste it here.

  11. #11
    e-support is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    9
    Found it...

    SELECT [Patient Data].[First Name:], [Patient Data].[Surname:], [Follow Ups].[Patient ID:], [Follow Ups].[Date:], [Follow Ups].[Tech:], [Follow Ups].[Brand of CPAP_F/U:], [Follow Ups].[Model of CPAP_F/U:], [Follow Ups].[Size of CPAP_F/U:], [Follow Ups].[Mask patient uses:], [Follow Ups].[Mask type_F/U:], [Follow Ups].[Mask brand_F/U:], [Follow Ups].[Mask Model_F/U:], [Follow Ups].[Mask size_F/U:], [Follow Ups].[Pressure patient uses:], [Follow Ups].Flex, [Follow Ups].[Humidifier used:], [Follow Ups].[Setting humidifier is on:], [Follow Ups].[Total machine hours:], [Follow Ups].[Compliance:], [Follow Ups].[Average usage per night:], [Follow Ups].[AHI severity:], [Follow Ups].[Leakage - L/min:], [Follow Ups].[Time in large leak:], [Follow Ups].[Condition of CPAP:], [Follow Ups].[Condition of mask:], [Follow Ups].[Other comments:], [Follow Ups].[F/ups], [Follow Ups].[No# of F/ups], [Follow Ups].[CPAP Eduction], [Follow Ups].[Send copy to], Trials.[F/Up Apt date:], Trials.[F/Up Apt location:], Trials.[F/up Apt with:], Trials.[F/up Apt time:]
    FROM ([Follow Ups] LEFT JOIN Trials ON [Follow Ups].[Patient ID:] = Trials.[Patient ID:]) LEFT JOIN [Patient Data] ON Trials.[Patient ID:] = [Patient Data].[Patient ID:]
    WHERE ((([Follow Ups].[Patient ID:])=[Enter Pt ID]) AND (([Follow Ups].[Date:])=[Enter Date]));

  12. #12
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The names of your fields have broken every naming convention known to man! It is very difficult to see what it right and what is wrong because of that.
    - never use special characters in names - :, #, etc - Access wants those for its own use
    - there are reserved words that must not be used - Date, Name, etc
    - spaces are frowned upon, use underscore instead

    If you changed the names then you could get rid of all the square brackets except those you need and it would be far easier to troubleshoot.

    When you are doing design work in Access you must put on your techy hat and do things according to programming standards. When you are showing data to the user then you show it for them to understand. For instance, the names can all be changed in a query to display nice English words.

  13. #13
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The way it is written, you will get all data from FollowUps, data from Trials if there, and data from PaientData only if there are Trials. Is this how you want it? To do it another way, if you did both of your joins from FollowUps then you would get PatientData regardless if there are Trials or not. But maybe this is not the way your data is laid out.

  14. #14
    e-support is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    9
    Quote Originally Posted by aytee111 View Post
    The way it is written, you will get all data from FollowUps, data from Trials if there, and data from PaientData only if there are Trials. Is this how you want it? To do it another way, if you did both of your joins from FollowUps then you would get PatientData regardless if there are Trials or not. But maybe this is not the way your data is laid out.
    Yes, that is where I have been having the problem... I have just changed the direction from Follow Ups to both other tables, and it looks to be working perfectly. Being a basic to intermediate (maybe) user - there is some much more to learn, and this DB is certainly testing my skills (or lack of)... Thank you so much for your time and patience.

  15. #15
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You're welcome!

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

Similar Threads

  1. Replies: 10
    Last Post: 03-22-2021, 07:59 AM
  2. Need to generate one blank row, new to Access
    By AccessOneRow in forum SQL Server
    Replies: 10
    Last Post: 03-14-2016, 05:48 AM
  3. Replies: 9
    Last Post: 05-26-2015, 07:36 AM
  4. Replies: 2
    Last Post: 12-15-2014, 04:53 AM
  5. Replies: 1
    Last Post: 02-11-2014, 11:14 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