Use of the UNION operation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am new to SQL and I am trying to use UNION for the first time in order to
combine the effect of a number of similar update queries. One of the queries
is as follows:

UPDATE Results SET Results.C_S = C_S+1
WHERE (((Mid([syll1],5,1)) = "*" or (Mid([syll1],5,1)) = "A" or
(Mid([syll1],5,1)) = "B"));

The intention is to increment the value of C_S in a table called Results
according to the value of the fifth character in another field called syll1.
Having done so, I then need to increment C_S again according to the value of
the corresponding character in the field syll2 - then syll3, etc up to syll12.

The description of the UNION operation indicates that its use is relevant
here, but so far I have no progress - perhaps I should be doing it some other
way.

Any help would be much appreciated.

Jim Jones
Botswana
 
I think UNION is not used for UPDATE.

You had better create 12 separate update queries and execute them one by
one.

Herbert
 
Jim

It may be that there is an issue with your table structure, if you are
finding it necessary to "walk through" fields in the table (12?) to handle
an update.

If you provide a bit more description of the underlying data structure (and
an example of the data), the 'group may be able to offer alternate
approaches ...
 
Dear Jeff

Many thanks for your offer of help.

The table I am working with shows examination results for about 25,000
senior school students in a country in Southern Africa (which should perhaps
remain unidentified). In each record, there are fields for things like
School Number (Schools are called Centres) candidate number (called cand),
date of birth and gender. Then there are 12 fields (called syll1, syll2,
syll3, etc)identifying the subjects (subject are known as syllabuses) taken
by the student and the grade awarded. Most students do 7 or 8 subjects in
which case the remaining fields are null. The first 4 bytes of the syll
fields identifies the syllabus with a 4 digit numeric code; the next 2 bytes
shows the grade (*, A, B, C .....G) sometimes **, AA, BB, CC etc. A typical
record looks like this - set out vertically for convenience:

Centre: AA520
Cand: 1234
Name: Jim Jones
DoB: 01/01/1985
Gender: M
Syll1: 1650A (grade A in English Language)
Syll2: 3650C (grade C in Mathematics)
Syll3: 9650DD (grade DD in Double Science)
etc

This particular query is intended to identify candidates who have grade C or
better in 5 or more subjects. The query is applied to each Syll in turn - if
the grade is C or better, the counter C_S is incremented by 1 - hence the
apparent need for the same query to be applied to each of 12 fields in turn.
I then have apply the query again to increment again if the subject is one
with 2 grades (like Double Science above).

I am applying the queries (about 27 altogether) using a single macro which
returns the total number of students in each school and the number with 5 C's
or better. It works, but there must be a better way - hence my query this
morning. As ever, any help will be much appeciated.

Pehaps this is a good opportunity to record how much help I am receiving via
the Newsgroup. I post something two or three times a week (twice today!),
and the help I receive solves the immediate problem and my working knowledge
of Access is always improved by the experience. A service of this kind is
particularly useful for someone such as myself working in isolation and
dependent on some good books and the help screens.

Thanks again

Jim Jones




In this particular query, I am identifying candidates who have grade C or
better in 5 or more subjects

Jeff Boyce said:
Jim

It may be that there is an issue with your table structure, if you are
finding it necessary to "walk through" fields in the table (12?) to handle
an update.

If you provide a bit more description of the underlying data structure (and
an example of the data), the 'group may be able to offer alternate
approaches ...

--
Good luck

Jeff Boyce
<Access MVP>

Jim Jones said:
I am new to SQL and I am trying to use UNION for the first time in order to
combine the effect of a number of similar update queries. One of the queries
is as follows:

UPDATE Results SET Results.C_S = C_S+1
WHERE (((Mid([syll1],5,1)) = "*" or (Mid([syll1],5,1)) = "A" or
(Mid([syll1],5,1)) = "B"));

The intention is to increment the value of C_S in a table called Results
according to the value of the fifth character in another field called syll1.
Having done so, I then need to increment C_S again according to the value of
the corresponding character in the field syll2 - then syll3, etc up to syll12.

The description of the UNION operation indicates that its use is relevant
here, but so far I have no progress - perhaps I should be doing it some other
way.

Any help would be much appreciated.

Jim Jones
Botswana
Click to expand...
Click to expand...
 
I suspected something like what you've described ... repeating fields. When
fields are named "syl1", "syl2", ..., or "January", "February", ..., you've
embedded data in the fieldname. This is NOT desirable in a relational
database.

Consider setting up a "many" (child) table that holds the studentID as a
foreign key, and holds two additional fields, "Subject/Syllabus" and
"Grade".

This way, any student will have as many tblSyllabus records as s/he has
Grades, but no more. And this way, you get to use the aggregate functions
that Access provides, since you only need to look "down", rather than across
a number of fields.

--
Good luck

Jeff Boyce
<Access MVP>

Jim Jones said:
Dear Jeff

Many thanks for your offer of help.

The table I am working with shows examination results for about 25,000
senior school students in a country in Southern Africa (which should perhaps
remain unidentified). In each record, there are fields for things like
School Number (Schools are called Centres) candidate number (called cand),
date of birth and gender. Then there are 12 fields (called syll1, syll2,
syll3, etc)identifying the subjects (subject are known as syllabuses) taken
by the student and the grade awarded. Most students do 7 or 8 subjects in
which case the remaining fields are null. The first 4 bytes of the syll
fields identifies the syllabus with a 4 digit numeric code; the next 2 bytes
shows the grade (*, A, B, C .....G) sometimes **, AA, BB, CC etc. A typical
record looks like this - set out vertically for convenience:

Centre: AA520
Cand: 1234
Name: Jim Jones
DoB: 01/01/1985
Gender: M
Syll1: 1650A (grade A in English Language)
Syll2: 3650C (grade C in Mathematics)
Syll3: 9650DD (grade DD in Double Science)
etc

This particular query is intended to identify candidates who have grade C or
better in 5 or more subjects. The query is applied to each Syll in turn - if
the grade is C or better, the counter C_S is incremented by 1 - hence the
apparent need for the same query to be applied to each of 12 fields in turn.
I then have apply the query again to increment again if the subject is one
with 2 grades (like Double Science above).

I am applying the queries (about 27 altogether) using a single macro which
returns the total number of students in each school and the number with 5 C's
or better. It works, but there must be a better way - hence my query this
morning. As ever, any help will be much appeciated.

Pehaps this is a good opportunity to record how much help I am receiving via
the Newsgroup. I post something two or three times a week (twice today!),
and the help I receive solves the immediate problem and my working knowledge
of Access is always improved by the experience. A service of this kind is
particularly useful for someone such as myself working in isolation and
dependent on some good books and the help screens.

Thanks again

Jim Jones




In this particular query, I am identifying candidates who have grade C or
better in 5 or more subjects

Jeff Boyce said:
Jim

It may be that there is an issue with your table structure, if you are
finding it necessary to "walk through" fields in the table (12?) to handle
an update.

If you provide a bit more description of the underlying data structure (and
an example of the data), the 'group may be able to offer alternate
approaches ...

--
Good luck

Jeff Boyce
<Access MVP>

Jim Jones said:
I am new to SQL and I am trying to use UNION for the first time in
Click to expand...
order
to
combine the effect of a number of similar update queries. One of the queries
is as follows:

UPDATE Results SET Results.C_S = C_S+1
WHERE (((Mid([syll1],5,1)) = "*" or (Mid([syll1],5,1)) = "A" or
(Mid([syll1],5,1)) = "B"));

The intention is to increment the value of C_S in a table called Results
according to the value of the fifth character in another field called syll1.
Having done so, I then need to increment C_S again according to the
Click to expand...
value
of
the corresponding character in the field syll2 - then syll3, etc up to syll12.

The description of the UNION operation indicates that its use is relevant
here, but so far I have no progress - perhaps I should be doing it
Click to expand...
some
other
way.

Any help would be much appreciated.

Jim Jones
Botswana
Click to expand...
Click to expand...
Click to expand...
 
Back
Top