Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    13

    Unanswered: Access 07 Report Help

    Hi Everyone,

    Here's my problem. I have a DB with the following structure:

    Tables:
    tblEMSCall_Main
    tblEMSCall_Patients
    tblEMSCall_VehicleInfo
    tblEMSCall_Personnel

    These tables are all related to the CallID Field in tblEMSCall_Main using a One-to-Many relationship to a field called CallID in each other table. So for example, CallID in tblEMSCall_Main for a record is 2013-001. That number is in each of the other tables in the CallID field (but I'm sure you all already knew that, just covering all bases).

    Here's where my problem is. I need to create a report that pulls each call from tblEMSCall_Main, and then pulls all related records from the related tables. But here's my problems:

    1) Each "call" may not have information in tblEMSCall_VehicleInfo because that is only used in the times where there's a vehicle involved in the call. And I know from testing a query if there's not data in that table for a record, instead of showing the other records with that data, it shows nothing.
    2) When there is data in the CallID field in all tables, the data in tblEMSCall is duplicated for each of the other entries. For example:

    Call 2013-100 has two patient records and two vehicle records. The query will pull the information for all the data in tblEMSCall for 2013-100 and repeats it for each independent record in all other tables. So each line shows the call address, response times, etc. for each independent record.

    What I need the report to do is pull the data from tblEMSCall ONCE, then give me any related data from the other tables, and push that in the report.

    Any help is greatly appreciated!

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    This requires a report with sub-reports in the detail section. The Help file should have some pointers to get you started - if not, comment back and I'll dig out some old examples.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jul 2011
    Posts
    13
    So I create a sub report in detail for each other table?

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Correct. The sub-reports must contain the foreign key from the table that's the basis for the main report, and then they will only display the related records in the detail section for each record in the main table.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •