quickie about cross tabs

[Nitesh9999]

Is it only possible to have one value "crosstab"?

So the following crosstab query design is impossible?

Field..........Field A..........Field B............Field C.............Field D........Field E
Table all fro the same table
Total.......Group By........Group By.........Group By............Sum...........Sum
Crosstab..Row Heading...Row Heading...Column Heading....Value......Value


The clash would occur between Field D and Field E. If i was to Field E from this design everything would work fine.

Is there anyway round this? So i could have 2 "Value" crosstabs?????

Thanks

Nitesh

 

[Pat Hartman]

You need to create two crosstabs and then join them in a third query. The crosstab will only pivot 1 value.

 

[Nitesh9999]

Thanks Pat.

http://www.utteraccess.com/forums/showflat.php?Cat=&Number=670425&page=&view=&sb=5&o=&fpart=all&vc=1

Looks like the right thing to do. Attached is how "I" pulled it it off...

Maybe it can help Someone else.

Nitesh

 

[Nitesh9999]

Hey one other thing,

Ive put this query into a report...

The only other thing i need now to finish this paticular report in the DB is to be able to sum the rows across and the columns down. But when i do this simple "Sum" i get blank boxes when i preview the report. Not even the usual "error". I was wondeering if this has something to do with the multiple queries this report relies on, or wether its just a simple error on my behalf??? Attached is the Db, the report in questionis "all".

Thanks

Nitesh

 

[Pat Hartman]

The crosstab automatically produces a row sum column. You can just use that rather than creating a new calculation. If you do your own calculation, you need to take nulls into account since there is no aggregate function you can use.

=nz(fld1,0) + nz(fld2,0) + nz(fld3,0) + ....

Aggregate functions automatically handle sums. So when you sum a column using:
=Sum(fld1)

the sum will work correctly even if some of the rows for fld1 contain null values.

 

[Nitesh9999]

Thanks again Pat.

The row sum will be helpful, and I completely forgot about it .

And the Nz should help me adding the coloumns.

The next thing i would like to ask is how to add a simple parameter query on the PCTCode Field in the query "qyCombineCrossTabs".

I tried the usual "[]" but that returns an error.

I have heard that adding parrameters involving crosstabs can be tricky...

Thanks

Nitesh


EDIT

Got it

this is what you need to read if your having problems with parameters in Xtab queries...

http://support.microsoft.com/default.aspx?scid=kb;en-us;209778

 

[Pat Hartman]

For some reason crosstab queries require that parameters be specificly defined. This includes parameters used in any subqueries. So in whichever query actually references the parameters, bring up the parameter dialog and define them.

 

[Nitesh9999]

Ok latest Question.

Now that i have got Xtab parameters working, i was wondering if there is a parameter query that works in reverse. Where the value you enter in the paramater pop up means that paticular value dosnt show?

EG

Table
Field 1
bob
mark
sam
phil

Query
Field Field 1
Table Table 1
Criteria "????"

Now when i run this query the regular parameter pop up box appears.
If i enter "mark" in this pop up the query should return:

field 1
bob
sam
phil

Is this possible?


Thanks
Nitesh

 

[Pat Hartman]

You can't vary this on the fly since it affects the structure of the query so if you want to support both includsion and exclusion, you'll need to do it with separate queries.

Where SomeField = "somevalue"; --- inclusion
Where SomeField <> "somevalue"; --- exclusion

Becareful with compound conditions that use NOT logic, you will usually need to combine them with the AND operator rather than the OR opertator to get the results you expect.

Where SomeField = "somevalue" Or SomeField = "someothervalue";
Where SomeField <> "somevalue" AND SomeField <> "someothervalue";

 

[Nitesh9999]

Thanks pat that is brilliant!!