Dynamic Crosstab Form

R

Ryan

I know this question has been asked 100 times, however, I still have not seen
a solution. Here is my crosstab query.
PARAMETERS [Forms]![Parameters].[LocationFilter] Text ( 255 ),
[Forms]![Parameters].[StartDateFilter2] Text ( 255 ),
[Forms]![Parameters].[EndDateFilter2] Text ( 255 );
TRANSFORM Sum(AllCharges.BalDue) AS SumOfBalDue
SELECT AllCharges.FinClass, Sum(AllCharges.BalDue) AS [Total Of BalDue]
FROM AllCharges
WHERE (((InStr("," & Forms!Parameters.LocationFilter & ",","," & [db] &
","))>0))
GROUP BY AllCharges.FinClass
ORDER BY AllCharges.FinClass
PIVOT AllCharges.DB;

I have seen many posts telling people to switch to reports, which will not
work for me. As you can see from my query, my row headings will be from
AllCharges.FinClass, and my row headings will be from AllCharges.DB. There
can be 1 to 72 DB's to choose from in my parameter form. I would like to
have a form open that only shows the selected DB's. I know you can name the
DB's in the PIVOT statement, for example PIVOT AllCharges.DB
IN("C28","C48","C52"), but the parameter form lets them select which DB's
they want to see so hard coding the values will not work. In the end, I
would like my form to be a continous form that only shows the select DB's
from the parameter page. I have read about hiding controls and then
adjusting the contols position to keep them together, but I have not seen a
post on how to do this. Any help on this matter would be greatly
apprieciated.
 
K

KARL DEWEY

Am I missing something? What is the purpose of PARAMETERS
[Forms]![Parameters].[StartDateFilter2] Text ( 255 ) and
[Forms]![Parameters].[EndDateFilter2] Text ( 255 )?

I may have anb idea, so post some sample data.
 
R

Ryan

FinClass Total Of BalDue C28 C47
1 SELF PAY 11474 11474
10 AETNA 1963 320 1643
12 COMMUNITY CARE 200 200
2 MEDICARE 107700 57185 50515
21 UNITED HEALTHCARE 1627.7 640 987.7
25 MEDICARE HMO 320 320
3 MEDICAID 52800 28812 23988
4 BLUE CROSS/BLUE SHIELD 1986 873 1113
5 COMMERCIAL INSURANCE 1141 1141
7 CHAMPUS/TRICARE 6445 6445
8 WORKERS COMPENSATION 600 600
9 AUTO LIABILITY 589 116 473
--
Please remember to mark this post as answered if this solves your problem.


KARL DEWEY said:
Am I missing something? What is the purpose of PARAMETERS
[Forms]![Parameters].[StartDateFilter2] Text ( 255 ) and
[Forms]![Parameters].[EndDateFilter2] Text ( 255 )?

I may have anb idea, so post some sample data.

--
KARL DEWEY
Build a little - Test a little


Ryan said:
I know this question has been asked 100 times, however, I still have not seen
a solution. Here is my crosstab query.
PARAMETERS [Forms]![Parameters].[LocationFilter] Text ( 255 ),
[Forms]![Parameters].[StartDateFilter2] Text ( 255 ),
[Forms]![Parameters].[EndDateFilter2] Text ( 255 );
TRANSFORM Sum(AllCharges.BalDue) AS SumOfBalDue
SELECT AllCharges.FinClass, Sum(AllCharges.BalDue) AS [Total Of BalDue]
FROM AllCharges
WHERE (((InStr("," & Forms!Parameters.LocationFilter & ",","," & [db] &
","))>0))
GROUP BY AllCharges.FinClass
ORDER BY AllCharges.FinClass
PIVOT AllCharges.DB;

I have seen many posts telling people to switch to reports, which will not
work for me. As you can see from my query, my row headings will be from
AllCharges.FinClass, and my row headings will be from AllCharges.DB. There
can be 1 to 72 DB's to choose from in my parameter form. I would like to
have a form open that only shows the selected DB's. I know you can name the
DB's in the PIVOT statement, for example PIVOT AllCharges.DB
IN("C28","C48","C52"), but the parameter form lets them select which DB's
they want to see so hard coding the values will not work. In the end, I
would like my form to be a continous form that only shows the select DB's
from the parameter page. I have read about hiding controls and then
adjusting the contols position to keep them together, but I have not seen a
post on how to do this. Any help on this matter would be greatly
apprieciated.
Click to expand...
Click to expand...
 
R

Ryan

I am pulling this data from an AS400, and the files in the AS400 go back
almost 8 years. The reason for the StartDate and EndDate parameters is for
running reports for given dates.
--
Please remember to mark this post as answered if this solves your problem.


KARL DEWEY said:
Am I missing something? What is the purpose of PARAMETERS
[Forms]![Parameters].[StartDateFilter2] Text ( 255 ) and
[Forms]![Parameters].[EndDateFilter2] Text ( 255 )?

I may have anb idea, so post some sample data.

--
KARL DEWEY
Build a little - Test a little


Ryan said:
I know this question has been asked 100 times, however, I still have not seen
a solution. Here is my crosstab query.
PARAMETERS [Forms]![Parameters].[LocationFilter] Text ( 255 ),
[Forms]![Parameters].[StartDateFilter2] Text ( 255 ),
[Forms]![Parameters].[EndDateFilter2] Text ( 255 );
TRANSFORM Sum(AllCharges.BalDue) AS SumOfBalDue
SELECT AllCharges.FinClass, Sum(AllCharges.BalDue) AS [Total Of BalDue]
FROM AllCharges
WHERE (((InStr("," & Forms!Parameters.LocationFilter & ",","," & [db] &
","))>0))
GROUP BY AllCharges.FinClass
ORDER BY AllCharges.FinClass
PIVOT AllCharges.DB;

I have seen many posts telling people to switch to reports, which will not
work for me. As you can see from my query, my row headings will be from
AllCharges.FinClass, and my row headings will be from AllCharges.DB. There
can be 1 to 72 DB's to choose from in my parameter form. I would like to
have a form open that only shows the selected DB's. I know you can name the
DB's in the PIVOT statement, for example PIVOT AllCharges.DB
IN("C28","C48","C52"), but the parameter form lets them select which DB's
they want to see so hard coding the values will not work. In the end, I
would like my form to be a continous form that only shows the select DB's
from the parameter page. I have read about hiding controls and then
adjusting the contols position to keep them together, but I have not seen a
post on how to do this. Any help on this matter would be greatly
apprieciated.
Click to expand...
Click to expand...
 
K

KARL DEWEY

The data sample I was looking for is the raw data, not after the crosstab
does it's thing.
--
KARL DEWEY
Build a little - Test a little


Ryan said:
FinClass Total Of BalDue C28 C47
1 SELF PAY 11474 11474
10 AETNA 1963 320 1643
12 COMMUNITY CARE 200 200
2 MEDICARE 107700 57185 50515
21 UNITED HEALTHCARE 1627.7 640 987.7
25 MEDICARE HMO 320 320
3 MEDICAID 52800 28812 23988
4 BLUE CROSS/BLUE SHIELD 1986 873 1113
5 COMMERCIAL INSURANCE 1141 1141
7 CHAMPUS/TRICARE 6445 6445
8 WORKERS COMPENSATION 600 600
9 AUTO LIABILITY 589 116 473
--
Please remember to mark this post as answered if this solves your problem.


KARL DEWEY said:
Am I missing something? What is the purpose of PARAMETERS
[Forms]![Parameters].[StartDateFilter2] Text ( 255 ) and
[Forms]![Parameters].[EndDateFilter2] Text ( 255 )?

I may have anb idea, so post some sample data.

--
KARL DEWEY
Build a little - Test a little


Ryan said:
I know this question has been asked 100 times, however, I still have not seen
a solution. Here is my crosstab query.
PARAMETERS [Forms]![Parameters].[LocationFilter] Text ( 255 ),
[Forms]![Parameters].[StartDateFilter2] Text ( 255 ),
[Forms]![Parameters].[EndDateFilter2] Text ( 255 );
TRANSFORM Sum(AllCharges.BalDue) AS SumOfBalDue
SELECT AllCharges.FinClass, Sum(AllCharges.BalDue) AS [Total Of BalDue]
FROM AllCharges
WHERE (((InStr("," & Forms!Parameters.LocationFilter & ",","," & [db] &
","))>0))
GROUP BY AllCharges.FinClass
ORDER BY AllCharges.FinClass
PIVOT AllCharges.DB;

I have seen many posts telling people to switch to reports, which will not
work for me. As you can see from my query, my row headings will be from
AllCharges.FinClass, and my row headings will be from AllCharges.DB. There
can be 1 to 72 DB's to choose from in my parameter form. I would like to
have a form open that only shows the selected DB's. I know you can name the
DB's in the PIVOT statement, for example PIVOT AllCharges.DB
IN("C28","C48","C52"), but the parameter form lets them select which DB's
they want to see so hard coding the values will not work. In the end, I
would like my form to be a continous form that only shows the select DB's
from the parameter page. I have read about hiding controls and then
adjusting the contols position to keep them together, but I have not seen a
post on how to do this. Any help on this matter would be greatly
apprieciated.
Click to expand...
Click to expand...
Click to expand...
 
R

Ryan

There are to many colunms to show in this form. I would be happy to send you
the data somehow if it will help? Is there a way for you to explain your
idea without seeing the data?


KARL DEWEY said:
The data sample I was looking for is the raw data, not after the crosstab
does it's thing.
--
KARL DEWEY
Build a little - Test a little


Ryan said:
FinClass Total Of BalDue C28 C47
1 SELF PAY 11474 11474
10 AETNA 1963 320 1643
12 COMMUNITY CARE 200 200
2 MEDICARE 107700 57185 50515
21 UNITED HEALTHCARE 1627.7 640 987.7
25 MEDICARE HMO 320 320
3 MEDICAID 52800 28812 23988
4 BLUE CROSS/BLUE SHIELD 1986 873 1113
5 COMMERCIAL INSURANCE 1141 1141
7 CHAMPUS/TRICARE 6445 6445
8 WORKERS COMPENSATION 600 600
9 AUTO LIABILITY 589 116 473
--
Please remember to mark this post as answered if this solves your problem.


KARL DEWEY said:
Am I missing something? What is the purpose of PARAMETERS
[Forms]![Parameters].[StartDateFilter2] Text ( 255 ) and
[Forms]![Parameters].[EndDateFilter2] Text ( 255 )?

I may have anb idea, so post some sample data.

--
KARL DEWEY
Build a little - Test a little


:

I know this question has been asked 100 times, however, I still have not seen
a solution. Here is my crosstab query.
PARAMETERS [Forms]![Parameters].[LocationFilter] Text ( 255 ),
[Forms]![Parameters].[StartDateFilter2] Text ( 255 ),
[Forms]![Parameters].[EndDateFilter2] Text ( 255 );
TRANSFORM Sum(AllCharges.BalDue) AS SumOfBalDue
SELECT AllCharges.FinClass, Sum(AllCharges.BalDue) AS [Total Of BalDue]
FROM AllCharges
WHERE (((InStr("," & Forms!Parameters.LocationFilter & ",","," & [db] &
","))>0))
GROUP BY AllCharges.FinClass
ORDER BY AllCharges.FinClass
PIVOT AllCharges.DB;

I have seen many posts telling people to switch to reports, which will not
work for me. As you can see from my query, my row headings will be from
AllCharges.FinClass, and my row headings will be from AllCharges.DB. There
can be 1 to 72 DB's to choose from in my parameter form. I would like to
have a form open that only shows the selected DB's. I know you can name the
DB's in the PIVOT statement, for example PIVOT AllCharges.DB
IN("C28","C48","C52"), but the parameter form lets them select which DB's
they want to see so hard coding the values will not work. In the end, I
would like my form to be a continous form that only shows the select DB's
from the parameter page. I have read about hiding controls and then
adjusting the contols position to keep them together, but I have not seen a
post on how to do this. Any help on this matter would be greatly
apprieciated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error '3061' 2
Multiple Parameter Crosstab query filter by form 1
Crosstab Parameter Decleration 2
Crosstab Sum 7
Crosstab column heading order 5
Parameter query behavior in form vs. subform 1
insert a crosstab in to temp table query 2
Include empty values in crosstab query 5

Top