Help!!

[ERTW]

Hi there,

I've just started using Access and databases in general.
I have a list of course timetables that need to be grouped.

course section day time
`````` ``````` ``` ````

xxxx123 101
xxxx123 L1A
xxxx123 L1B


Is it possible to write a query that returns the possible combinations.. one with section 101 and L1A, the other with section 101 and L1B? There are about 15 courses, each with different sections.

much obliged,
ERTW

 

[RickSpr]

Maybe. You can create what's called a "self-join" as follows: Create a new query, add your table to it twice, and drag the Course field from one and drop it on the other. That will give you all the "one from column A and one from column B" combinations, which is more than you want.

You can then use criteria to eliminate what you don't want. First, notice that Access has given the second copy of the table a unique name. I'll refer to them as "Courses" and "Courses1". Drag your Course and Section from Courses to the grid. Also drag your Section from Courses1 to the grid. In the Criteria line under that one, enter "> Courses.Section".

The following list shows all the permutations that the query without the criteria would have returned. Permutations that are eliminated by the criteria are marked with an "*":

101-101 *
101-L1A
101-L1B
L1A-101 *
L1A-L1A *
L1A-L1B
L1B-101 *
L1B-L1A *
L1B-L1B *

That leaves you with 101-L1A, 101-L1B, and L1A-L1B, which I imagine is what you were looking for.

One warning: This query will not include any courses that have only one section! Rick Sprague

 

[ERTW]

Thanks Rick,

Pretty neat idea. However, I only need combinations of a lecture section ( eg 101 ) and a lab section ( L1x), which means just 101-L1A and 101-L1B, and not L1A-L1B.
Also there are instances where there are 2 lecture sections (101 and 102) but I want only one to show up in each possibility.

ERTW

 

[RickSpr]

Well, that's even easier. Assuming lecture sections start with a digit and lab sections start with an "L", just pair a lecture section with each lab. In Criteria for Section from the Courses table, enter "Like [1-9]*". for Section from the Courses1 table, enter "Like L*". That should do it. Rick Sprague