Combining select data from several tables into one report

[Kimmcdt]

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!

[NTC]

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.

[Kimmcdt]

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!

[NTC]

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....

[Kimmcdt]

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