Report from Query - Select Max value

[Gil Lopes via AccessMonster.com]

Hi!

I' m trying to build a report based on a select query.

One of the querys fields states a price for a date series.
I would like to retreive to my report the higher value for the price column.

I tryed a textbox in my report that says:

=Max([rendabertos]![Cotação]) ----> rendabertos is my query and cotação is
the field in that query.

Needless to say that it didn' t work : )
I wanted the same for the lower value, and tryed to use the same expression,
but with "min".

Any help?

Thanks!!!!

 

[Allen Browne]

Use a subquery.

This example assumes the query contains the Product table, and that your
rendabertos table has a foreign key field that refers to the primary key of
the Product table. You have a query that contains just the Product table.
Then type something like this into the Field row of the query:

MaxContagco: (SELECT Max(Cotagco) FROM rendabertos WHERE
rendabertos.ProductID = Product.ProductID)

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[Gil Lopes via AccessMonster.com]

Wow...
Done!!!!

Thanks a lot Allen.
Let' s see how long I 'll take to return here for help... : )

Many, many thanks!!!

Use a subquery.

This example assumes the query contains the Product table, and that your
rendabertos table has a foreign key field that refers to the primary key of
the Product table. You have a query that contains just the Product table.
Then type something like this into the Field row of the query:

MaxContagco: (SELECT Max(Cotagco) FROM rendabertos WHERE
rendabertos.ProductID = Product.ProductID)

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Hi!

[quoted text clipped - 18 lines]

Thanks!!!!

 

[Gil Lopes via AccessMonster.com]

Hummm...

Circular reference...
How should I overcome this?

Again, many thanks!

Wow...
Done!!!!

Thanks a lot Allen.
Let' s see how long I 'll take to return here for help... : )

Many, many thanks!!!

Use a subquery.

[quoted text clipped - 16 lines]

 

[Allen Browne]

You are getting a 'Circular reference' error message on the query that was
created by the Unmatched Query wizard?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hummm...

Circular reference...
How should I overcome this?

Again, many thanks!

Wow...
Done!!!!

Thanks a lot Allen.
Let' s see how long I 'll take to return here for help... : )

Many, many thanks!!!

Use a subquery.

[quoted text clipped - 16 lines]

Thanks!!!!

 

[Gil Lopes via AccessMonster.com]

Well Allen...

I can run the query as long as it's not saved.
When I save it, it says it has a circular reference.

But I overcame that by creating a second query that "queries" the first one.
So, problem solved.

Anyway, I still can't make my report to show those values.
In my report I inserted a textbox with the following string:

=([maxcot].[maxcot]) ---> my second query

If the queries are saved, why shouldn' t I be able to revert them to my
report???
More, I can't refer to any table or saved query in my report. Shouldn' t I be
able to?

Sorry for the noob questions : (

Thanks!!!!!

You are getting a 'Circular reference' error message on the query that was
created by the Unmatched Query wizard?

Hummm...

[quoted text clipped - 16 lines]

 

[Allen Browne]

I'm not sure I understand what's going on here.

It does sound like Access is confused about the name of something or a data
type.

The naming problems could be caused by Name AutoCorrect. Uncheck those boxes
under:
Tools | Options | General
Then compact the database:
Tools | Database Utilities | Compact
More info on this issue in this article:
Failures caused by Name Auto-Correct
at:
http://allenbrowne.com/bug-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Well Allen...

I can run the query as long as it's not saved.
When I save it, it says it has a circular reference.

But I overcame that by creating a second query that "queries" the first
one.
So, problem solved.

Anyway, I still can't make my report to show those values.
In my report I inserted a textbox with the following string:

=([maxcot].[maxcot]) ---> my second query

If the queries are saved, why shouldn' t I be able to revert them to my
report???
More, I can't refer to any table or saved query in my report. Shouldn' t I
be
able to?

Sorry for the noob questions : (

Thanks!!!!!

You are getting a 'Circular reference' error message on the query that was
created by the Unmatched Query wizard?

Hummm...

[quoted text clipped - 16 lines]

Thanks!!!!

 

[Gil Lopes via AccessMonster.com]

Allen:

Let me start by thanking all the help you're providing me.

I done what you suggested, but didn' t seem to help...

My problem is that I can' t build a report that collects fields from tables
and queries, only can get them from my form.

I have this form where I provide my query with start and end dates, and a
product ID.
I can collect to my report the start and end date, as weel as the product ID
from the form, with a sentence like: =forms!rendibilidade!datacom

The problem is that I can' t do the same with fields in tables or queries.
For instance: I managed to get the max(cotação) from a second query. I cannot
make my report to show it anyway.

Please tell me if I shouldn' t be able to add a textbox to my report with
this kind of string:
=max[upsabertos].[cotação]

Many thanks!

I'm not sure I understand what's going on here.

It does sound like Access is confused about the name of something or a data
type.

The naming problems could be caused by Name AutoCorrect. Uncheck those boxes
under:
Tools | Options | General
Then compact the database:
Tools | Database Utilities | Compact
More info on this issue in this article:
Failures caused by Name Auto-Correct
at:
http://allenbrowne.com/bug-03.html

Well Allen...

[quoted text clipped - 28 lines]

 

[Allen Browne]

Your report has a RecordSource property.
If that contains the name of a table or query, then you can refer to fields
from that table/query in your report.

You cannot refer to fields in other tables, unless you use an expression
involving DLookup() or DMax().

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

Let me start by thanking all the help you're providing me.

I done what you suggested, but didn' t seem to help...

My problem is that I can' t build a report that collects fields from
tables
and queries, only can get them from my form.

I have this form where I provide my query with start and end dates, and a
product ID.
I can collect to my report the start and end date, as weel as the product
ID
from the form, with a sentence like: =forms!rendibilidade!datacom

The problem is that I can' t do the same with fields in tables or queries.
For instance: I managed to get the max(cotagco) from a second query. I
cannot
make my report to show it anyway.

Please tell me if I shouldn' t be able to add a textbox to my report with
this kind of string:
=max[upsabertos].[cotagco]

Many thanks!

I'm not sure I understand what's going on here.

It does sound like Access is confused about the name of something or a
data
type.

The naming problems could be caused by Name AutoCorrect. Uncheck those
boxes
under:
Tools | Options | General
Then compact the database:
Tools | Database Utilities | Compact
More info on this issue in this article:
Failures caused by Name Auto-Correct
at:
http://allenbrowne.com/bug-03.html

Well Allen...

[quoted text clipped - 28 lines]

Thanks!!!!

 

[Gil Lopes via AccessMonster.com]

Thanks, thanks, thanks, thanks, thanks...

That did it...
God...sorry for asking these noob questions...
I' m learning from scratch on my own...well, with your help!!!

Many, many thanks, Allen!!!!

Your report has a RecordSource property.
If that contains the name of a table or query, then you can refer to fields
from that table/query in your report.

You cannot refer to fields in other tables, unless you use an expression
involving DLookup() or DMax().

Allen:

[quoted text clipped - 45 lines]