Cross Referenced Data Form

  • Thread starter Thread starter jrmfzf
  • Start date Start date
J

jrmfzf

I think that this is something that should be quite simple to solve,
but I
don't know how to word it well enough in a search to find a solution.
Basically, I have data that's dependent on two keys in a table. E.g.
think
of a class schedule where you could have days of the week down the side
and
the time of the day across the top so that a particular class will only
fall
on a given day and time. Now, I just need to get this look into a
form. I'm
no Access whiz, but every technique I try only shows both sets of
qualifiers
down the side, making a really long list instead of a compact table on
the
form. Any suggestions will be appreciated.
 
If your record/query is so structured, i.e. separate fields for StudentID,
ClassDay and ClassTime, you can use a crosstab query:

TRANSFORM Min(Table1.ClassName) AS MinOfClassName
SELECT Table1.StudentID, Table1.ClassDay
FROM Table1
GROUP BY Table1.StudentID, Table1.ClassDOW, Table1.ClassDay
PIVOT Table1.ClassTime;

This will give a separate line for each student, and day of week in which
he/she has a class. Each individual class time will be shown as a column.
Add a "WHERE" to limit to one student.

BruceS
 
Thanks Bruce,
I really appreciate the responce. That did work great and was exactly
what I was looking for. Now I want to take it one step further and add
one more key that this value is dependent on. Using the same example
as above I'd like to use one report that shows days of the week down
the side and time of day across the top. Now, I'd like to use an
option group (or something along these lines) where when I select
Student A, the form dispalys his schedule, when I select Student B, his
schedule, etc. I'm trying to do this and I'm having a lot of problems
with different values not being recognized and the query getting
confused. Thanks in advance.
 
Back
Top