Use field values as names names?

  • Thread starter Thread starter PayeDoc
  • Start date Start date
P

PayeDoc

Hello All

Is it possible to get the field name for a query from DATA within the
underlying tables?

e.g.
Table [x confirmed] with text field 'practice' (related to key field in
table [practices]) and currency fields 'budget
A', 'budget B', 'budget C' and 'budget D';

Table [practices] with text fields 'budget A alias', 'budget B alias',
'budget C alias' and 'budget D alias';

I would like the query to output the values of 'budget A', 'budget B',
'budget C' and 'budget D' in fields that are named according to the VALUES
of the corresponding 'alias' in [practices]. I know this would be easy with
a report, but I need it in the query so that I can export the data to csv
with meaningful field names.

I should emphasize that I need the query's field name to be the VALUE of the
corresponding field in [Practices] - not just "Aliasname" every time! The
values of the various aliases will change according to other parameters on
the current form, and I need the query's fields to pick up these varying
alias names.

I am sure this can be done - but simply cannoy see how!!

Hope someone can help.
Many thanks
Leslie Isaacs
 
Not impossible and not straight forward. Since the field names have to
change depending on data values, you will need to construct queries that you
can modify at run time. I usually use a template query that does not change
and a runtime query that receives the modified query.

Load the SQL of the template query into a string variable.
Use the Replace function to change the known template field names with the
values you want to use.
Save the query as the runtime named query.
 
maybe you should redesign the table practices from:

ItemID, BudgetA, BudGetB, BudgetC, DubgetD
1010 100 20 30 220

to


ItemID, Budget, BudgetValue
1010 BudgetA 100
1010 BudgetB 20
1010 BudgetC 30
1010 BudgetD 220



and then, a simple join will do the job.



If you can't change the table design, not the most recommended at all, but
you can try;

SELECT SWITCH(
practice = "Budget A", [Budget A],
practice = "Budget B", [Budget B],
practice = "Budget C", [Budget C],
practice = "Budget D", [Budget D] )

FROM [x confirmed] INNER JOIN practices
ON [x confirmed].itemID = practices.itemID



Vanderghast, Access MVP
 
Hello Dave

Many thanks for your response.
I understand the concept that you suggest, but can't think how I would
actually do it!

In my data, table [x confirmed] has field 'practice' (related to key field
'prac name' in table [practices]) and currency fields 'budget A', 'budget
B', 'budget C' and 'budget D'.
Table [practices] has key field 'prac name' and text fields 'budget A
alias', 'budget B alias', 'budget C alias' and 'budget D alias'.
If my template query is called "qry_budget_analysis", how do I get the SQL
of that into a string variable? I know how to use Replace, but can't see how
to get the field names I need - do I use a DLookup? And then, how does the
runtime query access the modified template query?!

Sorry about being dim: I'm the nearest thing we have to an Access Expert in
our organisation, and can generaklly do most of what I need to do with
Access, but things like this are a bit beyond me - without help!

Many thanks again
Les


Klatuu said:
Not impossible and not straight forward. Since the field names have to
change depending on data values, you will need to construct queries that
you
can modify at run time. I usually use a template query that does not
change
and a runtime query that receives the modified query.

Load the SQL of the template query into a string variable.
Use the Replace function to change the known template field names with the
values you want to use.
Save the query as the runtime named query.
--
Dave Hargis, Microsoft Access MVP


PayeDoc said:
Hello All

Is it possible to get the field name for a query from DATA within the
underlying tables?

e.g.
Table [x confirmed] with text field 'practice' (related to key field in
table [practices]) and currency fields 'budget
A', 'budget B', 'budget C' and 'budget D';

Table [practices] with text fields 'budget A alias', 'budget B alias',
'budget C alias' and 'budget D alias';

I would like the query to output the values of 'budget A', 'budget B',
'budget C' and 'budget D' in fields that are named according to the
VALUES
of the corresponding 'alias' in [practices]. I know this would be easy
with
a report, but I need it in the query so that I can export the data to csv
with meaningful field names.

I should emphasize that I need the query's field name to be the VALUE of
the
corresponding field in [Practices] - not just "Aliasname" every time! The
values of the various aliases will change according to other parameters
on
the current form, and I need the query's fields to pick up these varying
alias names.

I am sure this can be done - but simply cannoy see how!!

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
 
Hello Vanderghast

Many thanks for your response.

Unfortunately, altering the table design isn't an option - the mdb is far
too 'mature' for that!

I am not familiar with the SELECT SWITCH construct that you have suggested.
What does it do? Also, I cannot see any reference to the text fields 'budget
A alias', 'budget B alias',
'budget C alias' and 'budget D alias' in Table [practices]: surely these
field must need to be accessed in order to use the values from them?

Sorry to seem so dim!

Thanks again
Les



vanderghast said:
maybe you should redesign the table practices from:

ItemID, BudgetA, BudGetB, BudgetC, DubgetD
1010 100 20 30 220

to


ItemID, Budget, BudgetValue
1010 BudgetA 100
1010 BudgetB 20
1010 BudgetC 30
1010 BudgetD 220



and then, a simple join will do the job.



If you can't change the table design, not the most recommended at all, but
you can try;

SELECT SWITCH(
practice = "Budget A", [Budget A],
practice = "Budget B", [Budget B],
practice = "Budget C", [Budget C],
practice = "Budget D", [Budget D] )

FROM [x confirmed] INNER JOIN practices
ON [x confirmed].itemID = practices.itemID



Vanderghast, Access MVP



PayeDoc said:
Hello All

Is it possible to get the field name for a query from DATA within the
underlying tables?

e.g.
Table [x confirmed] with text field 'practice' (related to key field in
table [practices]) and currency fields 'budget
A', 'budget B', 'budget C' and 'budget D';

Table [practices] with text fields 'budget A alias', 'budget B alias',
'budget C alias' and 'budget D alias';

I would like the query to output the values of 'budget A', 'budget B',
'budget C' and 'budget D' in fields that are named according to the
VALUES
of the corresponding 'alias' in [practices]. I know this would be easy
with
a report, but I need it in the query so that I can export the data to csv
with meaningful field names.

I should emphasize that I need the query's field name to be the VALUE of
the
corresponding field in [Practices] - not just "Aliasname" every time! The
values of the various aliases will change according to other parameters
on
the current form, and I need the query's fields to pick up these varying
alias names.

I am sure this can be done - but simply cannoy see how!!

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
 
SWITCH is a VBA function. It has an even number of arguments. If the first
argument evaluates to true, the switch returns the second argument, else, if
the third argument evaluates to true, it returns the fourth argument, else
....



so, if the field practice = "Budget A", then the switch retunrs the field
[Budget A], or maybe it should have been [Budget A Alias]
else, if field practive = "Budge B", then, the switch returns the field
[Budge B] ( or use [Budget B Alias], it that is the field name to be used),
etc.




Vanderghast, Access MVP



Leslie Isaacs said:
Hello Vanderghast

Many thanks for your response.

Unfortunately, altering the table design isn't an option - the mdb is far
too 'mature' for that!

I am not familiar with the SELECT SWITCH construct that you have
suggested. What does it do? Also, I cannot see any reference to the text
fields 'budget A alias', 'budget B alias',
'budget C alias' and 'budget D alias' in Table [practices]: surely these
field must need to be accessed in order to use the values from them?

Sorry to seem so dim!

Thanks again
Les



vanderghast said:
maybe you should redesign the table practices from:

ItemID, BudgetA, BudGetB, BudgetC, DubgetD
1010 100 20 30 220

to


ItemID, Budget, BudgetValue
1010 BudgetA 100
1010 BudgetB 20
1010 BudgetC 30
1010 BudgetD 220



and then, a simple join will do the job.



If you can't change the table design, not the most recommended at all,
but you can try;

SELECT SWITCH(
practice = "Budget A", [Budget A],
practice = "Budget B", [Budget B],
practice = "Budget C", [Budget C],
practice = "Budget D", [Budget D] )

FROM [x confirmed] INNER JOIN practices
ON [x confirmed].itemID = practices.itemID



Vanderghast, Access MVP



PayeDoc said:
Hello All

Is it possible to get the field name for a query from DATA within the
underlying tables?

e.g.
Table [x confirmed] with text field 'practice' (related to key field in
table [practices]) and currency fields 'budget
A', 'budget B', 'budget C' and 'budget D';

Table [practices] with text fields 'budget A alias', 'budget B alias',
'budget C alias' and 'budget D alias';

I would like the query to output the values of 'budget A', 'budget B',
'budget C' and 'budget D' in fields that are named according to the
VALUES
of the corresponding 'alias' in [practices]. I know this would be easy
with
a report, but I need it in the query so that I can export the data to
csv
with meaningful field names.

I should emphasize that I need the query's field name to be the VALUE of
the
corresponding field in [Practices] - not just "Aliasname" every time!
The
values of the various aliases will change according to other parameters
on
the current form, and I need the query's fields to pick up these varying
alias names.

I am sure this can be done - but simply cannoy see how!!

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
Back
Top