Results 1 to 5 of 5

Combining select data from several tables into one report

  1. #1
    Kimmcdt is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    6

    Combining select data from several tables into one report

    I have Access 2007. I have 5 tables that I need to pick fields from to make a report. When I run a report, I get the proper headings but no data appears. I can run a report on one table and that works great. But not what I need. I am not experienced so need someone to walk me through this.

    This is supposed to be better than Excel but I am now wondering.

    First table - Drug Info has 12 cols and I want to pick 2 for my report.
    Second table - Regulatory Info has 13 cols and I want to pick 2 for my report.
    Third table - Specific Info has 9 cols and I want to pick 1 for my report.
    Fourth table - CDR Infor has 12 cols and I want to pick 2 for my report.
    Fifth table - BC has 26 cols and I want to pick 3 for my report.

    I tried using a query and I tried using a report and all I ever get are the column headings with no data. Arghhh. HELP!

  2. #2
    NTC
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    1,503
    well.....

    first; does the query, when run by itself, return records?? Its possible you have joined the query in such a way that the valid result is no records. So whenever you have a report sourced on a query - - and there is no data showing; you must sanity check whether or not the underlying query actually has data; if so - check that your report property is not having a filter.

    a join of 5 tables is perhaps not feasible depending on whether or not there is a single cross referencing field valid in all 5 tables.

    have you considered sub-reports? sometimes this is the only - or better - way to implement a report; again this is depending on whether there is or is not cross referencing fields (key fields) and whether all 5 are a 1:1 or whether you have 1:Many relationships between tables.

  3. #3
    Kimmcdt is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    6

    Access Relationships

    I find your response hard to follow because I clearly do not know what I am doing.

    Ok, I am trying a simpler one now. This is what I have.
    Table 1 has 12 columns and for this report/query I want to pull out data from 3 columns.
    Table 2 has 13 columns and from this report/query I want to pull data from 3 columns as well.
    The primary key in Table 1 is called Drug ID (it is automatically numbered) and I have built a relationship between it and a field in Table also called Drug ID. This field has no data in it - do I need to copy the numbers from the first table Drug ID to this column?? Is this why they do not talk to each other? I am obviously a newbie!
    If I run a report or query from just 1 table at a time, they work beautifully.

    Thanks!

  4. #4
    NTC
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    1,503
    ha - well you don't know what I mean, and I don't know what you want....

    listen - in the most general terms - you have two choices - - join tables together via a query - and make that query the record source for the report.

    or - - select one table to be the record source - - and add all the other tables as subreports

    0r a combination of those 2 approaches.

    Generally in table relationships that are 1:Many the 1 is the source for the main report and the many table is a subreport.

    (And remember one can stick in a subreport totally unbound....so if I want a list of widgets in my rocket report - I can simply put it in...)

    but when you get a query result that is column headings without data - - it means there are no common records that cross reference....

  5. #5
    Kimmcdt is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    6

    My problem is solved

    I did it! It works. Thanks for your help. It helped stretch my brain and led me to the solution.
    Merry Christmas!!

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

Similar Threads

  1. Joining or Combining Two tables
    By escuro19 in forum Queries
    Replies: 2
    Last Post: 02-16-2010, 01:55 PM
  2. Combining Tables
    By king_bowzow in forum Queries
    Replies: 0
    Last Post: 08-05-2009, 09:15 AM
  3. Replies: 1
    Last Post: 06-21-2007, 12:02 PM
  4. Replies: 1
    Last Post: 06-03-2006, 04:02 PM
  5. Help combining 3 tables
    By luscioussarita in forum Queries
    Replies: 1
    Last Post: 12-14-2005, 01:22 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
  •  
Tech Forums: Microsoft Office Forums