Using expression in Query to limit table date used.

  • Thread starter Thread starter Ed Hawley
  • Start date Start date
E

Ed Hawley

Could use some ideas!

I am trying to identify an expression that I can use in a query to limit
lines used from a data table. For example, I have data collected over
several months. I want to select the data from the last 3 months, the last 6
months, etc. I would appreciate any ideas that I can try. The key I am using
is an autonumber field. Each line of monthly data has a unique number
assigned if that helps.

I will appreciate any suggestions that might help me solve this problem.

Thanks!
Ed
 
Ed said:
I am trying to identify an expression that I can use in a query to limit
lines used from a data table. For example, I have data collected over
several months. I want to select the data from the last 3 months, the last 6
months, etc. I would appreciate any ideas that I can try. The key I am using
is an autonumber field. Each line of monthly data has a unique number
assigned if that helps.
Click to expand...


If you want to use a date related criteria, then you need to
have a date field in the table, possible a CollectedDate
(default of Date() ) to record when the record was created.

With that kind of field in the table, you can use a criteria
like:
 
Could use some ideas!

I am trying to identify an expression that I can use in a query to limit
lines used from a data table. For example, I have data collected over
several months. I want to select the data from the last 3 months, the last 6
months, etc. I would appreciate any ideas that I can try. The key I am using
is an autonumber field. Each line of monthly data has a unique number
assigned if that helps.
Click to expand...

An Autonumber field by itself won't help: from what you say, it's
impossible to tell if there might be 90 records entered during the
past three months, or 9, or 9,000,000.

Do you have a Date/Time field in the table which indicates when the
record was entered or collected? If so, you can use a Query with a
criterion such as

BETWEEN DateAdd("m", -3, Date()) AND Date()

to retrieve the past three months' records.

If you don't have such a date field, it is pretty much impossible
unless there is some other information within the record to categorize
it. What is the structure of your table? What is a "line of monthly
data"?

John W. Vinson[MVP]
 
John, I do have a date field but it won't work very well. What I have are
periods of data. I buy several stocks, hold them for four weeks then sell
them. I do one buy and sell each month so these become my periods. Each
month is a period and has a unique number assigned by the autonumber field.
I would like to view the last 3 periods, the last 6 periods, etc. to monitor
the performance of my stock portfolio. Maybe this information will help. I
would like to use something like "Last period -3 (this would give me the
last 3 months.) or "Last Period -6( to get the last 6 months. I appreciate
your recommendations.

Thanks!
Ed
 
John, I do have a date field but it won't work very well. What I have are
periods of data. I buy several stocks, hold them for four weeks then sell
them. I do one buy and sell each month so these become my periods. Each
month is a period and has a unique number assigned by the autonumber field.
I would like to view the last 3 periods, the last 6 periods, etc. to monitor
the performance of my stock portfolio. Maybe this information will help. I
would like to use something like "Last period -3 (this would give me the
last 3 months.) or "Last Period -6( to get the last 6 months. I appreciate
your recommendations.
Click to expand...

There is no such thing as "last period"; and you can't trust
Autonumbers. If you start to enter a record, say "oops!" and hit the
Escape key after the first keystroke - bang, an autonumber value has
been "used up" and you will have a gap. If you assume that the current
record's Period field equals 12 means that Period 9 was three months
ago, that assumption IS WRONG - or at least it's not reliable.

The best you can do is use a Top Values query:

SELECT TOP 6 [tablename].*
FROM [tablename]
ORDER BY [period];

John W. Vinson[MVP]
 
John, I appreciate the suggestion using the "Select Top 6
[tablename]............option. That may work, I will give it a try. As far
as the errors in the "autonumber" field, that should not be a problem as I
should not have any. If I make a mistake, I will simply correct the data so
that there will be no holes in the autonumber sequence. What I am doing is
recording my stock purchases in a table. There are several fields such as
stock symbol, stock purchase price, shares purchased and sell prices along
with dates of the transactions. What I am interested in is being able to
look at my performance for the last period, the last 3 periods, last 6, etc.
If your suggestions works for me, that will solve my problem totally. I will
let you know in a day or two.

Thanks Again!
Ed



John Vinson said:
John, I do have a date field but it won't work very well. What I have are
periods of data. I buy several stocks, hold them for four weeks then sell
them. I do one buy and sell each month so these become my periods. Each
month is a period and has a unique number assigned by the autonumber
field.
I would like to view the last 3 periods, the last 6 periods, etc. to
monitor
the performance of my stock portfolio. Maybe this information will help. I
would like to use something like "Last period -3 (this would give me the
last 3 months.) or "Last Period -6( to get the last 6 months. I appreciate
your recommendations.
Click to expand...

There is no such thing as "last period"; and you can't trust
Autonumbers. If you start to enter a record, say "oops!" and hit the
Escape key after the first keystroke - bang, an autonumber value has
been "used up" and you will have a gap. If you assume that the current
record's Period field equals 12 means that Period 9 was three months
ago, that assumption IS WRONG - or at least it's not reliable.

The best you can do is use a Top Values query:

SELECT TOP 6 [tablename].*
FROM [tablename]
ORDER BY [period];

John W. Vinson[MVP]
Click to expand...
 
John, I appreciate the suggestion using the "Select Top 6
[tablename]............option. That may work, I will give it a try. As far
as the errors in the "autonumber" field, that should not be a problem as I
should not have any. If I make a mistake, I will simply correct the data so
that there will be no holes in the autonumber sequence.
Click to expand...

Given that (by design) autonumbers are not updateable, that may be
harder than you think. I'd suggest using a Long Integer field and
populating it manually or with code.
What I am doing is
recording my stock purchases in a table. There are several fields such as
stock symbol, stock purchase price, shares purchased and sell prices along
with dates of the transactions. What I am interested in is being able to
look at my performance for the last period, the last 3 periods, last 6, etc.
If your suggestions works for me, that will solve my problem totally. I will
let you know in a day or two.
Click to expand...

John W. Vinson[MVP]
 
Back
Top