Passing Table Name to Query , Maybe [Enter Table Name]?

[Rick4077]

H. L. E. P.
I’m still fighting to discover a way to select a table name from the first query and pass it to the below query.

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~*" And (MSysObjects.Name) Not Like "MSys*") AND ((MSysObjects.Type) In (1,4,6)));

Is there a way to use [Enter a Table Name] in the below query? I could select the table name from the list shown from the above query.

SELECT OfcName,  ‘(I believe this is where the tableName from the above query ahould go?)
Sum(IIf(S.ListName Like C.OfcName & "*" And S.SellName Like C.OfcName & "*",1,0))*2 AS WSO, 
Sum(IIf(S.ListName Like C.OfcName & "*" And S.SellName Like C.OfcName & "*",S.SalePrice,0))*2 AS WSODV, 
Sum(IIf(S.ListName Like C.OfcName & "*" And Nz(S.SellName) Not Like C.OfcName & "*",1,0)) AS TSO, 
Sum(IIf(S.ListName Like C.OfcName & "*" And Nz(S.SellName) Not Like C.OfcName & "*",S.SalePrice,0)) AS TSODV, 
Sum(IIf(S.ListName Not Like C.OfcName & "*" And S.SellName Like C.OfcName & "*",1,0)) AS WST, 
Sum(IIf(S.ListName Not Like C.OfcName & "*" And S.SellName Like C.OfcName & "*",S.SalePrice,0)) AS WSTDV, 

WSO+TSO+WST AS [Total Transactions], 
Sum(IIf(S.ListName Like C.OfcName & "*",S.SalePrice,0)) AS [Listing Dollar Volume], 
WSODV+TSODV+WSTDV AS [Listing and Sales Dollar Volume]

FROM CBAll AS C, SCMLS AS S  
GROUP BY C.OfcName
ORDER BY 10;

Simple is usually easier and faster!

Thanks . . . Rick

 

[dhookom]

Apparently you have multiple similar tables? Is there a good reason why these wouldn't all be in one table with an extra column storing the value previously stored in your table name?

You can't use a parameter for a table name. You can modify the SQL property of a saved query. Another solution might be to create a union query of similar tables with a column that identifies the originating table.

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]

 

[Rick4077]

. . . "Apparently you have multiple similar tables? . . . "

Yes - there are about 30 small tables with about 14 values in [OfcName] field for each table.

Each tables values are different but the fields have the same name.

. . .You can modify the SQL property of a saved query."

Would that have something to do with the HAVING clause?


. . ."Another solution might be to create a union query of similar tables with a column that identifies the originating table"

Would the origination table be the CBALL, etc?

How can I make a union query that incorporates the query with all of my calculations?

Would I have to make a new union query each time I wish to select a new table to run?

"A column that identifies the origination table."
I don't understand this.

Thanks . . . Rick

 

[Rick4077]

A union query wil not allow duplicates, correct?

If this is true than a union query will not work.

Rick

 

[dhookom]

You didn't suggest why you have multiple tables of the same structure. This normally isn't good practice.

Changing the SQL of a saved query has nothing to do with the HAVING clause. Why do you ask?

I'm not sure why you are worried about your calculations.

You would probably need only one union query that includes all of your unexplained similar tables. Your union query might look like:

SELECT * , "CBAll" as OrigTable
FROM CBAll
UNION ALL
SELECT * , "DBAll"
FROM DBAll
UNION ALL
--etc--

You would not exclude duplicates from this union query.


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]

 

[Rick4077]

. . . "You didn't suggest why you have multiple tables of the same structure. This normally isn't good practice."

Each table represents a separate client.

I need to select a table, one at a time, and run the query based on those field values and print the clients report.

Then I need to export the dynaset as an Excel file which is linked to charts.

Thanks . . . Rick

 

[lespaul]

Each table represents a separate client.

As stated above, this isn't good practice.

Kinda looks like you are trying to figure out total sales and quickly discovering why your table structure isn't good practice. For future reference, you will want to read the fundamentals document linked below so you can design tables that conform to the rules of database design and you will be able to easily extract the desired information.

In a normalized database, getting total sales, sales by rep, sales by quarter is easily done. Your real problem is in the structure.

Good Luck,

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Golom]

Combining the ideas
Use Duane's idea to generate a single table (or query)

SELECT * , "CBAll" as OrigTable
FROM CBAll
UNION ALL
SELECT * , "DBAll"
FROM DBAll
UNION ALL
etc.

and call that qryALLTables
then

Select Q.OrigTable As [Client] , ... Other Fields ...

From qryALLTables As Q

Where Q.OrigTable = [Enter The Client Code]
   OR [Enter The Client Code] IS NULL

Group By Q.OrigTable

This puts all your data in a single table and doesn't require that you switch tables for each customer.

 

[Rick4077]

I'm not sure how to interpret this?

SELECT * , "CBAll" as OrigTable
FROM CBAll
UNION ALL   
SELECT * , "DBAll"
FROM DBAll
UNION ALL

etc.

Are you saying to create 30 union queries in this one statement??

Then run this . . .

Select Q.OrigTable As [Client] , ... Other Fields ...

From qryALLTables As Q

Where Q.OrigTable = [Enter The Client Code]
   OR [Enter The Client Code] IS NULL

Group By Q.OrigTable

 

[dhookom]

You wouldn't have to create a huge union query if you just had all your separate client tables in a single table. It is so much easier to create queries to filter your table to a single client rather than attempting to put them together.

I can't imagine having to create a new table when you get a new client. You should only be adding records to existing tables.

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]

 

[Golom]

Are you saying to create 30 union queries in this one statement??

Yes ... but you can do it in pieces.

Select * INTO NewTable

From (
      SELECT * , "CBAll" as OrigTable FROM CBAll
      UNION ALL   
      SELECT * , "DBAll" FROM DBAll
      UNION ALL
      SELECT * , "EBAll" FROM EBAll
     )

and then for successive tables

INSERT INTO NewTable (fld1, fld2, ..., OrigTable)
(SELECT * , "FBAll" as OrigTable FROM FBAll
 UNION ALL   
 SELECT * , "GBAll" FROM GBAll
 UNION ALL
 SELECT * , "HBAll" FROM HBAll
)

until you have processed all your existing tables and saved them into "NewTable" (in this example.) From there on you just work with one table and, as Duane says, just add records to that one table rather than creating a new table for each client.

 

[Rick4077]

This will not work.

Each client has different values in their table (Competing office names) that makes then unique.

Each one of these values are calculated as shown in the previous posting, then printed in a report.

The dynaset from the query needs to be exported in an Excel format which is linked to charts in another program.

If I place all of those values in one table as you suggest, it will be impossible to differentiate one clients competitive offices from anothers. Yes, there are multiple instances of one clients field values in another clients table.

Right now, PER CLIENT, I have the folowing, in Paradox 8;
One table with competitive office names, usually no more than 14.
One program declaring variables, running the query calculations, exporting to Excel,etc.
One report which prints hundreds, sometimes thousands of records and shows the dynaset in the report footer.

If I have 30 clients, that translates to a whopping redundency of 90 plus objects!

I know there is a better way.

One program, one report for all of my clients. Then all I have to do when I get a new client is make a small table with their competitive office names.

I'm sorry, I'm not very good at explaining this which makes this process more labor intensive.

Thanks . . . Rick

 

[lespaul]

have you read the fundamentals document linked below? It will show you 'the better way'.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Rick4077]

Okay - I read the MyQL link.

I understand joins, relational DB's, and the benifits of having a primary index - somewhat.

I'm going to try the suggested union queries as shown above and see what I get.

Thanks . . .

Rick

 

[Rick4077]

You know . . . after looking at all of this again I feel that writing all of the union queries with specific filtering and various wildcards, and maintaining this for each client is far more suseptable to SQL programming problems than maintening simple small tables for each client using a single query based on a single client table.

If a client wishes to change their competitive offices all I need do is make those changes in their small table. No SQL union query change needed.

Using countless lines of union query code with filtering, wildcards etc., would create complexity that would be much more difficult maintain and debug.

What am I missing here?

Rick

 

[PHV]

The union query is a ONE shot task to create your normalised office table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rick4077]

Okay . . . I'm trying this with just a few client tables to see how it works in hopes of better understanding it.

Here is whare I am . . .

SELECT *, "ReMaxPremier" AS OrigTbl
From ReMaxPremier
UNION ALL

SELECT *, "NorthHills" AS OrigTbl
From NorthHills
UNION ALL

SELECT *, "StarFV" AS OrigTbl
From StarFV
UNION ALL

SELECT *, "ReMaxSoCo" AS OrigTbl
From ReMaxSoCo
UNION ALL

How do I end this query? (FROM,WHERE . . ?)

Do I need "AS OrigTbl" after each client table or just the first one?

How do I incorporate my original calculations query into this?

Thanks . . . Rick