Query for order number range

[dio66]

Hi,



I’m basically a Newbie in this posting of new threads and in the MS Access as well.

I spend a lot of hours trying to get this answer out of the Internet and 6 different books of MS Access and VB with no results. I don’t even know if is possible.

Here is the Question:

I am trying to get the years from order numbers, I got a table production year and two fields year and order no. The thing that I am trying to do is to get a year by entering an order number to the system, for example for order numbers 10000 to 20000 were produced in 2007, the 20000 to 30000 in 2008 and 30000 to 40000 in 2009.

My table is:

Production year

year order no

2007 10000 to 20000
2008 20000 to 30000
2009 30000 to 40000


I need a query that I can retrieve the production year by giving an order number.




Any Help in this matter is going to be greatly appreciated.
Any guidance, brainstorms, tips, etc…

Thank a lot in advance.

[nico5038]

Make sure your table holds 3 fields:
OrderYear
NumberFrom
NumberTo

Now in a query you can get the year by using:

select OrderYear from tblYears where 123456 between NumberFrom and NumberTo

Make sure that the numbers are "inclusive"

Nic;o)

[dio66]

I built this table Years:

Order No Number From Number To

earlier2007 58998 92316
2008 92317 105721
2009 105722 118109
2010 118110 140000



I also got a table Orders with these fields:

Type of material, Warehouse, Client, Order No and Quantity in KGr.



I am trying to get the year of production from the order no.


What relationship between these tables should I use?

I am using access 2003.

And the query that is mentioned how does it work on my tables?


Many thanks.

[nico5038]

Hmm, don't you have an order date in the orders table ?
In that case the year can be extracted using :

select Year(OrderDate) from tblOrder;

A JOIN with your ordertable can be achieved using:

select * from tblOrder, tblYears
where tblOrder.[order no] between tblYears.[Number From] and  tblYears.[Number To];

I would however rename [order no] into e.g. OrderYear....

Nic;o)

[dio66]

Actually, I had an order date in order table but I cannot use it as it is imported as text from excel. I deleted the order date field from the order table so I use the order no to identify the order year.

[nico5038]

When the text date format is `fixed` it´s rather easy to extract a year, or to transform it into a real date field.
Personally I would go for that solution, as it will keep working for many years to come....

Nic;o)