Solved Crosstab Query (1 Viewer)

TajikBoy

Member
Local time
Today, 16:59
Joined
Mar 4, 2011
Messages
82
Following from my other thread, I managed to create the crosstab query Pat was suggesting, DB attached

Now, issue is the column headings and filtering

I have inserted the criteria based on the form variables, and fixed the column headings (only way I managed to make it work.. as propertyIDs) - when I chose from dropdowns, query window disappears and comes back with nothing (I know there's data to display)

Also, column headings are driven by PropertyID, which has corresponding property name in tblPropertyRegistrations, is there anyway we can link those? (this is not a biggy, ideally I want to display Property 1,2,3 (3 columns in total or maybe 4) and limit to the selection above in dropdowns)

I appreciate your time and assistance on this and million thanks
 

Attachments

  • SalaryTest v2.accdb
    1.1 MB · Views: 440

June7

AWF VIP
Local time
Today, 15:59
Joined
Mar 9, 2014
Messages
5,423
If you want property name in crosstab data, then include tblPropertyRegistration in the CROSSTAB query and include PropertyName as a RowHeading field.

Parameterized query as set up requires input in every criteria. However, query errors with "expression is typed incorrectly, or is too complex to be evaluated ..." Why is [Forms]![propSalaryScale]![Property1] a criteria under Department field? Remove it and error goes away. So fix this criteria reference.

Since there is a criteria for Department, Master/Child Links shouldn't be needed.
 
Last edited:

TajikBoy

Member
Local time
Today, 16:59
Joined
Mar 4, 2011
Messages
82
If you want property name in crosstab data, then include tblPropertyRegistration in the CROSSTAB query and include PropertyName as a RowHeading field.

Parameterized query as set up requires input in every criteria. However, query errors with "expression is typed incorrectly, or is too complex to be evaluated ..." Why is [Forms]![propSalaryScale]![Property1] a criteria under Department field? Remove it and error goes away. So fix this criteria reference.

Since there is a criteria for Department, Master/Child Links shouldn't be needed.
HI JUne7,

Thanks for the input,

In the above attached DB, only look at query1, other ones are workings, testbeds etc

AS you can see from the form, I have 3 dropdowns for property, and the only way this form works if I fix the column headings in the query setup (check out query1 setup, you will see 33,35,37,48 - which defeats the dropdowns and the user is unable to change comparison property - that's what I am trying to achieve, I guess this could only happen via VBA?
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:59
Joined
Sep 21, 2011
Messages
14,048
You cannot link propertyname to propertyid in salary table as the propertyid in PropertyName is text? :(
 

June7

AWF VIP
Local time
Today, 15:59
Joined
Mar 9, 2014
Messages
5,423
@Gasman, link is between ID in tblPropertyRegistration and PropertyID in tblSalaryScale.

There is only one query in the provided db so there aren't any others for me to look at.

I just noticed that PropertyID is ColumnHeading, not RowHeading. Could change this to use PropertyName as ColumnHeading after joining tables. Either way, this would certainly result in more than 3 columns. And criteria of tblSalaryScale.PropertyID IN (1,2,3) does not make sense as there are no PropertyID with those values. Apparently, you do not understand how this parameter actually works.

I am not sure your WHERE criteria do what you want.

Also, why SUM on ID field? Should this be COUNT or SUM on Salary?
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:59
Joined
Sep 21, 2011
Messages
14,048
@Gasman, link is between ID in tblPropertyRegistration and PropertyID in tblSalaryScale.

There is only one query in the provided db so there aren't any others for me to look at.

I just noticed that PropertyID is ColumnHeading, not RowHeading. Could change this to use PropertyName as ColumnHeading after joining tables. Either way, this would certainly result in more than 3 columns. And criteria of tblSalaryScale.PropertyID IN (1,2,3) does not make sense as there are no PropertyID with those values. Apparently, you do not understand how this parameter actually works.

I am not sure your WHERE criteria do what you want.

Also, why SUM on ID field? Should this be COUNT or SUM on Salary?
OK, I cannot work like that and will bow out. :(
What is the point of calling that field PropertyID? :(
 

Users who are viewing this thread

Top Bottom