optimized way to make a report?

[smile]

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?


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

 

[Duane Hookom]

I am totally confused regarding your report and controls. I assume the record
source of your report is actually just the 3rd query. They all run together.

What are you doing with unbound text boxes? Aren't they bound to a field in
the record source or an expression based on a field? What kind of control
sources are you using?

 

[smile]

I read that:

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?

My controls are textboxes.

My report uses bound textboxes from all 3 queries, why else would I have 3?
1st query populates detail section.
2nd and 3rd populates footer.

I was just looking for way to remove 2nd and 3rd queries by using unbound
textbxes to do their job.
But as I wrote got mixed results, got delay, and errors asociated with it.

 

[Duane Hookom]

A report has only one record source. If this is based on the 3rd query then
all previous queries are used/run in order to return the 3rd recordset.

A report can have subreports that are each based on there own recordset.

You can use header and footer sections of report to display aggregated
values.

How/why are you using unbound textboxes? Are you using code or DLookup() or
other?

 

[smile via AccessMonster.com]

Sorry I'm new to access

As I said I have 3 queires:

1st filters data by criteria (date) [takes data form table]
2nd query is an agregiate type (sums the field with money amount) [based on
1st query]
3rd query converts numbers to words via module function. [based on 2nd query]

I created the report in design view by adding all 3 queires with their fields.
So when you ask me on what query it's based I don't know.

A report has only one record source. If this is based on the 3rd query then
all previous queries are used/run in order to return the 3rd recordset.

A report can have subreports that are each based on there own recordset.

You can use header and footer sections of report to display aggregated
values.

How/why are you using unbound textboxes? Are you using code or DLookup() or
other?

I read that:

[quoted text clipped - 12 lines]

textbxes to do their job.
But as I wrote got mixed results, got delay, and errors asociated with it.

 

[smile via AccessMonster.com]

Oh and I forgot, about unbound textbox.

I don't really want to use unbound textboxes, but since with my current
desigh my report have to use 3 queires I wanted to reduce them to 2 or less
if possible.

Perhaps this is stupid to bengin with I don't know. I have bunch of reports
and every one of them has 3 queries (totals to 7 reports and 21quries to make
them work).

Wanted to tidy things up, but seems it creates more problems than it solves,
no?

 

[Duane Hookom]

A report has one record source. This is generally a query or SQL statement or
table. Open your report in design view, find the Record Source property, view
its SQL view, copy it to the clipboard, and paste it in a reply.

Are you using Access 2007? Do you understand that if you want to get
aggregate totals, you can use group sections?

What do you mean by "unbound textboxes"? Do these have control source
properties you can share?

 

[smile via AccessMonster.com]

OK here is the SQL:

SELECT qry_prekiu_db_kknyga_kio_startdate_enddate_sum.
kio_startdate_enddate_sum, qry_prekiu_db_kknyga_kpo_startdate_enddate_sum.
kpo_startdate_enddate_sum, qry_prekiu_db_kknyga_kpo_startdate_enddate_sum.
kpo_startdate_enddate_count, qry_prekiu_db_kknyga_kio_startdate_enddate_sum.
kio_startdate_enddate_count, tbl_parametrai.pavadinimas, tbl_parametrai.
Adresas, tbl_parametrai.kodas, qry_prekiu_db_kknyga_kio_startdate_sum.
kio_startdate_sum, qry_prekiu_db_kknyga_kpo_startdate_sum.kpo_startdate_sum
FROM qry_prekiu_db_kknyga_kio_startdate_enddate_sum,
qry_prekiu_db_kknyga_kpo_startdate_enddate_sum, tbl_parametrai,
qry_prekiu_db_kknyga_kio_startdate_sum,
qry_prekiu_db_kknyga_kpo_startdate_sum;

A report has one record source. This is generally a query or SQL statement or
table. Open your report in design view, find the Record Source property, view
its SQL view, copy it to the clipboard, and paste it in a reply.

Are you using Access 2007? Do you understand that if you want to get
aggregate totals, you can use group sections?

What do you mean? I filter by criteria in my fist query, then create second
query based on first and using make my query "totals query"I select sum for
the field with money amount.

What do you mean by "unbound textboxes"? Do these have control source
properties you can share?

Oh and I forgot, about unbound textbox.

[quoted text clipped - 8 lines]

Wanted to tidy things up, but seems it creates more problems than it solves,
no?

 

[smile via AccessMonster.com]

If my 3rd query is a totals query in the field that convert number to words
should I select "group by" or "expression"?

 

[Duane Hookom]

Do each of the sources (queries/tables) in your return only a single record
other than one that returns one or more records?

I'm lost since I have no context for what you are attempting to accomplish.

 

[Duane Hookom]

WAG Group By...

 

[smile via AccessMonster.com]

Do each of the sources (queries/tables) in your return only a single record
other than one that returns one or more records?

I'm lost since I have no context for what you are attempting to accomplish.

A report has one record source. This is generally a query or SQL statement or
table. Open your report in design view, find the Record Source property, view

[quoted text clipped - 18 lines]

1st query return many "detail" records that go into detail section of the
report.
2nd and 3rd only 1 record each.

 

[Duane Hookom]

You don't generally need separate queries for 2 & 3 since:
1) you can calculate aggregates/sums to display in the report header and
footer sections.
2) you can change the number to words in your control source in the report.

--
Duane Hookom
Microsoft Access MVP

Do each of the sources (queries/tables) in your return only a single record
other than one that returns one or more records?

I'm lost since I have no context for what you are attempting to accomplish.

A report has one record source. This is generally a query or SQL statement or
table. Open your report in design view, find the Record Source property, view

[quoted text clipped - 18 lines]

Wanted to tidy things up, but seems it creates more problems than it solves,
no?

1st query return many "detail" records that go into detail section of the
report.
2nd and 3rd only 1 record each.

 

[smile via AccessMonster.com]

So is posible to get the same as this SQL in my second qurey in a report with
unbound textbox?:
SELECT qry_prekiu_db_bio.BIO, Sum(qry_prekiu_db_bio.Savikaina) AS
SumOfSavikaina
FROM qry_prekiu_db_bio
GROUP BY qry_prekiu_db_bio.BIO;

You don't generally need separate queries for 2 & 3 since:
1) you can calculate aggregates/sums to display in the report header and
footer sections.
2) you can change the number to words in your control source in the report.
[quoted text clipped - 10 lines]

report.
2nd and 3rd only 1 record each.

 

[Duane Hookom]

An unbound text box doesn't have anything in its control source property. If
you want a sum of a field in your reports record source, you can add a text
box to the report header or footer section with a control source like:
=Sum([Your numeric field or expression])
--
Duane Hookom
Microsoft Access MVP

So is posible to get the same as this SQL in my second qurey in a report with
unbound textbox?:
SELECT qry_prekiu_db_bio.BIO, Sum(qry_prekiu_db_bio.Savikaina) AS
SumOfSavikaina
FROM qry_prekiu_db_bio
GROUP BY qry_prekiu_db_bio.BIO;

You don't generally need separate queries for 2 & 3 since:
1) you can calculate aggregates/sums to display in the report header and
footer sections.
2) you can change the number to words in your control source in the report.

Do each of the sources (queries/tables) in your return only a single record
other than one that returns one or more records?

[quoted text clipped - 10 lines]

report.
2nd and 3rd only 1 record each.