Hello Tom,
Thanks for your response. I'll try and fill in some of
the blanks to hopefully make this a bit clearer...
The main table is called Records which includes the
details of the Candidates such as:
RecNo (PK Autonumber)
SchoolID (five-digit No)
CandID (four-digit No)
Name of Candidate (text)
Component (look-up field to Component table)
Mark (number)
This is the table structure of the other tables:
**EntryCodes**
EntryCode (PK)
EntryCodeTitle (Text)
**LinkTable**
LinkID (PK AutoNumber)
EntryCode (look-up field to EntryCodes table)
Component (look-up field to Components table)
**Components**
Component (PK)
ComponentTitle (Text)
MaximumMark (number)
When I say I want the query to find candidates with the
same SchoolID and CandID I mean that there could well be
records in the Records table for the same candidate and
the way I determine they're the same candidate is by
seeing if they have the same SchoolID AND the same CandID
in each record.
A bit of background to clarify what this is all about:
Normally, schools tell my organisation which candidates
(pupils) will be taking which examinations in advance.
They do this by providing a list of candidate names (with
a unique CandID for each) together with EntryCodes. (The
EntryCodes tell us which Components (Papers) the
candidates will be attempting). We then provide the
schools with all the necessary materials to take the
exams and we then mark all the work submitted by the
candidates.
This Access database is to record instances where
candidates have been allowed to take exams that the
schools haven't previously notified us about.
As we don't have a proper 'entry' for these candidates we
don't have anywhere to 'hang' the marks on our main
systems so this database is used as a repository for all
the information that we gleen from processing
these 'unexpected' marks. So, if there isn't any record
of the candidate for the Component that we have a mark
for on the main systems, someone will key the details of
this rogue candidate/component/mark into this Access
Database. When we are able to get a complete set of
marks for an EntryCode we are then able to make a formal
entry on our main systems in order that the rest of the
processing may continue and results issued alongside all
the other candidate's results.
We'll probably end up with about 20,000 'rogue' marks in
this database but the information is being added to all
the time. Today we may only have a single mark for
Candidate X but by the end of the week we may have a
complete set. Up 'til now, I've got the database to
produce reports of candidates/components/marks by
EntryCode. My staff then look for complete sets within
an EntryCode but as I say, it would be helpful if I could
produce a report (or form) that at least shows where we
already have a set.
I'm sorry this is all rather complicated and that I've
rambled on rather but I hope this makes things a little
clearer.
Regards,
Lee
-----Original Message-----
Dear Lee:
I'm going to try to restate your post in the technically specific terms to
which I am accustomed. I do this in part to confirm the details you have
represented so you can correct my understanding.
There is a many-to-many relationship between tables [EntryCodes] and
[Components] which is mapped using table [LinkTable].
The unique key to [EntryCodes] represented in [LinkTable] is field(s)
__________ (fill in the blank).
The unique key to [Components] represented in [LinkTable] is fields(s)
__________ (fill in the blank).
Now some questions about the rest of your description.
"My database allows us to records the marks for each of these (and many
other) Components."
Are these recorded in [LinkTable] or somewhere else?
"I'd like the query to find candidates with the same SchoolID and CandID"
Do you really mean that a SchoolID would be the same as a CandID? If not,
then SchoolID is to be the same as what? CandID the same as what?
You might want to try a narrative description of the problem in real-world
terms. I cannot imagine what is being stored in [EntryCodes] for example.
What is the entity being represented there. Same for [Components], and so
forth.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts
Hello all,
I wonder if you can offer any suggestions on how I might
achieve the following:
I have a table called EntryCodes and one called
Components and they are linked via a joined table called
LinkTable. This LinkTable shows how the EntryCodes and
Components are linked to each other. For example:
For 01211 (Geography), the EntryCode is linked to three
Components in the LinkTable as:
01211 01211/1 (paper 1)
01211 01211/2 (paper 2)
01211 0121/C (coursework)
My database allows us to records the marks for each of
these (and many other) Components. What I would like to
do is to run a query that shows me when there are
candidates with marks recorded for all Components that
make up an EntryCode (ie when there are a full set of
marks for ANY EntryCode). The details of a Candidate are
stored in a table called Records and this includes the
SchoolID, the CandID and each Component we have received
marks for.
So, to summarise, I'd like the query to find candidates
with the same SchoolID and CandID and where there are a
set of marks that constitute an EntryCode. Just to
complicate matters, a Component can relate to more than
one EntryCode. For example:
01211 01211/1
01211 01211/2
01211 0121/C
01212 01212/1
01212 01212/2
01212 0121/C
I hope I've explained myself clearly enough. Any advice
would be much appreciated.
Lee
.
