which is the right way ?

[pantanna]

if i have a table with dates and nr ie:

1-11-10 - 300
2-11-10 - 400
3-11-10 - 500

how is the smartest way to find out the difference of 2 numbers by entering the according 2 dates

ie from: 2-11-10 (400)
to : 3-11-10 (500), it sould come out 100.
i need your help. any help would be really appreciated.
thanks for ur time.
anna

 

[namliam]

the trick to this is to make 2 queries
1)
Make a query to get make up your previous date, calculating the previous date in a seperate column

2)
Use original table and above query, join them on your original date and the previous date in your above query... then calculate the difference easy like.

 

[ByteMyzer]

This can actually be done with ONE query.

Assume a table like the following:

MyTable
-------
DateField
NumField

The following query:

SELECT T1.*,
T1.NumField - 
  (SELECT T2.NumField
  FROM MyTable T2
  WHERE T2.DateField = (
    SELECT MAX(T3.DateField)
    FROM MyTable T3
    WHERE T3.DateField < T1.DateField)) AS NumDiff
FROM MyTable T1;

...will return these results:

DateField NumField NumDiff
1-11-2010      300 
2-11-2010      400     100
3-11-2010      500     100

 

[pantanna]

the trick to this is to make 2 queries
1)
Make a query to get make up your previous date, calculating the previous date in a seperate column

2)
Use original table and above query, join them on your original date and the previous date in your above query... then calculate the difference easy like.

thanks so much for your promt reply.
what do u mean by calculating the previous date in a seperate column?

it seems a little complicated....

 

[pantanna]

This can actually be done with ONE query.

Assume a table like the following:

MyTable
-------
DateField
NumField

The following query:

SELECT T1.*,
T1.NumField - 
  (SELECT T2.NumField
  FROM MyTable T2
  WHERE T2.DateField = (
    SELECT MAX(T3.DateField)
    FROM MyTable T3
    WHERE T3.DateField < T1.DateField)) AS NumDiff
FROM MyTable T1;

...will return these results:

DateField NumField NumDiff
1-11-2010      300 
2-11-2010      400     100
3-11-2010      500     100

THANKS SO MUCH.....!!!
i only made the table and copied and paste the code...and ...that''s it...

i think i am going to cry.....
but i have to admit i would NEVER EVER made this by myself because i am learning by myself access and this is too difficult for me...my db are more....easy...thanks again so much to both of you. i am so pleased to have entered this site because i love db.
i own a gas station with my husband and also a mini market so i am making db to "control" a few thinks.
i surely will have more questions in future time.

 

[namliam]

This can actually be done with ONE query.

Yes it can, BUT NOT IN ACCESS.

Note your not using one query neither AND your query has a HUGE scaling problem, doing this is a bad bad idea unless you know your dataset is going to be VERY limitted.

 

[namliam]

thanks so much for your promt reply.
what do u mean by calculating the previous date in a seperate column?

it seems a little complicated....

It is NOT complicated, its pretty easy...

To make a calculated column you type into the top column in the query design:
CalculatedDate: YourOriginalDate - 1

Above calculated date will always go back 1 day, as a result your query/calcuated date will return:
1-11-10 - 300 - 30-10-10
2-11-10 - 400 - 1-11-10
3-11-10 - 500 - 2-11-10

Now you take your original table and above query joining the calculated date from the query and (left) joining that to your table... You then in the second query end up with
1-11-10 - 300 - 30-10-10 - NULL - NULL
2-11-10 - 400 - 1-11-10 - 1-11-10 - 300
3-11-10 - 500 - 2-11-10 - 2-11-10 - 400

calculating the difference is then easy, what is more important... this will scale perfectly and not cause any issues when your table grows.
Myzer's solution does not scale and will cause performance issues when records contained in the table will start to grow.

 

[pantanna]

It is NOT complicated, its pretty easy...

To make a calculated column you type into the top column in the query design:
CalculatedDate: YourOriginalDate - 1

Above calculated date will always go back 1 day, as a result your query/calcuated date will return:
1-11-10 - 300 - 30-10-10
2-11-10 - 400 - 1-11-10
3-11-10 - 500 - 2-11-10

Now you take your original table and above query joining the calculated date from the query and (left) joining that to your table... You then in the second query end up with
1-11-10 - 300 - 30-10-10 - NULL - NULL
2-11-10 - 400 - 1-11-10 - 1-11-10 - 300
3-11-10 - 500 - 2-11-10 - 2-11-10 - 400

calculating the difference is then easy, what is more important... this will scale perfectly and not cause any issues when your table grows.
Myzer's solution does not scale and will cause performance issues when records contained in the table will start to grow.

i THANK YOU SOOO SOOO MUCH FOR THE GREAT HELP.
i will work on it immadiately and in case i have any question, pls let me bother u again

it seems really easier now that u explained it.

 

[pantanna]

It is NOT complicated, its pretty easy...

To make a calculated column you type into the top column in the query design:
CalculatedDate: YourOriginalDate - 1

Above calculated date will always go back 1 day, as a result your query/calcuated date will return:
1-11-10 - 300 - 30-10-10
2-11-10 - 400 - 1-11-10
3-11-10 - 500 - 2-11-10

untilll here i made it.

but i could not fix the rest. how can i enter the numbers and then calculate the difference? thanks again

 

[namliam]

You know how to join 2 tables?

Upload your (test) db perhaps I can have a look see......

 

[ByteMyzer]

Yes it can, BUT NOT IN ACCESS.

Note your not using one query neither AND your query has a HUGE scaling problem, doing this is a bad bad idea unless you know your dataset is going to be VERY limitted.

It CAN be done in Access, but you are correct, the method I used is only good for small-scale tables.

For a larger set, it can still be done in Access with a single query, but better done this way:

SELECT T1.*, T1.NumField - T2.NumField AS NumDiff
FROM MyTable T1
LEFT JOIN (
    SELECT T2.DateField, T2.DateField+1 AS DateNext, T2.NumField
    FROM MyTable T2) T2 ON T1.DateField = T2.DateNext

NOTE: Technically, it is a single SQL statement, consisting of one main query and one in-line subquery. It is essentially two queries combined into one.

 

[namliam]

It CAN be done in Access, but you are correct, the method I used is only good for small-scale tables.

Yeah sure, "one query object" however.... you still have 2 selects, thus 2 queries... as you put it... it is two queries combined into one... it isnt ONE query, it can NOT be done in one query in Access.

In oracle or sql server you can use the PARTITION BY function and really do it in one efficient query. Using the partition by you can show values from the previous row on this row and even use them to calculate with.

 

[ByteMyzer]

Yeah sure, "one query object" however.... you still have 2 selects, thus 2 queries... as you put it... it is two queries combined into one... it isnt ONE query, it can NOT be done in one query in Access.

...and yet, if you save it in Access, it appears as... ONE QUERY.

My point, namliam, is that you do not need to create a separate query object in the database; that the whole task can be accomplished in a single SQL statement, which can be saved in a single query instance. You are needlessly splitting hairs to no useful end.

Both of us have offered practical solutions for this particular problem. I am sure that pantanna is grateful for both solutions.

 

[namliam]

My point, namliam, is that you do not need to create a separate query object in the database; that the whole task can be accomplished in a single SQL statement, which can be saved in a single query instance. You are needlessly splitting hairs to no useful end.

There is a usefull end, on my end atleast...

Your assuming the OP to be knowledgable of SQL... If he is he can make the 2 objects in the database into one himself, heck (no offense to the OP) he problably wouldnt be asking this question.

Your answer is overly complex and doesnt provide a learning step for the OP, it just *magicaly* solves his problem.

By making 2 seperate objects in the DB, the OP makes something which he
a) can learn
b) can understand
c) can maintain
d) can repeat

The fact, btw, that it is one object in the database doesnt make it a single query... It is still containing a subquery, this is NOT splitting hairs... this is fundamentally wrong.

One (query) object, yes, One query, no
There is a big difference between an Object and a Query, maybe in Access it dont seem that clear, but fundamentaly they are and in other languages/packages where a lot of Access users ultimately end up if you dont know the difference... you go broke.

 

[ByteMyzer]

namliam, the most you seem to be accomplishing by your continued posts in this thread is that he who has the last word is right. (The best way to prove me wrong is not to respond to this post)

That aside, you are correct in that the method you presented provides more of a learning step for the beginner in Access. The method I presented is definitely for the intermediate to advanced user who wishes to explore the potential of advanced query methods to enumerate and report data. Both methods are equally effective in achieving the same results.

You are also correct that the method I provided uses two queries: a main query and a sub query. You also apparently ignored my acknowledgements of the merits of your previous posts.

As I stated before, both of us have offered practical solutions for this particular problem. I am sure that pantanna is grateful for both solutions. If you feel it necessary to continue posting to this thread simply to prove yourself right by having the last word, by all means, go ahead.

 

[pantanna]

It is NOT complicated, its pretty easy...

To make a calculated column you type into the top column in the query design:
CalculatedDate: YourOriginalDate - 1

Above calculated date will always go back 1 day, as a result your query/calcuated date will return:
1-11-10 - 300 - 30-10-10
2-11-10 - 400 - 1-11-10
3-11-10 - 500 - 2-11-10

Now you take your original table and above query joining the calculated date from the query and (left) joining that to your table... You then in the second query end up with
1-11-10 - 300 - 30-10-10 - NULL - NULL
2-11-10 - 400 - 1-11-10 - 1-11-10 - 300
3-11-10 - 500 - 2-11-10 - 2-11-10 - 400

calculating the difference is then easy, what is more important... this will scale perfectly and not cause any issues when your table grows.
Myzer's solution does not scale and will cause performance issues when records contained in the table will start to grow.

good day to all of you. i would like to ask my dear friend mamliam, i could do only the first part but could manage it to join it right. maybe i did not enter the exaxt epression in the price field. coule u pls help me, because i am still learning. thanks in adnvance a lot.
greeting from the rainy greece

 

[pantanna]

good day to all of you. i would like to ask my dear friend mamliam, i could do only the first part but could manage it to join it right. maybe i did not enter the exaxt epression in the price field. coule u pls help me, because i am still learning. thanks in adnvance a lot.
greeting from the rainy greece

i did it i did it!!!!! i was such a fool. i just had to think simple.... thanks anyway so much all of u for the great help.

 

[namliam]

The simple solutions are often best

Gratz on solving the puzzle

 

[pantanna]

The simple solutions are often best

Gratz on solving the puzzle

yes i guess this is true. i had i teacher he always said,
"u have to think simple in ur live if u want to solve ur problems!!"(he was a physician )

anyway. i am so glad i was working for hours... i have to many thinks to ask i ll come back sure.

good day