Ordering data from a query

[kulanga]

Hi,
I am newbie to Access. The database that I have designed has 4 fields namely BedNo, Dislike1,Dislike2 and Dislike3.
The primary key is BedNo.
I tried to create a select query to group the data according to the values in the Dislike columns. For example values in the Dislike columns can be 'Eggs', 'Soup' etc.


The result I need to get is for example is to group the Bedno's which dislike 'Eggs', 'Soup' etc.

Thank you in advance.

[RuralGuy]

That just involves an OrderBy Clause.

[kulanga]

Thank u RuralGuy but the thing is that the values in the Dislike columns is random and differs from bed to bed.
The columns look like the following.
Bedno|Dislike1 | Dislike2 | Dislike3
1A | Eggs | Soup |
2A | Chicken|Icecream|

Thank You

[RuralGuy]

It still sounds like a sorting problem to me.

[pbaldy]

I think it's a normalization problem (repeating fields). If the table were normalized like

1A Eggs
1A Soup
2A Chicken
2A Ice cream

it would be a simple matter to find the bed numbers that don't like Eggs.

[kulanga]

Thank You Paul and Rural Guy. I have already done a query for this. The SQL coding looks like the following:

SELECT ResidentsMasterTable.BedNo
FROM ResidentsMasterTable
WHERE (((ResidentsMasterTable.Dislikes1) Like '*Eggs*')) OR (((ResidentsMasterTable.Dislikes2) Like '*Eggs*')) OR (((ResidentsMasterTable.Dislikes3) Like '*Eggs*')) OR (((ResidentsMasterTable.Dislikes4) Like '*Eggs*')) OR (((ResidentsMasterTable.Dislikes5) Like '*Eggs*')) OR (((ResidentsMasterTable.Dislikes6) Like '*Eggs*')) OR (((ResidentsMasterTable.Dislikes7) Like '*Eggs*')) OR (((ResidentsMasterTable.Dislikes8) Like '*Eggs*')) OR (((ResidentsMasterTable.Dislikes9) Like '*Eggs*')) OR (((ResidentsMasterTable.Dislikes10) Like '*Eggs*'))
GROUP BY ResidentsMasterTable.BedNo;


Using this I get the BedNo's who dislike Eggs. But the result I would to get is let's say Eggs or Soup as a column name and BedNo as a Row name. I tried the pivot table option but didn't understand it much.

Thank You.

[pbaldy]

Given the sample data you posted above, what will your desired result look like? BTW, with 10 dislike fields, there's no question in my mind it should be designed differently.

[kulanga]

Hi Paul,
the desired result is:

Eggs | Soup| Tomatoes
1A |2A | 1A
3B | |

The query should group the bedno's according to the random values eg. Eggs or Soup in the Dislike columns.

[pbaldy]

I'm not sure how to achieve that; maybe Allan knows a way. The reverse would be fairly easy with the normalized design I mentioned:

Eggs 1A, 3B
Soup 2A
Tomatoes 1A

[RuralGuy]

There is probably a way to get the report desired but it seems to me that if you have too many items disliked you can run out of horizontal room on a report.