query from 2 tables with no join

[mark kubicki]

(this is part a follow-up question to a previous chat... see below...)
I have 2 tables, one of which has only 1 record, the other having several

-the 1 record table has 24 fields
-the multi-record has a whopping 83 fields
-there are no linked fields between the two

the suggestion was made that I could create a query from these 2 tables for
use as data source in a report.
---> the problem that I'm having is doing exactly that; I continually run
into a wall that tells me I need to have a relationship between the two ?!?!
I've tried creating a stand alone query, and also an SQL query in the the
report as its data source: same problem both places

lost,
(thanks in advance)
-mark




----------------------------------------------------------------------
What is the structure of tblPrintOptions? Is it a one record table with
multiple fields? (yes)

If so, you can add the table to your query with NO join. Then each row
produced
would have all the options in the row and you could build your query with

SELECT SomeField,
, SomeOtherField
, IIF(tblPrintOptions.[Abbreviate Description],
Left(tblData.[Description],50))
as Description
, IIF(tblPrintOptions.[Include Location], "Location:" &
tblData.[Location],Null)
as Location
, IIF(tblPrintOptions.[Include Notes], "Notes:" & tblData.[Notes]) as Notes
, ...
FROM tblData, tblPrintOptions

This should be faster than doing all those lookups.

 

[Guest]

"run into a wall"? There should be no reason why you would have to have a
relationship between the tables. Are you using a report wizard? If so, either
don't use one or just add the second table to the record source later.
--
Duane Hookom
Microsoft Access MVP

(this is part a follow-up question to a previous chat... see below...)
I have 2 tables, one of which has only 1 record, the other having several

-the 1 record table has 24 fields
-the multi-record has a whopping 83 fields
-there are no linked fields between the two

the suggestion was made that I could create a query from these 2 tables for
use as data source in a report.
---> the problem that I'm having is doing exactly that; I continually run
into a wall that tells me I need to have a relationship between the two ?!?!
I've tried creating a stand alone query, and also an SQL query in the the
report as its data source: same problem both places

lost,
(thanks in advance)
-mark




----------------------------------------------------------------------
What is the structure of tblPrintOptions? Is it a one record table with
multiple fields? (yes)

If so, you can add the table to your query with NO join. Then each row
produced
would have all the options in the row and you could build your query with

SELECT SomeField,
, SomeOtherField
, IIF(tblPrintOptions.[Abbreviate Description],
Left(tblData.[Description],50))
as Description
, IIF(tblPrintOptions.[Include Location], "Location:" &
tblData.[Location],Null)
as Location
, IIF(tblPrintOptions.[Include Notes], "Notes:" & tblData.[Notes]) as Notes
, ...
FROM tblData, tblPrintOptions

This should be faster than doing all those lookups.

I have a report where the content of several of the fields is dependant
upon
options entered by the user on a form.
for example some options might be...
_abbreviate description?
_include location?
_include notes?
_include contact info?

the information for the print options is stored in table: tblPrintOptions
the information for the records (all of the info) comes from a table:
tblData
the fields in tblData are named similar to the print options...

so the field might read [sic: plz ignore the code typos]:
if (dlookup(tblPrintOptions!abbreviate description),
left(description,50)+"",description) & _
if (dlookup(tblPrintOptions!include location), ("Location: " +
location), "") & _
if (dlookup(tblPrintOptions!include notes), (Notes: " + notes), "")
&
_
if (dlookup(tblPrintOptions!include contact info), (Contact: " +
contact
info), "")

there are seldom more than 100 records in the report, however, there are
(24+/-) print options in total

----> my question is: What would be the best approach?
...keep all the Dlookups? ...store the print options as variables on
report
open?
...I can't combine the 2 tables in a query as there are no relationships
between the 2

any direction, no matter how general would be a HUGH help,
thanks in advance,
mark