Solved Crosstab Question v2 (1 Viewer)

TajikBoy

Member
Local time
Today, 03:55
Joined
Mar 4, 2011
Messages
82
Hi Chaps,

Me again......

I have resolved few steps in my Crosstab query issue, now can filter for 2 fields ! But I need more.... As usual....

I have attached my sample DB, and this is what I need to do but have no idea how to

Table tblSalaryScale contains salary details by department/position/hotel and year (this list will grow by hotel count), created crosstab query "tblSalaryScale_Crosstab" is working as intended, lists all years,departments, positions as rows, and all the hotel data in columns - so far so good

Form1, I have number of Combo Boxes, working fine ones are Department and Year but I need/want to filter 5 particular hotels (could be 2,3 or 4 also) - ComboBoxes already in the form ,data for the data already filtered ie year, department and position - this is where I am failing and I don't want to display set selection of hotels, I want user to select which hotels to compare

Any ideas folks? I am really desperate.....
 

Attachments

  • Crosstab Problem.accdb
    2.1 MB · Views: 349

June7

AWF VIP
Local time
Today, 02:55
Joined
Mar 9, 2014
Messages
5,425
Add parameters for the comboboxes to use in WHERE clause.

PARAMETERS [Forms]![Form1]![Combo4] Text ( 255 ), [Forms]![Form1]![Combo20] Long, Forms!Form1!Combo9 Text ( 255 ), Forms!Form1!Combo15 Text ( 255 ), Forms!Form1!Combo18 Text ( 255 ), Forms!Form1!Combo27 Text ( 255 ), Forms!Form1!Combo38 Text ( 255 );
TRANSFORM Avg(tblSalaryScale.Salary) AS AvgOfSalary
SELECT tblSalaryScale.strYear, tblSalaryScale.Department, tblSalaryScale.Position
FROM tblSalaryScale LEFT JOIN tblPropertyRegistration ON tblSalaryScale.PropertyID = tblPropertyRegistration.ID
WHERE (((tblPropertyRegistration.PropertyName)=[Forms]![Form1]![Combo9])) OR (((tblPropertyRegistration.PropertyName)=[Forms]![Form1]![Combo15])) OR (((tblPropertyRegistration.PropertyName)=[Forms]![Form1]![Combo18])) OR (((tblPropertyRegistration.PropertyName)=[Forms]![Form1]![Combo27])) OR (((tblPropertyRegistration.PropertyName)=[Forms]![Form1]![Combo38]))
GROUP BY tblSalaryScale.strYear, tblSalaryScale.Department, tblSalaryScale.Position, tblPropertyRegistration.PropertyName
ORDER BY tblSalaryScale.Department
PIVOT tblPropertyRegistration.PropertyName;
 

mike60smart

Registered User.
Local time
Today, 10:55
Joined
Aug 6, 2017
Messages
1,899
Hi Chaps,

Me again......

I have resolved few steps in my Crosstab query issue, now can filter for 2 fields ! But I need more.... As usual....

I have attached my sample DB, and this is what I need to do but have no idea how to

Table tblSalaryScale contains salary details by department/position/hotel and year (this list will grow by hotel count), created crosstab query "tblSalaryScale_Crosstab" is working as intended, lists all years,departments, positions as rows, and all the hotel data in columns - so far so good

Form1, I have number of Combo Boxes, working fine ones are Department and Year but I need/want to filter 5 particular hotels (could be 2,3 or 4 also) - ComboBoxes already in the form ,data for the data already filtered ie year, department and position - this is where I am failing and I don't want to display set selection of hotels, I want user to select which hotels to compare

Any ideas folks? I am really desperate.....
Hi
In the attached the Form that opens at start-up is a Split Form.
You can use the small dropdown arrow alongside each Column Heading to Filter as required.
 

Attachments

  • Crosstab Problem.accdb
    656 KB · Views: 295

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:55
Joined
May 7, 2009
Messages
19,175
a little complicated but will do.
what version do you have, this is a2019.
 

Attachments

  • Crosstab Problem.accdb
    928 KB · Views: 333

TajikBoy

Member
Local time
Today, 03:55
Joined
Mar 4, 2011
Messages
82
Add parameters for the comboboxes to use in WHERE clause.

PARAMETERS [Forms]![Form1]![Combo4] Text ( 255 ), [Forms]![Form1]![Combo20] Long, Forms!Form1!Combo9 Text ( 255 ), Forms!Form1!Combo15 Text ( 255 ), Forms!Form1!Combo18 Text ( 255 ), Forms!Form1!Combo27 Text ( 255 ), Forms!Form1!Combo38 Text ( 255 );
TRANSFORM Avg(tblSalaryScale.Salary) AS AvgOfSalary
SELECT tblSalaryScale.strYear, tblSalaryScale.Department, tblSalaryScale.Position
FROM tblSalaryScale LEFT JOIN tblPropertyRegistration ON tblSalaryScale.PropertyID = tblPropertyRegistration.ID
WHERE (((tblPropertyRegistration.PropertyName)=[Forms]![Form1]![Combo9])) OR (((tblPropertyRegistration.PropertyName)=[Forms]![Form1]![Combo15])) OR (((tblPropertyRegistration.PropertyName)=[Forms]![Form1]![Combo18])) OR (((tblPropertyRegistration.PropertyName)=[Forms]![Form1]![Combo27])) OR (((tblPropertyRegistration.PropertyName)=[Forms]![Form1]![Combo38]))
GROUP BY tblSalaryScale.strYear, tblSalaryScale.Department, tblSalaryScale.Position, tblPropertyRegistration.PropertyName
ORDER BY tblSalaryScale.Department
PIVOT tblPropertyRegistration.PropertyName;
HI Mate,

Thanks for above update,

If you look at the Form1 design, I placed 5 unbound fields for the output, how do I link the query output to these? After updating with above, there are no fields available under 'add existing fields' ?

Erol
 

TajikBoy

Member
Local time
Today, 03:55
Joined
Mar 4, 2011
Messages
82
a little complicated but will do.
what version do you have, this is a2019.

If I can trouble you once more again, how can I transfer the data to a report?

In the original DB, I have a report designed which links to the form, but not sure how I can replicate your solution in the report?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:55
Joined
May 7, 2009
Messages
19,175
you can do the same with the report?
 

Attachments

  • Crosstab Problem.accdb
    756 KB · Views: 326

June7

AWF VIP
Local time
Today, 02:55
Joined
Mar 9, 2014
Messages
5,425
Right click on report in PrintPreview > Export.

or use

DoCmd.OutputTo
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:55
Joined
May 7, 2009
Messages
19,175
i used Macro but you can use VBA if you wish.
 

Attachments

  • Crosstab Problem.accdb
    928 KB · Views: 325

TajikBoy

Member
Local time
Today, 03:55
Joined
Mar 4, 2011
Messages
82
Ahhh now I see where I was going bananas, was trying to export via docmd.TransferSpreadsheet !

Much to learn, much to learn......

Thank you kindly sir
 

Users who are viewing this thread

Top Bottom