Query as a spreadsheet model

[shacdactvm]

Hello there,

I want to create one query as follows. I have one query 'ManpowerMain' and a table 'Assesment'. I want to join this. Both having a primary key 'EmpID'.

Assesment Table Structure

ID EmpID AYear Mark Grading
1 123456 12-31-2013 9.20 Outstanding
2 123456 12-31-2011 8.50 Excellent
3 123456 12-31-2012 8.20 Excellent

ManpowerMain query structure

ID EmpID Name GP Position
1 123456 ABC 2400 Engineer
2 456789 XYZ 6600 Scientist

What I want is :

EmpID Name Mark in 2011 Mark in 2012 Mark in 2013
123456 ABC 8.50 8.20 9.20



Please help me.. I want to do it at the earliest

Sha

 

[Frothingslosh]

What you are going to want is a CROSSTAB QUERY.

The first thing you should do is create a query. Use both the table and the query you provided in your post, joined on EmpID. In the query builder use these fields:

• EmpID
• Name
• AssessmentYear: "Mark in " & DatePart("yyyy",AYear)
• Mark

Then click on the 'Crosstab' button on the Ribbon (in the Design section).

Next, you'll see every field now has both a 'Crosstab' line and a 'Total' line in the grid.

For EmpID, set Total to 'Group By' and Crosstab to 'Row Heading'.
For Name, set Total to 'Group By' and Crosstab to 'Row Heading'.
For AssessmentYear, set Total to 'Group By' and Crosstab to 'Column Heading'.
And for Mark, set Total to 'Max', and Crosstab to 'Value'.

That should get you what you want.

 

[shacdactvm]

@Frothingslosh

Thanks a lot.....I done it

 

[shacdactvm]

BTW, How can I show an average column to the right of this query as below.

EG:

EmpId-----Name --------2011-------2012--------2013---------2014---AVG
123456------ABC----------9.2---------8.0----------8.5-----------7.6----8.33
456789------MNO---------8.5---------8.5----------9.5-----------6.6----8.28
345678------STU----------8.8---------8.0----------8.5-----------9.6----8.73


Please help

 

[stopher]

Go into the design view of of the query again.

Add a new column as follows:
Field> AVRG: [Mark]
Table> Your table
Total> Avg
Crosstab> Row Heading

Run your query. The avrg will probably display on the left of the other values but you can drag it to the right.

 

[shacdactvm]

@stopher
When I followed you, I am getting "Invalid precision for decimal data type" message.

please check attached query builder screenshot and please guide me to do the same.

thanks

 

[stopher]

@stopher
When I followed you, I am getting "Invalid precision for decimal data type" message.

please check attached query builder screenshot and please guide me to do the same.

thanks

How have you set Mark up? i.e. what data type, decimal places, precision etc.

Can you post a cut down version of your database with dummy data to show the problem?

 

[shacdactvm]

A Sample db is attached for your reference. please check

 

[stopher]

I can't replicate your problem but I've added the average into your sample query and it works fine for me. See attached.