Source Table as variable (1 Viewer)

Tskutnik

Registered User.
Local time
Today, 07:42
Joined
Sep 15, 2012
Messages
229
hello all,

Is it possible to setup a query that has the source tables/fields as a variable?


Source Tables : They all have the same data but different field names
Position_1: Fields = Account; Value; Date
Position_2: Fields = Acct; Val; DT
Position_3: Fields = AcctNum; Acctval; AcctDate

Query Name = PosQ

Here is the scenario.

PosQ is setup to reference [Position_1]'s 3 fields.

At some point I need to point PosQ to [Position_2] - which has the same data but with different field names. I'll have a translation table that equates the PosQ fields with the Position_1, _2, _3 fields

This does not have to be run time setting, more like a fixed database-wide "Pointer" that will map the PosQ fields to the correct Position_1, _2, _3 table fields.

I don't know how to point the PosQ query to the different table to then let the translation table do its work.

Maybe this is a strange one.

Thanks for any help
 

isladogs

MVP / VIP
Local time
Today, 11:42
Joined
Jan 14, 2017
Messages
18,186
It is a strange one and it doesn't really make much sense

Do you have a relationship between the tables? If not, why not add it?

If you are wanting to modify the data in Position_2 fields based on Position_1 fields, just use an update query.

Why do you need a translate table at all?
 

Tskutnik

Registered User.
Local time
Today, 07:42
Joined
Sep 15, 2012
Messages
229
Agreed this may not make sense.

The use case is I have one Database that will be used across multiple clients. Each client has the same data but the table and field names are obviously different.

In my (strange) example there is a translation table that the users could maintain through a form where they would relate (point) my PosQ query fields to wherever their source data
.[fieldname] is.
 

isladogs

MVP / VIP
Local time
Today, 11:42
Joined
Jan 14, 2017
Messages
18,186
Sorry to say it still doesn't make sense

I still don't see why field names & table names have to be different
In fact if the data is identical in each, why do you need 2 tables at all?

I'll try to explain in more detail using the nearest equivalent I have:

Many of my commercial databases are sold to multiple clients.
These are always split with SQL or Access BE files and each client will choose a different location for the datafiles

In the FE folder, I also have a small configuration database containing a copy of important system tables including the location of BE files. The table names & field names are identical to those in the FE but aliases are used to distinguish the tables in the config file

When I release a new version of the FE, I remove all linked tables & any client specific settings from system tables in the FE.

When the client installs the FE, the client's setting are recovered from the config file, then all tables are relinked. If necessary, other changes required for that update are also done.

NOTE In case of emergency, a backup of the config file is recovered from a backups folder. In addition, important settings are also stored in the registry.

So far the system has never failed!

HTH
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:42
Joined
Jan 23, 2006
Messages
15,364
Will the number of fields, and /or the number of clients change?
Is your sample complete?
Source Tables : They all have the same data but different field names
Position_1: Fields = Account; Value; Date
Position_2: Fields = Acct; Val; DT
Position_3: Fields = AcctNum; Acctval; AcctDate
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:42
Joined
Feb 19, 2002
Messages
42,981
Each client has the same data but the table and field names are obviously different
Why do you allow this? If the field names are different because they are using different spreadsheets, then you can solve the problem by standardizing the names in the database and linking to the spreadsheet and doing an append query that appends fldX to fldY and fld4 to fld9, or whatever.

I think we need to know more about how you got to this point in order to offer a real solution.
 

Users who are viewing this thread

Top Bottom