Query field based on outcome other query

[paultje_bos]

Good morning,

I am looking to solve an issue I have, it's a bit difficult to explain, but I will try to be as clear as possible. To create a report I have multiple queries to get me the right results. One of these queries creates "Field names" I use in the report. Example, based on the queries result I allocate the label "IN", "OUT", "DOUBLE", "NONE". Based on the label a price is allocated, so in respective 10, 10, 20, 0.
I than want to display the results (accumulated) per label in a report. So per Label the total number and the associated total Price, looking something like this:

IN #	IN €	OUT #	OUT €	DOUBLE #	DOUBLE €	NONE #	NONE €
3	30	0	0	2	40	1	0
0	0	10	100	5	100	0	0
1	10	1	10	1	10	1	0
9	90	5	50	3	60	5	0

I managed to get this via 2 cross-tab queries, 1 is getting the number per label and the other is getting the price. Next I have a query that gets the results of the 2 cross-tab queries in one overview which I use then in the report. Just to sum up, the order of queries:

Query 1: Allocates label to the results of other queries
Cross-tab 1: Counts the number of occurrences per label (so no label, no field name)


Cross-tab 2: Calculates the total per label (so no label, no calculation)
Query 2: Looks up label and associated number and price.

Now the problem I have, if one label (for instance "DOUBLE") is not allocated, this gives me an error in query 2 that gets the results of the 2 cross-tab queries. This because the cross-tab query creates the field names (read labels) for query 2.

I hope I clarified the problem enough so you can help me. If you need more info, please don't hesitate to ask.

[CJ_London]

I think I understand what you are asking - if I have then the solution is in your first crosstab is to set the column headings to appear whether they have values or not (if not they will return nulls so you second query may need to handle that)

to set the column headings, in the query properties enter them in the column headings separated by a comma. In SQL it will look something like

TRANSFORM ...
SELECT ...
FROM ....
GROUP BY ...
PIVOT columnfieldname In ("Double","None");

[paultje_bos]

Ajax, many thanks. I used your solution and it worked perfectly! Thanks!