Dynamically assign lookup-table name to criteria in query which drives report

[Bob Wolf]

BIG-TABLE contains 1,000 detail records of 10 fields each.
LOOKUP-TABLE contains maybe 50 records of 1 field each.
ID is the field common to both tables, and is the primary key in BIG-TABLE.
Lookup-Table is named “FIRST” (the First subset of records to be printed).
SUBSET-QUERY drives BIG-REPORT, where the ID lookup succeeds,
because “FIRST” is entered to ‘criteria’ cell in SUBSET-QUERY for field ID.
The title “FIRST” is hard-wired in the header of BIG-REPORT (report title).

But I need to print numerous unique record sets from BIG-TABLE.
So I will create many LOOKUP-TABLE(s), named “SECOND”, “THIRD”, etc,
and each next Lookup-Table must be cycled through SUBSET-QUERY.
But I do not want to hand-edit ‘criteria’ to “SECOND” in SUBSET-QUERY,
nor do I want to hand-edit the BIG-REPORT header to “SECOND”.

Rather, I want SUBSET-FORM to accept the parameter “SECOND”;
pass that variable to the ‘criteria’ cell in SUBSET-QUERY for the ID field,
and pass that variable to the header of BIG-REPORT (to label next report).

Can these two properties be exhanged dynamically?

I may redesign:
Create (1) lookup table with columns FIRST to FIFTY by 1,000 records.
Check-boxes would flag records to print (yes/no), and
each report would be looked-up by column name.
But I would still have to pass a variable to the lookup function,
and would still want a way to switch out the report header.

I use Access-2000 and have little experience with code.

 

[WayneRyan]

Bob,

With these assumptions:

1) BigTable contains 1000 records.

2) "Lookup" tables are collections of BigTable's Keys to be printed.

3) Groupings can't be expressed in SQL terms (must be human defined).

The following table structures let you maintain BigTable and use the
two new tables to track as many groups as you want. Any reports only
have to obtain ReportGroupID. The user can select a name from a
combo-box on a form and generate a report for it.


BigTable:
========
BigTableID
Field1
.
.
.
Field10


ReportGroupings:
===============
ReportGroupID
ReportGroupName


ReportGroupingsDetail:
=====================
ReportGroupingDetailID
ReportGroupID
BigTableID

Wayne

 

[Bob Wolf]

Thanks Wayne,

Your third assumption is on point. I believed I had indeed grouped subsets of BigTable keys by collecting them in separate tables named ‘First’, ‘Second’ … ‘Two-Hundred’. Since these many lookup tables have only a single field (BigTableID, in your parlance), the only way to reference each grouping in SQL would be to point to the unique lookup table name from within the report-driving query.

Here is the SQL view of a lookup query created by the handy query builder grid:

SELECT BigTable.DetailFieldOne, BigTable.DetailFieldTwo
FROM BigTable INNER JOIN [First] ON BigTable.ID = First.ID;

So, if I could find a way to dynamically substitute the string “First” with “Second” within this SQL statement; long enough to print BigReport, which calls this query as its record source; accomplish this substitution via a variable passed from a form, since no parameter is offered; I will have in effect “parameterized” the ‘TABLE’ cell in the query builder.

Put another way, I wish I could enter “[Which lookup table?]” in the ‘TABLE’ cell of the query-builder, just as one can now enter “[Which cantaloupe?]” in the ‘CRITERIA’ cell.

The same variable could be conveniently referenced in the report header, titling the report with the name of the subset of keys chosen by the user (i.e. the lookup table name). But I did read somewhere that this reference may only remain available for so long as the form under which the variable was entered remains active (a minor uncertainty, for now).

If I redesign using your table structure, a single table will contain all relationships needed to associate a subset of keys called for reporting - to detail fields in BigTable. I will then be operating to the right of the WHERE clause in the underlying SQL statement (as the relational database gods intended). My table structure allows repeated duplication of a lookup table when most records are common to the next: copy table, rename new table, edit out or in the few keys unique to the new table. A perceived time-saver, forcing me to tinker with the FROM portion of the SQL statement. A more involved description of my project is available: outputting individual title run sheets for 200 modern tracts of land, drawing details from a pool of 1,000 deeds; title chains have characteristics similar to genealogical trees.

Thanks for the alternative thinking. I will probably wait to see if anyone thinks toying around with passing variables to SQL statements is entertaining, and will experiment with your table structure in the mean time.

Bob

 

[WayneRyan]

Bob,

Great! Let's learn QueryDefs...

Use a combobox to display all of your table names.

In the combo's AfterUpdate event, you can easily make this SQL:

strSQL = "SELECT BigTable.DetailFieldOne, " & _
         "       BigTable.DetailFieldTwo " & _
         "FROM   BigTable INNER JOIN " & Me.Combo & " ON BigTable.ID = " & Me.Combo & ".ID;"

Then you save that query as (for example: MyReportsQuery).

MyReportsQuery is always the DataSource for your report. You just change it
whenever the user requests a new qroup.

Use the Search Facility here and look for QueryDef. You'll find examples.

Wayne