Solved Crosstab Query

[TajikBoy]

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

 

[June7]

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.

 

[TajikBoy]

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]

You cannot link propertyname to propertyid in salary table as the propertyid in PropertyName is text?

 

[June7]

@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]

@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?