Combining Variable Multiple Fields in a Query

[Davrini]

Hi all,

I have been asked to create a pilot database to use for Student Placements within our department at our University. My main criteria is that it has to be as user-friendly as possible, so I'm hoping to build something with a bit of automation in it.

One of the things I am trying to implement at the moment is a Query that pulls data from three separate tables and combines them to create a 'Course Code' so that it is easy to identify immediately in one field which year/course/stage students are at on any given course within our department. I will then use this query as a Combo box that the Tutors will use to identify the appropriate year/course/stage of their training to make input as user-friendly as possible.

My problem is this - not each course has the same number of years it runs for. As an example, I have four courses; one is 1 year long, two are 2 years long, and one is 4 years long. What I want a query to do is return a list of all possible variations of year/course/stage (that have been marked as active in the main table - that part I got covered).

Eventually, I want it to look a little like this:

CBP 15-16 Yr1
CBP 15-16 Yr2
INT 15-16 Yr1
INT 15-16 Yr2
SUP 15-16 Yr1
SYS 15-16 Yr1
SYS 15-16 Yr2
SYS 15-16 Yr3
SYS 15-16 Yr4
SYS 16-17 Yr1
SYS 16-17 Yr2
...etc.

This means that in the future, it is possible to add other courses and years and the list will automatically generate itself.

My question is, how do I do this? I have attached a screenshot of how I am recording the data and an example of how I want it returned (tried an IIf, but that only returns one value).

Any feedback would be greatly appreciated!

Many thanks all,
Davrini

 

[Minty]

You shouldn't be storing the Year1, Year2 etc fields in that way.
Can you list your tblCourse Fields completely as I suspect they are not well normalised, and any other tables you have. May be a picture of the relationships window is the best way.

 

[MarkK]

The result in your picture looks right. I don't understand the problem.

One thing for me is that I wouldn't record the duration of something using check boxes. If you have 1 year courses, 2 year courses, 4 year courses, consider representing this dimension of your object with a single number rather than four check boxes. Then, just read that number.

But you can also write a query that adds boolean values in a row, like...

SELECT Abs(Check1 + Check2 + Check3 + Check4) AS CheckSum
FROM Table

...which basically gives you a count the checked items in that row.