Call data from unrelated Lookup table for inclusion on report

[mckenneyj]

I have a report that displays info for users based on dynamic queries.
That user info is in misc address fields.
The report also displays 4 paragraphs of information.
The database is converted to mde files for use by my customers so they can't screw up the code.
The problem is the 4 paragraphs change from time to time.
I would like to populate a lookup table for information of this nature for this report and other reports.

The lookup would contain a primary key field and a memo field.
tblReportText
ID Text
1 This is the content of paragraph 1
2 This is the content of paragraph 2
3 Content for next paragraph
etc...

report name is rptMyReport
This report need to pull the contants of tblReportText records 1,2,3 & 4 for display in the detail area



Thanks,
John McKenney
Work Hard... Play Harder

 

[dhookom]

You can use a subreport or a number of other solutions. You might be able to add paragraph1 by adding tblReportText to the report's record source and setting the criteria under the ID field to 1. Add the Text field with an alias of "Text1". This will allow you to add paragraph 1.

To add more paragraphs, just add tblReportText again and alias the text field and set the criteria to 2 or 3 etc.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mckenneyj]

The problem is there is noway that I know of to add the table to the report source as it is not related to any records in the main source
If you add an unrelated table then the result set is null

Thanks,
John McKenney
Work Hard... Play Harder

 

[Knicks]

What Dhookup is trying to say I believe is that, just create a number or text field in both tables and set each field to "1" for linking purposes. Then drop a subreport into your main report and use those fields.

Now granted, its not going to have much "logic" but it will start to get that data in there

 

[dhookom]

You don't need a join field between the tables. The objective is to return only one record from each instance of tblReportText by setting a criteria of 1 or 2 or whatever under the tblReportText.ID field.

SELECT tblOther.*, P1.Text as Para1, P2.Text as Para2,
P3.Text as Para3, P4.Text as Para4
FROM tblOther, tblReportText P1, tblReportText P2, tblReportText P3, tblReportText P4
WHERE P1.ID = 1 AND P2.ID = 2 AND P3.ID = 3 and P4.ID = 4;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mckenneyj]

Perfect
Thanks

Thanks,
John McKenney
Work Hard... Play Harder