Query for Field Names

[Guest]

I have a Form1 .
Have Query1 with Fields named "PKey", "Name", "Tool1", "Tool2", "Tool3".
Form1 has CommandButton which opens Query1 using SelectionCriteria.

I want to have the user be able to select the name of a Field in Query1 he
wants to apply the SelectionCriteria to. I want to display a list of the
names of the Fields in Query1 using a ComboBox on Form1. In this case, I want
to create a query (Query2) to populate the ComboBox with the following choices

PKey
Name
Tool1
Tool2
Tool3

I DO NOT want the following to be displayed in the ComboBox

1 John Saw Hammer Screwdriver
2 Steve Saw Pliers Paint Brush

Any ideas???
Sorry I was so vague last time I posted this question.

 

[Allen Browne]

Ray, there is a serious problem with the way this table is designed.

If one person can have multiple tools, you have a one-to-many relationship
between person and tool. This means you need another related table with many
*records* to hold the various tools for each person, not many *fields* in
one table.

Actually, several people can own one type of tool (such as a saw). This
means there is really a many-to-many relation between person and tool type.
You therefore need these 3 tables:
Person table (one record for each person):
PersonID primary key
Surname text
FirstName text
...
Tool table (one record for each type of tool):
ToolID primary key
...
PersonTool table (one record for each combination of person and tool):
PersonID relates to Person.PersonID
ToolID relates to Tool.ToolID

The interface for this is a main form bound to the Person table, with a
subform bound to the PersonTool table. In the subform, you will have a combo
for ToolID, and the continuous view subform will let you choose one tool per
row.

Once you have that structure in place, it is very easy to create the queries
you want. There are heaps of benefits to this approach. For example, you can
add another type of tool (just by adding a record to the Tool table),
without having to redesign all your tables, queries, forms, and reports.

Technically, the PersonTool table is called a junction table, and it is the
standard way to resolve a many-to-many relationship into a pair of
one-to-many relationships. If you want to read another example, see:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

 

[Guest]

Frustration......

Here is a small part of the SQL for my real query1

SELECT EmployeeTable.EmpID, EmployeeTable.Name, EmployeeTable.Department,
EmployeeTable.Shift, EmployeeTable.PkgKyoto, EmployeeTable.PkgYork,
EmployeeTable.PkgTam, EmployeeTable.PkgGima
FROM EmployeeTable;

I am not interested in seeing the values contained in those fields. I can
see that by just running query1.

What I want to be able to see in a new query is a list of all the names OF
the fields in query1. In this case I want to create a query that will return
exactly the following:

EmpID
Name
Department
Shift
PkgKyoto
PkgYork
PkgTam
PkgGima

I do not want to see

11840 Rene 339 1st Shift 5 0 10
34805 Clifford 339 2nd Shift 10 5 10
70000 Michael 339 1st Shift 10 5 10

 

[Allen Browne]

Access does not expose the field names to you like that.

You can list them programmatically like this:
http://allenbrowne.com/func-06.html
Just substitute QueryDefs instead of TableDefs.

If you are trying to get the field names into a combo or list box, you can
do that just by setting its RowSourceType to Field List.

Without understanding what you are aiming to do, it still looks like the
problem exists because the table is unnormalized.

 

[Guest]

I figured it out. Here is the SQL

SELECT Mid([Expression],15,30) AS FieldName
FROM MSysQueries
WHERE (((MSysQueries.Attribute)=6) AND ((MSysQueries.ObjectId)=-2147483572));