Rounding up to particular numbers

[dz2k7]

Hi everybody,

I just need the main idea what to do in this situation.

Let's say we have predetermined numbers that we should roundup our purchases.

Example list:
1
2
3
4
5
6
10
12
15
20
25
30
40
50
60
80
100
120
150
200
and so on up to 1000000

so if my calculation sais i have to buy 55 peaces of something i must buy 60

Should i go throgh VBA or it is possible to do with qweries?

Thanks

 

[ByteMyzer]

Assuming the simplest approach, a single table with a single field which holds the list of values, something like the following:

Table: MyTable
-------
Field: MyValue
-------
1
2
3
4
5
6
10
12
15
20
25
30
40
50
60
80
100
120
150
200
.
.
.
1000000

The following query should do what you want:

SELECT MIN(T1.[b][i]MyValue[/i][/b]) AS MyValue
FROM [b][i]MyTable[/i][/b] AS T1
WHERE T1.[b][i]MyValue[/i][/b]>=[ParamValue];

 

[dz2k7]

Hi,
Nice to see you again.
You do so many things with this T1 as usual!
Where i should read up to understand better how it works?

 

[raskew]

Hi -

so if my calculation sais i have to buy 55 peaces of something i must buy 80

Don't understand your logic. Why wouldn't you buy 60?

Bob

 

[dz2k7]

Sorry

60 is on the list.

 

[ByteMyzer]

Hi,
Nice to see you again.
You do so many things with this T1 as usual!
Where i should read up to understand better how it works?

I use T1 as a table alias. It makes for quicker writing of SQL Statements.

Instead of:

SELECT MyFirstTable.*
FROM MyFirstTable
INNER JOIN MySecondTable ON MyFirstTable.ID = MySecondTable.ID;

...you can use aliases, like so:

SELECT [b][i]T1[/i][/b].*
FROM MyFirstTable [b][i]AS T1[/i][/b]
INNER JOIN MySecondTable [b][i]AS T2[/i][/b] ON [b][i]T1[/i][/b].ID = [b][i]T2[/i][/b].ID;

However, there is a greater advantage to using table aliases. You can use more than one instance of the same table in the same query in order to link subsets of data.

Suppose you have a table like the following:

MyTable
-------
ID | Type | Description
-----------------------
 1 | 1    | John
 1 | 2    | Doe
 2 | 1    | Jane
 2 | 2    | Doe
 3 | 1    | John
 3 | 2    | Smith

Now, let's say you wish to create a query on this table that returns the following:

ID | First Name | Last Name
---------------------------
 1 | John       | Doe
 2 | Jane       | Doe
 3 | John       | Smith

Use the following SQL Statement:

SELECT T1.ID, T1.Description AS [First Name], T2.Description AS [Last Name]
FROM MyTable AS T1
INNER JOIN MyTable AS T2 ON T1.ID = T2.ID
WHERE T1.Type = 1
AND   T2.Type = 2;

NOTE: You do not have to use T1, T2, T3, etc. as your table aliases. The alias names can be whatever you want them to be. It just happens to be one of the naming conventions that I use.

 

[dz2k7]

you opened my eyes.
I read on those alias

http://www.1keydata.com/sql/sqlalias.html

I think i'll be OK soon

Thanks a lot

 

[datAdrenaline]

As an FYI ... if you ever need to get the Ceiling of a value to a specific interval, you can use an expression like this ...

-Int(-<value> * <interval>) / <interval>

For Example: Round 23 UP to the Nearest interval of 5 (from the immediate window) ...

? -Int(-23/5) * 5
25

....

 

[Mike375]

Here is a function I got from this forum quite a while back.

Public Function RoundFactor(X As Long, F As Long) As Long
RoundFactor = (Int(X / F) - (X Mod F >= (F / 2))) * F
End Function

In your query create a new field....

NewFieldName:RoundFactor([FieldtobeRounded],10) In that case it would round to the nearest 10. Use 100 to round to nearest 100 and so on.

 

[gemma-the-husky]

a) does your list follow a computable sequence

if so, you can just calculate the appropriate quantity, rather than look it up


b) if this list is used a lot, and doesnt change much in your program, then preload the list into an array - much faster to search an array, than keep re-reading the table

 

[dz2k7]

a) no it is not computable - just a set up.

b) The array idea attracts me a lot. I just don't know how to do that.
Can you give me a reference to read up?

 

[gemma-the-husky]

dim quants(50) as long {note arrays can be resized dynamically, but just make the value big enough to hold your table
dim rst as recordset
dim dbs as database
dim index as long

set dbs=currentdb
set rst = dbs.openrecordset("quantitytable")

index=0

while not rst.eof
  index=index+1
  quants(index)=rst!orderlevel
  rst.movenext
wend

this code will move your table into the array quants, which can be examined a lot quicker than the table

note that arrays are actually zero based, but this code will load from array values 1.