Can I structure my fields this way?

[danseuse]

It's been a loooong time since I've worked with Access. I want to be able to do queries on 'Class'.

My 'Class' table:

Fields: Class, Element 1, Element 2, Element 3
Data: Class A, Red, Maroon, Cerise
Class B, Violet, Mauve, Purple
Class C, Emerald, Forest, Lime

My 'Rooms' table:

Fields: Room, Colours
Data Room 1, Purple Red Violet Lime
Room 2, Maroon Forest Cerise

I want to query by Class such that if I query on Class B, it will give me Room 1 in the results (because Room 1 has Purple in it which is in Class B).

In the end I will have many rooms, each with many colours but because the colours belong to classes, I want to be able to query on the class rather than constructing lengthy queries for all the colours in a class.

Question 1: In the 'Rooms' table, can I use a single 'colours' field and enter many colours in it (each would have a different class) or must I create many 'colours' fields?

Question 2: how do I structure the tables such that this query will work?

(I think I used to know this but it was ages ago).

Thanks much.

[nico5038]

To get correct normalized tables you'll need to use just one Element in a table like:

Class A, Red
Class A, Maroon
Class A, Cerise
Class B, Violet
Class B, Mauve
Class B, Purple
etc.

Having this setup you can join to the other table by the colorfield.

The present setup would require a union like:

select class,element1 from tblClass
UNION
select class,element2 from tblClass
UNION
select class,element3 from tblClass;

This will deliver the normalized data and then the query could be used to join by element to the other table.

Getting the idea ?

Nic;o)

[danseuse]

Ah yes! 'Normalization' - it's arising from the foggy mists of memory. That's what's wanted! I'll give that a try. Thanks!

[nico5038]

Just keep me posted about the progress :-)

Nic;o)

[NeoPa]

************ Misunderstanding - Please Ignore **********
I've just reread Nico's post and realised I'd got the wrong end of the stick. He was actually talking about how to convert the present data into the correctly designed table layout. In this case he was absolutely correct and I should have guessed :(
************************************************** ***********

select class,element1 from tblClass
UNION
select class,element2 from tblClass
UNION
select class,element3 from tblClass;

It's a minor detail but, to avoid confusion, the second field of the SELECT clause should be the same in all cases as they refer to the same field in the same table.
So, the SQL should be like :

select class,element from tblClass
UNION
select class,element from tblClass
UNION
select class,element from tblClass;

HTH.

[nico5038]

Hi, NeoPa.

Sorry to correct you, but all a UNION requires is the same number of columns and the same datatype.
The Element1, Element2, etc can be in separate rows, as long as they are all text, number or date. The result will however use the fieldnames of the first select, so to get just Element, use for the first:
select Class, Element1 as Element from tblX

The other selects won't change the name the query returns.

Nic;o)

[NeoPa]

You're absolutely right Nico (and apology not necessary even if you had not been).
However, that wasn't my confusion - I was further off base than that even :( I got completely the wrong end of the stick.
I was thinking you were running the UNION query on the data that actually you were producing. A two column table - hence my post. I was aware of the point you make here (but it never hurts for it to be expressed publicly).
Preferably I would have deleted the post completely but contributors would likely be confused if they'd had an email just to find a gap where the post had been.