Solved

Field criteria based on other fields value.

Posted on 2011-09-09
5
445 Views
Last Modified: 2013-11-28
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!
0
Comment
Question by:WiserG
5 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 36515477
I think some pictures would help here.
I find it quite hard to hold all this iin my fading brain in one go.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 36531930
Try to attach a simple sample database with few test data and the expected output.
0
 

Author Comment

by:WiserG
ID: 36534861
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?

0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 36551092
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.
0
 

Author Comment

by:WiserG
ID: 36554408
Thanks Helen.

Looks as the queries was rebuilt so often from it's ODBC source, that a make-table for the selection did miracles to the report.

From 2 minutes to 13 seconds! Thanks!!!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now