optimized way to make a report? (1 Viewer)

If I have a report or form based on 3 queries:

1st query pulls data from table or multiple tables and filter by criteria (for example date interval)
2nd query is based on 1st - it is a total query that calculates sum
3rd query is based on 2nd query - it converts numbers to words by using a module VBA code.

The problem:

When I open report or form with all 3 queries in i what access does behind the hood?

1.

all queries are executed once.

2.

1st query populates detail section of the report
2nd query populates total sum field but also runs query 1?
3rd query populates numbers to words field but also runs queries 1 and 2?

Well I can't see how and why u would use 3 queries at the same time on the same report. Normally u should have just 1 query to populate the report. The total sum text box should only contain the sum function (for ex. "=Sum([SubTotal])"). The text box that displays the number in words, should only contain the function name (for ex. "=fNumbersToWords([Total])").

HTH

Premy said:

Well I can't see how and why u would use 3 queries at the same time on the same report. Normally u should have just 1 query to populate the report. The total sum text box should only contain the sum function (for ex. "=Sum([SubTotal])"). The text box that displays the number in words, should only contain the function name (for ex. "=fNumbersToWords([Total])").

HTH

Click to expand...

Yes I tried to use unbound textboxes, and I ran into these problems:

1. Unbound textbox takes 1 to 1.5 seconds delay to load on any report or form.

2. If I have unbound texbox 1 that creates total sum and unbound textbox2 that calculates numbers to words from 1st textbox I have a problem:

textbox 2 sometimes fails because textbox 1 encounters delay (for example many records etc.)

It seems that unbound text box fires formula and does not wait for "source" to provide data. If the source control does not have ready data then it fails or in the case where I need to convert number to words gives me debug error because integer is empty sometimes etc.

3. I have some reports designed with summary only, I need calculated total but do not have any records visible. So I do calculations in second totals query. Because totals query does not always like expressions (don't know why this is but if I need to have data in currency format I have a formula like


I entered:
total: Nz([sumof_myfieldname];0)

but no zero if my value is null.

your code genreates this sql:
SELECT Sum(Nz([SumOf_myfieldname],0)) AS total
FROM my_query_ name_group;

my working sql but shown as expression:
SELECT CCur(Nz(Sum(my_query_ name_group.SumOf_myfieldname),0)) AS total
FROM my_query_ name_group;

I would like to avoid using expression

) I have third query to convert number to words.

Any thoughts are welcome

smile said:

If I have a report or form based on 3 queries:

1st query pulls data from table or multiple tables and filter by criteria (for example date interval)
2nd query is based on 1st - it is a total query that calculates sum
3rd query is based on 2nd query - it converts numbers to words by using a module VBA code.

The problem:

When I open report or form with all 3 queries in i what access does behind the hood?

1.

all queries are executed once.

2.

1st query populates detail section of the report
2nd query populates total sum field but also runs query 1?
3rd query populates numbers to words field but also runs queries 1 and 2?

Click to expand...

What problem are you having?

I'm still struggling to know exactly what you require.
See Rpt_query1, am I on the right track?
As for group totals see qry_Group_total.

The problem with my report is that it does totalling, if you have converted your currency to text strings that will not be possible, I think then all totalling must be done prior to the report. Maybe you are looking at sub reports.

Brian

Brianwarnock said:

I'm still struggling to know exactly what you require.
See Rpt_query1, am I on the right track?
As for group totals see qry_Group_total.

The problem with my report is that it does totalling, if you have converted your currency to text strings that will not be possible, I think then all totalling must be done prior to the report. Maybe you are looking at sub reports.

Brian

Click to expand...

Hmm, seems you have updated my sample db from another post. I see you got 50% where I'm now, you filter data by using query "group_code_bjw" then sum by code "total_code".

In my db for now I work on another table that I must also filter data by criteria and calculate grand total no need to divide by code.

I did it with 3 queries and I'm looking for more optimized way to do the same. As I said I tried to make only 1 query to filter criteria and calculate total on report by using unbound text box. But that is slow.

Also how to tell a textbox that it gets data from a control that is not bound - I mean to wait for data.

Rich said:

What problem are you having?

Click to expand...

My problem:

How to make calculations on report itself with unbound text boxes if that is the correct way to have 1 instead of 3 queries.

Ran into these problems:

read post 3.

you filter data by using query "group_code_bjw" then sum by code "total_code".

Click to expand...

No, I Total in the Report, I have flagged "total_code" as not needed, but I see that I'm now out of date as to where you are.

Brian

Brianwarnock said:

No, I Total in the Report, I have flagged "total_code" as not needed, but I see that I'm now out of date as to where you are.

Brian

Click to expand...

What about my problems with unbound text boxes on post 3, any ideas:

1. Unbound textbox takes 1 to 1.5 seconds delay to load on any report or form.

2. If I have unbound texbox 1 that creates total sum and unbound textbox2 that calculates numbers to words from 1st textbox I have a problem:

textbox 2 sometimes fails because textbox 1 encounters delay (for example many records etc.)

It seems that unbound text box fires formula and does not wait for "source" to provide data. If the source control does not have ready data then it fails or in the case where I need to convert number to words gives me debug error because integer is empty sometimes etc.

U may try to set the expression to call the function (numbers to words) only after the report has loaded, for example in the Detail_Format Event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.NumberInWords = fNumbersToWords(Me.Total)
End Sub

HTH

Premy said:

U may try to set the expression to call the function (numbers to words) only after the report has loaded, for example in the Detail_Format Event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.NumberInWords = fNumbersToWords(Me.Total)
End Sub

HTH

Click to expand...

Thanks I'll try that.

I don't understand the delay anyway. Why working with unbound controls in my case text box I get delay until code (I mean formula) is executed? Is this normal? Any methods to reduce the delay?

BTW the delay can be seen if report opens in "report view" mode if I open it in "print preview" it seems to not have delay. Both open is same amount of time so anybody know?

So based on everyone replies how unbound text boxes work and process data or should I say lack of replies I have made a decision that it’s better to have more queries since:

Access creates a derived table (record set) from the first query, the 2nd query uses the derived table to create another derived table that the final query uses to produce the end result. So the answer is each query is only executed once.

Besides having some sort of standard (naming convention, design convention) or how you create things like queries, how they pull data, etc. is better than none.

And it also seems that reports run faster (no delay) with 3 queries than if unbound texbox is used.

I’m still open to discussion and if somebody knows additional information I would be thankful.

smile said:

So based on everyone replies how unbound text boxes work and process data or should I say lack of replies I have made a decision that it’s better to have more queries since:

.

Click to expand...

You have been getting replies for almost 2 months now across 2 threads but seemed intent on ignoring our responses and questions as you have wanted to do everything in 1 query, now at last you are on the right track.

Brian

I would partially agree with you, but as I said I'm no access expert and don't pretend to be one. I just looking for help if possible to use 1 query and no 3 for every report I make. If that makes me ask stupid looking questions I don’t care as long as I learn something.

Unfortunately as you noted been getting replies for 2 months but you forgot that they were not to the point on this particular thread.

The other thread http://www.access-programmers.co.uk/forums/showthread.php?t=159513
Is another story and I’m helpful that I learned how to avoid creating a bunch of queries if I don’t need to change data on the fly. Thanks for you help.

Now about current thread:

I provided information on what queries I have and what they do.
I provided information on my findings working with unbound textboxes and problems with them.

I got no response on how access process queries, no response on how unbound textbox get data and how many levels deep they can be used (I mean textbox1[pulls data form table]- textbox2[pulls form box1] - textbox3[pulls form box1] as it seems there is a problem if textbox pulls data from unbound controls)

Sorry if I sound ignorant but that’s not what I wanted to say.
I just did not get a reply on my previous thread answering one question:

Is resource (CPU usage, memory etc.) wise it’s better to make 3 queries in my scenario, or make
Unbound textbox on report and reduce the number of queries (making more easy management of the database for the user/designer)

If what I want is wrong and can't or should not be done then you should say so.
As you see I tend to ask questions first then design later as opposite to some people making first and whining later that something does not work.

Again thanks to everyone who shed any light on this perhaps nonstandard subject.