How to use two value field in a crosstab query? (1 Viewer)

[Nicesoni_ash]

Hi

I am trying to make a crosstab query to filter my records from my table.

Here is the scenario.

I want to make a query that will return me my Rep ID, Rep Name, his Bonus and his GV-Q (another value) based on every month.

Now I make a crosstab query and here is the syntax.

TRANSFORM First([TBL Qualification Data India].[Bonus Rank]) AS [FirstOfBonus Rank]
SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
FROM [TBL Qualification Data India]
GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
PIVOT [TBL Qualification Data India].Period;

This resulted in a column for Rep Number, one column for Rep Name and columns for all the period of Bonus I am going to have., so there are basically 9 columns for this till this month for each month and bonus value shows as values for all these month (period) columns.

Now in this same syntax, I want to have my Rep GV-Q value as well as his bonus to show in the same query, I read and came to know that it's not possible to directly have two values or two column headings in a crosstab query, I must have to make a new crosstab query and then use a normal select query to display records from these two crosstab queries, so I went ahead and made a new similar but with one value field changed crosstab query and here is the syntax for that.

TRANSFORM First([TBL Qualification Data India].[GV-Q]) AS [FirstOfGV-Q]
SELECT [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
FROM [TBL Qualification Data India]
GROUP BY [TBL Qualification Data India].[Rep #], [TBL Qualification Data India].[Rep Name]
PIVOT [TBL Qualification Data India].Period;

Now after this I have no idea how to make a select query to show the data from these two queries.

I can make a normal query based on these two crosstab queries and manually add all fields and then I would have my result but then after every month I have to manually enter these two extra month details from both crosstab queries to my final query and that's not what I want.

Is there any method to do this by gathering data from these two queries into one and achieve the result I want or if there is any other approach to tackle this.

To explain my database and my need for output, I am attaching few pics to make things easier if I made some mistakes in explaining my problem. It's included in attached zip since I am not able to post images or links.

I would really appreciate if someone could help. Thanks.

 

[Nicesoni_ash]

I found an MS help topic for the same problem but that is also doing the same thing. It's based on a table that has static values, my table keeps updated every month

http: //support. microsoft. com/kb/304458I

Remove spaces please.

 

[Galaxiom]

I have not looked at your problem but maybe this kind of thing is what you are looking for.

http://www.access-programmers.co.uk/forums/showthread.php?t=221397

 

[Nicesoni_ash]

That link didn't help me. I am sorry but I am not familiar with SQL so I couldn't gather what that post implicates. To make matter easy, I have attached my database file along with the screenshots of what I desire, if you or anyone can have a look at suggest me what to do, I will be obliged.

Thanks.

 

[Nicesoni_ash]

Can I use a union query or something like that?

 

[Nicesoni_ash]

I tried this union code,

SELECT *
FROM BONUS
union
SELECT *
FROM [GV-Q];

That almost did the trick but instead of two separate columns for each month for two different values, I have only one column for each month and two rows for each employee having these two different values.

It's somewhat good but not what I want. I want to have two separate columns for each month and then a single record for each employee.

Can anyone put some insights please.

This is what I have..

Rep # Rep Name 2010-11 2010-12
1 Ashok Soni 78930 4887
1 Ashok Soni CAN CONS
5 Even Thomas 0 0
5 Even Thomas CONS CONS

This is what I want.

Rep # Rep Name BONUS.2010-11 GV-Q.2010-11
1 Ashok Soni Test 78930
5 Even Thomas CONS 0

Any help is really appreciated.