Is there a way to select a query upon different table each time? 1

[inso18]

Hello.

I'm writing an application that has many tables of the same purpose/class [tblRecordsOrg1], [tblRecordsOrg2], etc.

I would like to create a general purpose query that will select one of these tables depending on a control value, and will function as a base to other, more specific quieries.

So far I came up with this:

SELECT DISTINCT IIf(Form!MyForm!MyControl=1,[tblRecordsORG1].[Column1],[tblRecordsORG2].[Column1]) AS Column1, IIf(Form!MyForm!MyControl=1,[tblRecordsORG1].[Column2],[tblRecordsORG2].[Column2]) AS Column2, IIf(Form!MyForm!MyControl=1,[tblRecordsORG1].[Column3],[tblRecordsORG2].[Column3]) AS Column3....
FROM tblRecordsORG1, tblRecordsORG2;

But this isn't enough because the number of columns could change in the future.

Any ideas on a solution?
Thanks a bunch.

 

[HarleyQuinn]

Why is it you want to do this? I assume you're going to want to dynamically create joins between the tables within the query as well?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[inso18]

No, I do not need to create joins between tables.

The reason I want to do it is, I work with many tables that has the same metadata, but different data. You can say that the tables are from the same 'class'. I need to use one of these tables each time, (selected by a user) in several reports, each report has a different condition option.

 

[inso18]

Just to add:

With this general query - SELECT * FROM -table name variable- I will be able to write other queries with more specific condition options.

 

[HarleyQuinn]

I'd say do this with VBA.

Build a standard query (using whatever table you fancy) and then you can use DAO and querydefs to dynamically change the table used in your query, based on the users selection.

Rather than type a huge post about it, here's an example I found for you

http://www.tek-tips.com/viewthread.cfm?qid=835675

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[inso18]

Thanks HarleyQuinn.

What's the difference between dynamically changing the table used in my query using DAO and dynamically renaming my tables so they will suit my query when needed?

 

[inso18]

Just wanted to add that it sucks Access/VBA aren't really object oriented.

 

[HarleyQuinn]

I'd stay away from dynamically renaming tables. It should require more code (to also keep track of the previous name) and gives light to possible problems with name conflicts etc.

The tables generally should be left alone (IMO), whereas queries are built to get exactly what you require from them and therefore would be my obvious choice to be changed (from the two choices).

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[inso18]

I understand your argument for staying away from dynamically renaming tables. How about using make-table queries (tblRecords? -> tblRecordsGeneral)

Thanks for help.

 

[HarleyQuinn]

Do you mean making a table from your dynamic query results?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[inso18]

No, creating a make-table SQL that is dynamic, and creates each time the same table from different tables:

docmd.runSQL "SELECT * INTO tblRecordsGeneral FROM tblRecords" & Forms!MyForm!MyControl

 

[chrissie1]

Just wanted to add that it sucks Access/VBA aren't really object oriented.

Uhm, what you are trying to do will not work in an OO language either. That's why people make dataaccesslayers and use ORM's.

I think you are trying to overcomplicate matters. Do you remember KISS? So, I would consider what you are trying to do bad practice.

Christiaan Baes
Belgium

My Blog

 

[HarleyQuinn]

I suppose if you wanted to do it that way then it should work for you, just seems unnecessary to run at least two queries (especially if you've got a decent amount of records, the make table might be slow).

As I say, if you want to use just one query and truncate tblRecordsGeneral (and obviously you'll have to make sure that it's cleared out after every query to keep data integrity) and the possible speed issue isn't a problem then that will work for you.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Do you remember KISS?

Ah, those crazy, crazy, crazy, crazy nights...

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[inso18]

chrissie1, appriciate your hint, do you have a useful explanation link of how to create dataaccesslayers?

HarleyQuinn, thanks for your help and advise. It was nice foruming with you in general.

Regards.

 

[HarleyQuinn]

Thanks, glad I could help

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[chrissie1]

Yep

this little know company called Sun with their obscure language called Java and it's enterprise library called J2EE have a nice pattern for it called DAO.

You can find it here

http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html

It uses a lot of OO patterns but you should get the picture.

But since Access is not a real programming language ;-) those rules might not apply.



Christiaan Baes
Belgium

My Blog

 

[HarleyQuinn]

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[chrissie1]

Look I gave HarleyQuinn a star ;-)

Christiaan Baes
Belgium

My Blog

 

[inso18]

Thanks chrissie1.