Solved: Complex query

[wpanssi]

I need to create a complex (at least for me) query with access. I have the tables in the attachment also. It may be easier to look the tables in excel.

I have a table data:

Person Wish 1 Wish 2
person 1 A C
person 2 B A
person 3 C A
So I have different persons and each person has two wishes. A wish presents a task that the person wants. So everyone can apply for two tasks.

I have also table task:
task
A
B
C

And from these two tables I would need to create query that results in something like this:
Task Person Wish 1 Wish 2
A person 1 A C
A person 2 B A
A person 3 C A
B person 2 B A
C person 1 A C
C person 3 C A
So I can easily check all the people who have applied for the task A and all the people who have applied for the task b and so on.

I'm not sure do I need vba to achieve this. I certainly hope I can just make a query since I haven't played with access vba..

Could someone please help me?

Thanks in advance!

[wpanssi]

I also attached a database which has the data and the task tables.

[OBP]

wpanssi, the query that you want to produce is actually quite straight forward to do, unfortunatley I cannot create it for you as I do not have Access 2007.
So what I suggest that you do is to build it in stages, so that you can see and test each stage.
1. use the query wizard to create a simple "Select" query (which is the default) based on your data table. this should just list all of the records from the data table for you.
2. With the data table query in Design view add the Task table to the query. To do this you click on the "Show Table" Icon on the main Menu.
3. If you have relationships set up the relationship between the tables should also appear. Drag the fields that you want to use in the Tasks table down on to the query Grid.
When you run the query now it should show you all of the records for each person with the tasks as well.
4. Drag the Task Field over to the left hand edge of the Grid, so that it is the first column. Set it's Sort Row to Ascending.
5. On the main menu click on View>Totals and the "Groups" will appear.
When you run it now the A tasks should be grouped together and then the B tasks etc.

I am working from memory so I hope it gives you what you need, it will certainly give you a good starting point.

[wpanssi]

I forgot to mention that in the results I need to have the following condition:
Wish1 or Wish2 has to be the same as Task.

But anyways I think I was able to make it work with the condition also. I have to test with more complex data, though. I have never really understood the Group by thingy, have to work with it.

Thanks for the help!

[OBP]

great, can you mark the thread as solved please?