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]

Ta chrissie

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]

Hehe.

chrissie1, if you're still on this post,
Just to clarify,

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

It won't work in OO, but then you're suggesting me an OO solution?

 

[chrissie1]

The principle of a DAL can not only be used for OO languages an ORM (Object relational mapper) is.

But it was you who suggested that if you had OO that it would make it more easy and I said that you wouldn't do that in OO.

A database should be normalized and hold data in a transparent way. The fact that you have this "[tblRecordsOrg1], [tblRecordsOrg2]" suggest to me that something isn't normalized or that you perhaps have to much data for an access database (meaning that you have millions of records in each table and that you are doing horizontal partioning to keep the size down).

The problem is that your profile says that your a technicaluser and I don't know many TechnicalUsers that grasp the concepts of OO.

Christiaan Baes
Belgium

My Blog

 

[dhookom]

Why would you want "many tables that has the same metadata, but different data"? Access might not be OO but it is a fairly decent relational database.

Is there a reason why you don't have just one table with a field added that identifies the attribute previously stored in the table name?

If you can't normalize this down to a single table then SQL has this cool syntax called "UNION". You can use:

SELECT 1 as Org, tblRecordsORG1.*
FROM tblRecordsORG1
UNION ALL
SELECT 2, tblRecordsORG2.*
FROM tblRecordsORG2
UNION ALL
--- etc ---

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[gmmastros]

Here's a hint that you can use/re-use over and over again. Consider it, uh... an OO hint.

Whenever you are designing a database, and feel an urge to start numbering things.... you need to stop. Step back, and take a closer look at things. You should never use a sequential number in a table name or a column name. For example, if you had a table to track test scores for students. You could have a separate column for each test taken. So, suppose you have 20 columns for tests. Now, what happens if a professor wants to give 21 test in a semester? Suddenly, your table fails. Instead, you should create a table that has StudentId, TestId, Grade. Then, when the student takes another test, you simply add another record to the table. How many tests could a student take with a table structure like this??? How big is your hard drive?

Similarly, if you feel the urge to number your tables, then you need to re-design your database. Going back to the test example. You could have a separate table for each course. The structure might be. StudentId, TestId, Grade. You would have separate tables for English, Math, SocialStudies, etc... Instead, it would be better to have a single table for all of these. Of course, you would need to identify which course the test applies to, so add another column to accommodate it. The structure would be... StudentId, CourseId, TestId, Grade. With a structure like this, if you wanted to return the data for the Math course, you would simple add a filter (ex: CourseId = 2 for math, so... Where CourseId = 2).

In this case, you have multiple tables that have the same structure but contain different data. I suggest that you consider combining these tables in to a single table.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HarleyQuinn]

Duane & George - couldn't agree more.

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 for all of you for your advise.

My database works in the following way:

There are many excel tables that I get from a firm, every month, which have very similiar metadata, in .csv format and import them into a database. I need a query to act like a port to any table.

There are parts of the whole application (the manufacture of the tables with their metadata and records) to which I just don't have access to.

 

[gmmastros]

I had a feeling that Excel would be part of the problem.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[inso18]

chrissie1, I'm studying now JAVA and J2EE (course) so I do have some experience in OO.

Do you know what is the best platform to code something like an interface object or abstract object that will let me create and maintain objects that would act like a patterns by which I could create reports in MS-Access?

 

[onpnt]

Do you know what is the best platform to code something like an interface object or abstract object that will let me create and maintain objects

Excel?

[sub]____________ signature below ______________
Backups are for sissies!!!!

http://www.lessthandot.com

coming to your keyboards soon[/sub]

 

[lespaul]

So you get these spreadsheets from outside entities and then you insert that information into your own (Access) tables? Or into the application you mention?



Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[inso18]

Onto the Access application (which has many kinds of reports, tables of its own such as country areas, question values, etc., and a VBA engine that makes different calculations using the imported tables and applications tables.)

 

[lespaul]

so this application:

There are parts of the whole application (the manufacture of the tables with their metadata and records) to which I just don't have access to.

IS the Access application you are trying to load the excel data into?

 

[chrissie1]

Do you know what is the best platform to code something like an interface object or abstract object that will let me create and maintain objects that would act like a patterns by which I could create reports in MS-Access?

I give up. An interface that creates something?

And why would you want to use access reports from Java? There are so many good/better reporting engines for Java.

How long have you been studying Java/JEE/OO?

Christiaan Baes
Belgium

My Blog

 

[inso18]

By 'whole application' I meant - whole project, which is my application, and the application that creates the excel data.

Sorry for not being clear..

 

[lespaul]

There are many excel tables that I get from a firm, every month, which have very similiar metadata, in .csv format and import them into a database.

and the application that creates the excel data.

which is it? you have an application that creates the excel data or you get it from an outside source?

 

[inso18]

There's an outside source which creates the excel tables. I import them into my application. This whole system is what I called later 'project'.