Select Case

  • Thread starter Thread starter Max
  • Start date Start date
M

Max

Hi i am having two tables
1: Code
2. Data

Code-asset_value-Month_fig_refer
X-1000-2006/01
X-2000-2006/11

Data:
Cust-Code-Current_month-Asset_value
abc-X-2006/10-1000
abc-X-2006/11-2000

My question is i want to make select query which check current month value
if this value is small than Month_fig_refer, it should pick up the value for
that code, for the particular range.

Suppose user select code 'X' then Current_Month value is 2006/11 then
asset_value should be return as 2000, and not 1000

is it possible

thanx
 
Hi i am having two tables
1: Code
2. Data

Code-asset_value-Month_fig_refer
X-1000-2006/01
X-2000-2006/11

Data:
Cust-Code-Current_month-Asset_value
abc-X-2006/10-1000
abc-X-2006/11-2000
Click to expand...

So you're storing multiple pieces of information in a single field in each
table??? WHY? Fields should be "atomic" - have only one value.
My question is i want to make select query which check current month value
if this value is small than Month_fig_refer, it should pick up the value for
that code, for the particular range.

Suppose user select code 'X' then Current_Month value is 2006/11 then
asset_value should be return as 2000, and not 1000

is it possible
Click to expand...

Yes, by parsing out the various chunks of information embedded in what (to
Access) looks like a single string of characters.

Basically, if you're going to use Access, you should use it as designed. It's
MUCH easier to take multiple fields and concatenate them for display than it
is to take a composite field and split it apart!

John W. Vinson [MVP]
 
Hi John

Could it be that Max used "-" as a field separator in his post? i.e.
table Data has 4 fields Cust, Code, Current_month, Asset_value?

Max: if I'm correct, what is the data type of the fields Current_month
and Month_fig_refer?
 
Hi John,
I am storing information in multiple fields, i posted here by separating
field by "-"
 
That's the easy questions out of the way!

Max, the sample data you've given isn't enough to demonstrate what you
want to achieve. You seem to want to compare Asset_Value with
Month_fig_refer when code = X, but it's not clear what you value you
want to be returned when Asset_value is less than Month_fig_refer. In
your sample data both tables show a value of 2000 for the month
2006/11. And the other records are for months 2006/10 and 2006/01,
which is confusing.

Please explain in more detail, with a bit more sample data.


Also, as you know, an Access date/time field can't store a month, it
can only store a point in time. Are you using the beginning of the
month to represent the entire month (i.e. when you say "2006/11" the
value of the field is #2006/11/01 00:00:00#) or do you mean "any date
between 2006/11/01 and 2006/11/30"?
 
Hi John,

Month_fig_refer stores values as date/time format, 2006/01/01 - YYYY/MM/DD

Code-asset_value-Month_fig_refer
X-1000-2007/01 /01
X-2000-2007/11 /01

Here,Month_fig_refer indicates that upto this month The code corrosponding
value. Also, asset_value is fixed for certain period of time. it is not going
to change month by month.
2007/01/01 it indicates the asset value be fixed from this date, upto the
next change for code.
2007/11/11 it indicates now onwards asset value be picked up this.


Cust-Code-Current_month-Asset_value
abc-X-2007/09/10-1000
abc-X-2007/11/10-2000


Now here Customer purchase a product with Code 'X' in the month of November,
it means Asset_value should be return as '2000' as Current_Month>=
Asset_Month_fig, if current month is less than Asset_Month_fig, then it
should pick Asset_value as '1000'.


Thanx
 
I'm sorry: I can't understand the relationship between the sample data
you've posted and data and the results you seem to want.

Hi John,

Month_fig_refer stores values as date/time format, 2006/01/01 - YYYY/MM/DD

Code-asset_value-Month_fig_refer
X-1000-2007/01 /01
X-2000-2007/11 /01

Here,Month_fig_refer indicates that upto this month The code corrosponding
value. Also, asset_value is fixed for certain period of time. it is not going
to change month by month.
2007/01/01 it indicates the asset value be fixed from this date, upto the
next change for code.
2007/11/11 it indicates now onwards asset value be picked up this.


Cust-Code-Current_month-Asset_value
abc-X-2007/09/10-1000
abc-X-2007/11/10-2000


Now here Customer purchase a product with Code 'X' in the month of November,
it means Asset_value should be return as '2000' as Current_Month>=
Asset_Month_fig, if current month is less than Asset_Month_fig, then it
should pick Asset_value as '1000'.


Thanx

John Nurick said:
That's the easy questions out of the way!

Max, the sample data you've given isn't enough to demonstrate what you
want to achieve. You seem to want to compare Asset_Value with
Month_fig_refer when code = X, but it's not clear what you value you
want to be returned when Asset_value is less than Month_fig_refer. In
your sample data both tables show a value of 2000 for the month
2006/11. And the other records are for months 2006/10 and 2006/01,
which is confusing.

Please explain in more detail, with a bit more sample data.


Also, as you know, an Access date/time field can't store a month, it
can only store a point in time. Are you using the beginning of the
month to represent the entire month (i.e. when you say "2006/11" the
value of the field is #2006/11/01 00:00:00#) or do you mean "any date
between 2006/11/01 and 2006/11/30"?
Click to expand...
Click to expand...
 
Back
Top