Get the Minimum Value for a set of record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear All,

I have table having more than 1000 of rows for every date like

Date Amount
01/01/2001 5000
31/12/2005 7200

i want to get a single line answer of minimum amount for a date range
through a query
what i get now in return of the query is the all record for the specified
date.
I need to get the minimum amount, let say, for a month or for a year, a
single minimum amount.
If anyone can help please
 
1. Create a query using this table.

2. Depress the Total button on the toolbar.
Access adds a Total row to the design grid.

3. Drag the date field into the grid.
In the Total row, choose Where.
In the Criteria row under this field, enter the limiting dates, e.g.:
Between #6/1/2006# And #6/30/2006#

4. Drag the Amount into the grid.
In the Total row, choose Min

If you want to do it without a query, switch this query to SQL View (View
menu), and take a look at the WHERE clause. You can use DMin() to get this
value, with the 3rd argument like what you see. Example:
=DMin("Amount", "Table1", "[Date] Between #6/1/2006# And #6/30/2006#")

Hopefully you don't really have a field named Date. Access will
misunderstand that name and sometimes evaluate it based on today's date
instead of the contents of that field.
 
Thanks for the help
one thing more please
i have different table (60) with same format as stated before
now i have to make a single list of min for all tables for any specified date
i made the date range optional like
Between[Enter Start Date] And [Enter End Date]
so i can have data for any date range of the table
one thing is missing here, how could i add columns in the result that
mention the table name (if query can work on all table and export the result
in a single table) and the 2 columns for those dates on which the query runs
AND
how could i apply the sigle query to all tables and have the result exported
in a single table for all sixty tables.
This would be really helpfull

Allen Browne said:
1. Create a query using this table.

2. Depress the Total button on the toolbar.
Access adds a Total row to the design grid.

3. Drag the date field into the grid.
In the Total row, choose Where.
In the Criteria row under this field, enter the limiting dates, e.g.:
Between #6/1/2006# And #6/30/2006#

4. Drag the Amount into the grid.
In the Total row, choose Min

If you want to do it without a query, switch this query to SQL View (View
menu), and take a look at the WHERE clause. You can use DMin() to get this
value, with the 3rd argument like what you see. Example:
=DMin("Amount", "Table1", "[Date] Between #6/1/2006# And #6/30/2006#")

Hopefully you don't really have a field named Date. Access will
misunderstand that name and sometimes evaluate it based on today's date
instead of the contents of that field.

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

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

Salman said:
Dear All,

I have table having more than 1000 of rows for every date like

Date Amount
01/01/2001 5000
31/12/2005 7200

i want to get a single line answer of minimum amount for a date range
through a query
what i get now in return of the query is the all record for the specified
date.
I need to get the minimum amount, let say, for a month or for a year, a
single minimum amount.
If anyone can help please
Click to expand...
Click to expand...
 
You really need to create one combined table to do this. You can add an
extra field that indicates whatever is the difference between the 60 tables.
You can use an Append query to populate new combined table from each of your
60 tables.

It is possible to combine a few tables in a UNION query, but I doubt that
will be successful with 60 tables. See help on UNION.

In the query for one table, you can switch it to SQL View (View menu, in
query design), and change the WHERE clause to:
WHERE (([Enter Start Date] Is Null) OR ([Field1 >= [Enter Start Date]))
AND (([Enter End Date] Is Null) OR ([Field1 < [Enter Start Date]+1))

If you are interested in how to create a search form with lots of optional
criteria, see:
http://allenbrowne.com/ser-62.html
However, you will still need the combined table to be able to get this to
work.

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

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

Salman said:
Thanks for the help
one thing more please
i have different table (60) with same format as stated before
now i have to make a single list of min for all tables for any specified
date
i made the date range optional like
Between[Enter Start Date] And [Enter End Date]
so i can have data for any date range of the table
one thing is missing here, how could i add columns in the result that
mention the table name (if query can work on all table and export the
result
in a single table) and the 2 columns for those dates on which the query
runs
AND
how could i apply the sigle query to all tables and have the result
exported
in a single table for all sixty tables.
This would be really helpfull

Allen Browne said:
1. Create a query using this table.

2. Depress the Total button on the toolbar.
Access adds a Total row to the design grid.

3. Drag the date field into the grid.
In the Total row, choose Where.
In the Criteria row under this field, enter the limiting dates, e.g.:
Between #6/1/2006# And #6/30/2006#

4. Drag the Amount into the grid.
In the Total row, choose Min

If you want to do it without a query, switch this query to SQL View (View
menu), and take a look at the WHERE clause. You can use DMin() to get
this
value, with the 3rd argument like what you see. Example:
=DMin("Amount", "Table1", "[Date] Between #6/1/2006# And
#6/30/2006#")

Hopefully you don't really have a field named Date. Access will
misunderstand that name and sometimes evaluate it based on today's date
instead of the contents of that field.

Salman said:
Dear All,

I have table having more than 1000 of rows for every date like

Date Amount
01/01/2001 5000
31/12/2005 7200

i want to get a single line answer of minimum amount for a date range
through a query
what i get now in return of the query is the all record for the
specified
date.
I need to get the minimum amount, let say, for a month or for a year, a
single minimum amount.
If anyone can help please
Click to expand...
Click to expand...
Click to expand...
 
Thanks for providing a useful tool for search
I think i was not much clear to state my problem, let me explain one more
time please

wat is the exact situation is i have tables as below

Table 1 (IIBL) Table 2 (TLIBL)

Date Amount Date
Amount

01/01/2001 5000 15/01/2001
3000
31/12/2005 7200 01/11/2004
9800

I have to perform an action to get the answer in a single sheet for all 60
companies as below

Co. Name Min Amount From
To
which is the table name (Amount) Start Date End
Date
(Co. Name in Symbols)

IIBL xxxx
xx/xx/xxxx xx/xx/xxxxx
TLIBL xxxxx xx/xx/xxxx
xx/xx/xxxxx

I dont have company name in the table, the table name identify for which the
table is, infact i am linking excel files with and in excel file the company
name is only mention in the first row for refernce and as sheet name, in
access the top rows consider as column headings and co. symbol is shown as
column heading with no entry in the whole column in access table

Infact this exercise is a basic one needed for further calculations and have
to perform on weekly basis, so i have to find a smart way to do this with no
time.

If u need i can send u the sample file.


Allen Browne said:
You really need to create one combined table to do this. You can add an
extra field that indicates whatever is the difference between the 60 tables.
You can use an Append query to populate new combined table from each of your
60 tables.

It is possible to combine a few tables in a UNION query, but I doubt that
will be successful with 60 tables. See help on UNION.

In the query for one table, you can switch it to SQL View (View menu, in
query design), and change the WHERE clause to:
WHERE (([Enter Start Date] Is Null) OR ([Field1 >= [Enter Start Date]))
AND (([Enter End Date] Is Null) OR ([Field1 < [Enter Start Date]+1))

If you are interested in how to create a search form with lots of optional
criteria, see:
http://allenbrowne.com/ser-62.html
However, you will still need the combined table to be able to get this to
work.

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

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

Salman said:
Thanks for the help
one thing more please
i have different table (60) with same format as stated before
now i have to make a single list of min for all tables for any specified
date
i made the date range optional like
Between[Enter Start Date] And [Enter End Date]
so i can have data for any date range of the table
one thing is missing here, how could i add columns in the result that
mention the table name (if query can work on all table and export the
result
in a single table) and the 2 columns for those dates on which the query
runs
AND
how could i apply the sigle query to all tables and have the result
exported
in a single table for all sixty tables.
This would be really helpfull

Allen Browne said:
1. Create a query using this table.

2. Depress the Total button on the toolbar.
Access adds a Total row to the design grid.

3. Drag the date field into the grid.
In the Total row, choose Where.
In the Criteria row under this field, enter the limiting dates, e.g.:
Between #6/1/2006# And #6/30/2006#

4. Drag the Amount into the grid.
In the Total row, choose Min

If you want to do it without a query, switch this query to SQL View (View
menu), and take a look at the WHERE clause. You can use DMin() to get
this
value, with the 3rd argument like what you see. Example:
=DMin("Amount", "Table1", "[Date] Between #6/1/2006# And
#6/30/2006#")

Hopefully you don't really have a field named Date. Access will
misunderstand that name and sometimes evaluate it based on today's date
instead of the contents of that field.

Dear All,

I have table having more than 1000 of rows for every date like

Date Amount
01/01/2001 5000
31/12/2005 7200

i want to get a single line answer of minimum amount for a date range
through a query
what i get now in return of the query is the all record for the
specified
date.
I need to get the minimum amount, let say, for a month or for a year, a
single minimum amount.
If anyone can help please
Click to expand...
Click to expand...
Click to expand...
 
Ah, just 2 tables.

Try a UNION query like this, with a literal value for the company name:

SELECT 'IIBL' As Company,
[Table 1].[Date],
[Table 1].[Amount]
FROM [Table 1]
UNION ALL
SELECT 'TLIBL' AS Company,
[Table 2].[Date],
[Table 1].[Amount]
FROM [Table 2];

Save that query, and you can then use it as a source "table" for another
query.

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

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

Salman said:
Thanks for providing a useful tool for search
I think i was not much clear to state my problem, let me explain one more
time please

wat is the exact situation is i have tables as below

Table 1 (IIBL) Table 2 (TLIBL)

Date Amount Date
Amount

01/01/2001 5000 15/01/2001
3000
31/12/2005 7200 01/11/2004
9800

I have to perform an action to get the answer in a single sheet for all 60
companies as below

Co. Name Min Amount From
To
which is the table name (Amount) Start Date
End
Date
(Co. Name in Symbols)

IIBL xxxx
xx/xx/xxxx xx/xx/xxxxx
TLIBL xxxxx
xx/xx/xxxx
xx/xx/xxxxx

I dont have company name in the table, the table name identify for which
the
table is, infact i am linking excel files with and in excel file the
company
name is only mention in the first row for refernce and as sheet name, in
access the top rows consider as column headings and co. symbol is shown as
column heading with no entry in the whole column in access table

Infact this exercise is a basic one needed for further calculations and
have
to perform on weekly basis, so i have to find a smart way to do this with
no
time.

If u need i can send u the sample file.


Allen Browne said:
You really need to create one combined table to do this. You can add an
extra field that indicates whatever is the difference between the 60
tables.
You can use an Append query to populate new combined table from each of
your
60 tables.

It is possible to combine a few tables in a UNION query, but I doubt that
will be successful with 60 tables. See help on UNION.

In the query for one table, you can switch it to SQL View (View menu, in
query design), and change the WHERE clause to:
WHERE (([Enter Start Date] Is Null) OR ([Field1 >= [Enter Start
Date]))
AND (([Enter End Date] Is Null) OR ([Field1 < [Enter Start Date]+1))

If you are interested in how to create a search form with lots of
optional
criteria, see:
http://allenbrowne.com/ser-62.html
However, you will still need the combined table to be able to get this to
work.

Salman said:
Thanks for the help
one thing more please
i have different table (60) with same format as stated before
now i have to make a single list of min for all tables for any
specified
date
i made the date range optional like
Between[Enter Start Date] And [Enter End Date]
so i can have data for any date range of the table
one thing is missing here, how could i add columns in the result that
mention the table name (if query can work on all table and export the
result
in a single table) and the 2 columns for those dates on which the query
runs
AND
how could i apply the sigle query to all tables and have the result
exported
in a single table for all sixty tables.
This would be really helpfull

:

1. Create a query using this table.

2. Depress the Total button on the toolbar.
Access adds a Total row to the design grid.

3. Drag the date field into the grid.
In the Total row, choose Where.
In the Criteria row under this field, enter the limiting dates, e.g.:
Between #6/1/2006# And #6/30/2006#

4. Drag the Amount into the grid.
In the Total row, choose Min

If you want to do it without a query, switch this query to SQL View
(View
menu), and take a look at the WHERE clause. You can use DMin() to get
this
value, with the 3rd argument like what you see. Example:
=DMin("Amount", "Table1", "[Date] Between #6/1/2006# And
#6/30/2006#")

Hopefully you don't really have a field named Date. Access will
misunderstand that name and sometimes evaluate it based on today's
date
instead of the contents of that field.

Dear All,

I have table having more than 1000 of rows for every date like

Date Amount
01/01/2001 5000
31/12/2005 7200

i want to get a single line answer of minimum amount for a date
range
through a query
what i get now in return of the query is the all record for the
specified
date.
I need to get the minimum amount, let say, for a month or for a
year, a
single minimum amount.
If anyone can help please
Click to expand...
Click to expand...
Click to expand...
 
Sorry for the late reply, infact i was away,

Thanks for your help its work, although, 2 union queries work for this bcz
union query doesnot support much tables and result for the min from 2
different queries have to merge manually but its really helpfull.
Thanks alot.

Allen Browne said:
Ah, just 2 tables.

Try a UNION query like this, with a literal value for the company name:

SELECT 'IIBL' As Company,
[Table 1].[Date],
[Table 1].[Amount]
FROM [Table 1]
UNION ALL
SELECT 'TLIBL' AS Company,
[Table 2].[Date],
[Table 1].[Amount]
FROM [Table 2];

Save that query, and you can then use it as a source "table" for another
query.

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

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

Salman said:
Thanks for providing a useful tool for search
I think i was not much clear to state my problem, let me explain one more
time please

wat is the exact situation is i have tables as below

Table 1 (IIBL) Table 2 (TLIBL)

Date Amount Date
Amount

01/01/2001 5000 15/01/2001
3000
31/12/2005 7200 01/11/2004
9800

I have to perform an action to get the answer in a single sheet for all 60
companies as below

Co. Name Min Amount From
To
which is the table name (Amount) Start Date
End
Date
(Co. Name in Symbols)

IIBL xxxx
xx/xx/xxxx xx/xx/xxxxx
TLIBL xxxxx
xx/xx/xxxx
xx/xx/xxxxx

I dont have company name in the table, the table name identify for which
the
table is, infact i am linking excel files with and in excel file the
company
name is only mention in the first row for refernce and as sheet name, in
access the top rows consider as column headings and co. symbol is shown as
column heading with no entry in the whole column in access table

Infact this exercise is a basic one needed for further calculations and
have
to perform on weekly basis, so i have to find a smart way to do this with
no
time.

If u need i can send u the sample file.


Allen Browne said:
You really need to create one combined table to do this. You can add an
extra field that indicates whatever is the difference between the 60
tables.
You can use an Append query to populate new combined table from each of
your
60 tables.

It is possible to combine a few tables in a UNION query, but I doubt that
will be successful with 60 tables. See help on UNION.

In the query for one table, you can switch it to SQL View (View menu, in
query design), and change the WHERE clause to:
WHERE (([Enter Start Date] Is Null) OR ([Field1 >= [Enter Start
Date]))
AND (([Enter End Date] Is Null) OR ([Field1 < [Enter Start Date]+1))

If you are interested in how to create a search form with lots of
optional
criteria, see:
http://allenbrowne.com/ser-62.html
However, you will still need the combined table to be able to get this to
work.

Thanks for the help
one thing more please
i have different table (60) with same format as stated before
now i have to make a single list of min for all tables for any
specified
date
i made the date range optional like
Between[Enter Start Date] And [Enter End Date]
so i can have data for any date range of the table
one thing is missing here, how could i add columns in the result that
mention the table name (if query can work on all table and export the
result
in a single table) and the 2 columns for those dates on which the query
runs
AND
how could i apply the sigle query to all tables and have the result
exported
in a single table for all sixty tables.
This would be really helpfull

:

1. Create a query using this table.

2. Depress the Total button on the toolbar.
Access adds a Total row to the design grid.

3. Drag the date field into the grid.
In the Total row, choose Where.
In the Criteria row under this field, enter the limiting dates, e.g.:
Between #6/1/2006# And #6/30/2006#

4. Drag the Amount into the grid.
In the Total row, choose Min

If you want to do it without a query, switch this query to SQL View
(View
menu), and take a look at the WHERE clause. You can use DMin() to get
this
value, with the 3rd argument like what you see. Example:
=DMin("Amount", "Table1", "[Date] Between #6/1/2006# And
#6/30/2006#")

Hopefully you don't really have a field named Date. Access will
misunderstand that name and sometimes evaluate it based on today's
date
instead of the contents of that field.

Dear All,

I have table having more than 1000 of rows for every date like

Date Amount
01/01/2001 5000
31/12/2005 7200

i want to get a single line answer of minimum amount for a date
range
through a query
what i get now in return of the query is the all record for the
specified
date.
I need to get the minimum amount, let say, for a month or for a
year, a
single minimum amount.
If anyone can help please
Click to expand...
Click to expand...
Click to expand...
 
Back
Top