boris
Posts: 14
|
| Posted: 08/16/2004, 4:57 PM |
|
I have an Access database which contains some data of serial numbers of equipment.
The database is designed with the following fields:-
Year, Model1, Model2 etc. In the Model fields is the FIRST serial number of that equipment for the year.
e.g Record 1 is: Year = 1980, Model1=556
Record 2 is: Year=1981, Model1=630 (etc).
What i need to do is the user selects a Model (which is the field names eg. Model1). Enter's a serial number, and do a search and come up with a corresponding Year for the specific serial number. The problem i have is if the serial number (in Model1 field) i am looking for is 629 i need it to find the corrsponding Year. In this example it would be 1980 as the first serial number for 1981 is 630.
Unfortunatly the database cannot be re-designed and include all the serial numbers for a given year, it only contains the first serial number for that year.
I'm sorry if my explanation is confusing but hopefully you will get my drift. I would appreciate hearing any ideas on how i can accomplish this?
Thanks.
|
 |
 |
DonB
|
| Posted: 08/16/2004, 5:34 PM |
|
1st - what's the minimum Model1 , by year? (did you REALLY used a reserved
word for the column name???)
SELECT MIN(Model1) From sometable GROUP BY [Year]
You have Access, so create a view (assumed here to be called "theview") with
the above SQL
2nd - Into what year does a given Model1 fall?
Select MAX([Year]) FROM theview WHERE Model1 <= somemodelnumber
Should work...
--
DonB
http://www.gotodon.com/ccbth
"boris" <boris@forum.codecharge> wrote in message
news:6412149cc551b8@news.codecharge.com...
> I have an Access database which contains some data of serial numbers of
> equipment.
>
> The database is designed with the following fields:-
> Year, Model1, Model2 etc. In the Model fields is the FIRST serial number
of
> that equipment for the year.
>
> e.g Record 1 is: Year = 1980, Model1=556
> Record 2 is: Year=1981, Model1=630 (etc).
>
> What i need to do is the user selects a Model (which is the field names
eg.
> Model1). Enter's a serial number, and do a search and come up with a
> corresponding Year for the specific serial number. The problem i have is
if the
> serial number (in Model1 field) i am looking for is 629 i need it to find
the
> corrsponding Year. In this example it would be 1980 as the first serial
number
> for 1981 is 630.
>
> Unfortunatly the database cannot be re-designed and include all the serial
> numbers for a given year, it only contains the first serial number for
that
> year.
>
> I'm sorry if my explanation is confusing but hopefully you will get my
drift. I
> would appreciate hearing any ideas on how i can accomplish this?
>
> Thanks.
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
