Selecting Records in a query

[Ed Hawley]

I need help! I have many records in my database but in my query, I only
want to see the last 3 or the last 6 or the last 9 or ......... My records
all relate to monthly records so if I ask for the last 3 records, I am
asking for the last 3 months of records. I want to set the query to select
the last 3 or 6 or 9 or whatever without my intervention. Does this seem
like a possilbility? I have an autonumber in my records so that I will have
a unique number assigned to each record.

Thank you for the help
Ed

 

[Guest]

Define "last". Does that mean the most recently entered? If so, then your
table needs to have a field of type DATE that records the date/time when each
record was entered. Further, if the user wants the "last 3", does that mean
the latest 3 records, or perhaps however many records have been entered in
the last 3 months?

 

[Van T. Dinh]

SELECT *
FROM [YourTable]
WHERE [RecordDate] BETWEEN
DateAdd("m", [Enter Required Number of Months], Now())
AND Now()

 

[Ed Hawley]

Ted

Thanks for the response. What I am doing is looking at the most recent data
for the last month, the last 3 months, and the last 6 months. I have an
assigned period that is based on an autonumber field that identifies each
period. There is one period per month. What I would like to do is include
the data from the last month, the data from the last 3 months, and the data
from the last 6 months in three separate reports. I could use the date field
but then I have to enter data each time I run the reports. I would like to
run the reports without having to input any additional information. All
reports will be based ont he 1, 3, and 6 month intervals. The method that I
am using now requires me to input which period is the last (most recent)
period then the query uses this information to include data. For example, if
the last period was 17, then the query would use [Last Period], [Last
Period] -1, [Last Period] - 2, to get the last 3 periods and so on. I would
like to avoid having to enter the last period number and have the query
automatically use the last (most recent) period.

Thanks!
Ed

 

[Ed Hawley]

SELECT *
FROM [YourTable]
WHERE [RecordDate] BETWEEN
DateAdd("m", [Enter Required Number of Months], Now())
AND Now()

--
HTH
Van T. Dinh
MVP (Access)

I need help! I have many records in my database but in my query, I only
want to see the last 3 or the last 6 or the last 9 or ......... My records
all relate to monthly records so if I ask for the last 3 records, I am
asking for the last 3 months of records. I want to set the query to select
the last 3 or 6 or 9 or whatever without my intervention. Does this seem
like a possilbility? I have an autonumber in my records so that I will
have a unique number assigned to each record.

Thank you for the help
Ed

 

[Ed Hawley]

Van,

Thanks for the response. I believe the date method would work but I am
trying to avoid having to enter data each time I want to run the report. See
the response to the other person that responded, I have explained my needs
more clearly in it.

Thanks for your input!
Ed

SELECT *
FROM [YourTable]
WHERE [RecordDate] BETWEEN
DateAdd("m", [Enter Required Number of Months], Now())
AND Now()

--
HTH
Van T. Dinh
MVP (Access)

I need help! I have many records in my database but in my query, I only
want to see the last 3 or the last 6 or the last 9 or ......... My records
all relate to monthly records so if I ask for the last 3 records, I am
asking for the last 3 months of records. I want to set the query to select
the last 3 or 6 or 9 or whatever without my intervention. Does this seem
like a possilbility? I have an autonumber in my records so that I will
have a unique number assigned to each record.

Thank you for the help
Ed

 

[John Spencer]

So to calculate the last period, you need to know the maximum value in the
Period field. You can get that with the DMAX function of with a subquery.

DMAX("Period","YourTableName") should return the largest value in the Period
Field.

Field: [Period]
Criteria: Between DMax("Period","YourTableName")-2 AND
DMax("Period","YourTableName")

Adjust the range size by setting the -2 to Number of Periods desired minus
1. For 6 periods that is 6-1 = -5

Ted

Thanks for the response. What I am doing is looking at the most recent
data for the last month, the last 3 months, and the last 6 months. I have
an assigned period that is based on an autonumber field that identifies
each period. There is one period per month. What I would like to do is
include the data from the last month, the data from the last 3 months, and
the data from the last 6 months in three separate reports. I could use the
date field but then I have to enter data each time I run the reports. I
would like to run the reports without having to input any additional
information. All reports will be based ont he 1, 3, and 6 month intervals.
The method that I am using now requires me to input which period is the
last (most recent) period then the query uses this information to include
data. For example, if the last period was 17, then the query would use
[Last Period], [Last Period] -1, [Last Period] - 2, to get the last 3
periods and so on. I would like to avoid having to enter the last period
number and have the query automatically use the last (most recent) period.

Thanks!
Ed

Define "last". Does that mean the most recently entered? If so, then your
table needs to have a field of type DATE that records the date/time when
each
record was entered. Further, if the user wants the "last 3", does that
mean
the latest 3 records, or perhaps however many records have been entered
in
the last 3 months?

 

[Ed Hawley]

John

Thank you for the suggetion to use DMax. I was not able to get it to work
perfectly due to problems using the "Autonumber" field. I was able to use
the Criteria suggestion you made though to get the job done. I still have to
enter the "Period" I want but using a variation of your suggestion worked. I
created a new column named LPeriod = [Enter Period] I then used the Between
[Enter Period] AND [Enter Period]-2 (for 3 Months), with a minus 5 to get 6
months, and so on. I simply enter the last "Period" number and the rest is
history. I verified the numbers and they are all correct. I really
appreciated your suggestion.

Thanks Again!
Ed

So to calculate the last period, you need to know the maximum value in the
Period field. You can get that with the DMAX function of with a subquery.

DMAX("Period","YourTableName") should return the largest value in the
Period Field.

Field: [Period]
Criteria: Between DMax("Period","YourTableName")-2 AND
DMax("Period","YourTableName")

Adjust the range size by setting the -2 to Number of Periods desired minus
1. For 6 periods that is 6-1 = -5

Ted

Thanks for the response. What I am doing is looking at the most recent
data for the last month, the last 3 months, and the last 6 months. I have
an assigned period that is based on an autonumber field that identifies
each period. There is one period per month. What I would like to do is
include the data from the last month, the data from the last 3 months,
and the data from the last 6 months in three separate reports. I could
use the date field but then I have to enter data each time I run the
reports. I would like to run the reports without having to input any
additional information. All reports will be based ont he 1, 3, and 6
month intervals. The method that I am using now requires me to input
which period is the last (most recent) period then the query uses this
information to include data. For example, if the last period was 17, then
the query would use [Last Period], [Last Period] -1, [Last Period] - 2,
to get the last 3 periods and so on. I would like to avoid having to
enter the last period number and have the query automatically use the
last (most recent) period.

Thanks!
Ed

Define "last". Does that mean the most recently entered? If so, then
your
table needs to have a field of type DATE that records the date/time when
each
record was entered. Further, if the user wants the "last 3", does that
mean
the latest 3 records, or perhaps however many records have been entered
in
the last 3 months?