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.

That just involves an OrderBy Clause.

__________________
(RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 - acXP, ac07
If your issue is resolved...follow this link for directions on how to use the Solved thread tool!
Teaching is not filling a bucket but lighting a fire. Borrowed quote..."Docendo discimus"

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

It still sounds like a sorting problem to me.

__________________
(RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 - acXP, ac07
If your issue is resolved...follow this link for directions on how to use the Solved thread tool!
Teaching is not filling a bucket but lighting a fire. Borrowed quote..."Docendo discimus"

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.

__________________
Paul
MS Access MVP
www.BaldyWeb.com

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.

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.

__________________
Paul
MS Access MVP
www.BaldyWeb.com

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.

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

__________________
Paul
MS Access MVP
www.BaldyWeb.com

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.

__________________
(RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 - acXP, ac07
If your issue is resolved...follow this link for directions on how to use the Solved thread tool!
Teaching is not filling a bucket but lighting a fire. Borrowed quote..."Docendo discimus"