Field criteria based on other fields value.
Posted on 2011-09-09
I'm struggling with the following problem (hope it makes sense):
I'm making a report that shows gross weight sum for a customer. This is listed in columns per month (Jan-Des) and ArticleNo in rows. I've made a crosstable query that extract the information I need and made a report based on that query.
My problem occurs when I'm trying to make totals in the bottom. The totals for the gross weight is no problem using the Sum function, but I'm also supposed to have a sum of the charged amount of money per month.
I didn't find a way to make a function to ask another query than the record source query, and tried the following:
One crosstable query which extracts sales with weight sum per article number.
Another crosstable query which extracts sales with the charged amount per article number
A select query that asks for customer no and year, and with reference to the two crosstables with fields for all months from both.
Works fine, except that the report is really slow. Running one crosstable takes 20 seconds. Running the select query takes 3 minutes.
But after congratulating myself with the success, I come into another problem. For the first crosstable I want to restrict the query to show ArticleGroup "3" only. No problem to make that criteria in the first crosstable query. But when I run the select query, I get the sums for all article groups from the month fields from the first crosstable (and of course the second crosstable which should show all the article groups).
Any suggestions what I can do to make this work? Thanks in advance!