Link form values into a query, how??

  • Thread starter Thread starter howren
  • Start date Start date
H

howren

Here are my difficulties:

(1) Set Query grid to display a user-specified field.

I want to use the set query grid to display a field
specified by the user. For instance, if a table has 3
fields ENG, MATHS and GEO, how do I create a capability
such that a user can say I want to see only MATHS and the
query shows that field. Someone suggested earlier that I
create 3 queries ENG_Query, MATHS_Query and GEO_Query but
I find that too complicated.


(2) Create a search form.

I don't want to use a Parameter Query because one Subject
criteria has 6 choices, i.e. ENG, GEO, MATHS, SCI, ART,
HIST. So how do I pass the values entered by user at a
form into a query. By the way, the Subject text box
suppose to tell a query which field to list. If ART is
entered then it means list the ART field. There is no
subject field in the table.

Below is the search form,

ENTER SEARCH CRITERIA:
Class ? ______
Year ? ______
EXAM ? ______
Subject ? > ENG
GEO
MATHS
SCI
ART
HIST
Click to expand...

Locate Cancel

while the table looks like this.

NAME ENG GEO MATHS SCI ART HIST
-------------------------------------------
 
Here are my difficulties:

(1) Set Query grid to display a user-specified field.

I want to use the set query grid to display a field
specified by the user. For instance, if a table has 3
fields ENG, MATHS and GEO, how do I create a capability
such that a user can say I want to see only MATHS and the
query shows that field. Someone suggested earlier that I
create 3 queries ENG_Query, MATHS_Query and GEO_Query but
I find that too complicated.
Click to expand...

You're finding it complicated because your table structure is not
properly normalized. You're storing data - a course name - IN A FIELD
NAME. Data should be stored *in a field* - not in a fieldname! If you
have a many (student) to many (course) relationship, it should be
stored *as a many to many relationship*: a table of Students, a table
of Courses, and a table of (say) Grades linked one to many to both of
these tables. Rather than having a field for ENG and a field for
MATHS, you would have different *records* for each score, with the
coursename as a field in that record.

This datastructure will let you use a Subform to display all the
courses taken by a student.
 
Back
Top