There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
Solved
Field criteria based on other fields value.
Posted on 2011-09-09
Hi,
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!
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!
-
2
- +1
5 Comments
I think some pictures would help here.
I find it quite hard to hold all this iin my fading brain in one go.
I find it quite hard to hold all this iin my fading brain in one go.
Did a workaround to fix it. Instead of merging the crosstables, I made several reports and merged the reports into one report based on subreports.
I'm still struggling with the speed issue. I'm using the following queries:
-Main query (filter on customer number and year)
--subquery 1 - crosstable - weight data for article group 1 pr month
--subquery 2 - crosstable - weight data for article group 2 pr month
--subquery 3 - crosstable - cost data for article group 1,2,3 pr month
--subquery 4 - chart data
All queries runs in a few seconds, 2-6. But when running the unbound report with subreports for the 4 queries takes 2-4 minutes. Any ideas how to speed this up?
I'm still struggling with the speed issue. I'm using the following queries:
-Main query (filter on customer number and year)
--subquery 1 - crosstable - weight data for article group 1 pr month
--subquery 2 - crosstable - weight data for article group 2 pr month
--subquery 3 - crosstable - cost data for article group 1,2,3 pr month
--subquery 4 - chart data
All queries runs in a few seconds, 2-6. But when running the unbound report with subreports for the 4 queries takes 2-4 minutes. Any ideas how to speed this up?
Active 2 days ago
Sometimes you can get around this problem by using a make-table query to run the code. It can be run (depending on how often the data needs to be refreshed) from an AutoExec macro, or from a button on the main menu that opens the report, or a button on a form where the data is updated. Then use the make-table query as the report's record source.
Featured Post
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen.
Visualize your data! ... really see it
To use the code to create a calendar from a q…
Suggested Courses
Course of the Month4 days, 13 hours left to enroll
587 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.