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.