Date & nearest Date (1 Viewer)

[DaRTHY]

Hello friends,

Hope so you can help me as soon as possible. Please Check my picture :

You can see there same ID number they all same. Those ID's have Groups (A) at this picture only 3 different groups 20 30 40. Actual Date and Appointed Date. (Others not important now.)

I need to compare Actual Date and Appointed Date and to see there nearest (closest) Appointed Date.

For example for Group 20 > Appointed Date 26.01 same as 30 and 40 too...

what shall i write to my query for to find this.

Thank you for your help.

 

[gemma-the-husky]

add a column to the query

difference: appointeddate - actualdate

if you aren't bothered about plus/minus make it

difference: abs(appointeddate - actualdate)

sort by this column.

now the closest date will be at the top

 

[DaRTHY]

I want to only closest dates. for me there ll be only 3 Date records. Others are not relevant. I don't wanna see not relevant records. Of course Actual Date can be variable.

P100911, 20, .... 31.12.2015, 26.03.2016
P100911, 30, .... 31.12.2015, 26.03.2016
P100911, 40, .... 31.12.2015, 26.03.2016

 

[gemma-the-husky]

well you can't find the nearest date unless you evaluate how close all the dates are, can you?

how you use the closest date after you identify it, is a different issue.

 

[DaRTHY]

I dont need to difference. I ll send better example. May be i can explain myself much better. (im sending 1 more picture.)

Project, Actual, Appointed
P102345, 28.02.2016, 17.03.2016
P102390, 31.12.2015, 26.01.2016
P102405, 31.03.2016, 26.04.2016

These are closest Actual and Appointed Dates for me. Somehow (i guess) i need to compare those dates and which one is nearest one. I need to see it.

 

[plog]

Somehow (i guess) i need to compare those dates and which one is nearest one.

After reading this whole post 5 times, I think I might have found the misunderstanding. What do you mean by nearest one? To which date?

Let's use your last dataset as an example:

Project, Actual, Appointed
P102345, 28.02.2016, 17.03.2016
P102390, 31.12.2015, 26.01.2016
P102405, 31.03.2016, 26.04.2016


1. Do you mean the closet out of those 6 dates to today? Which means the answer would be P102390's ActualDate.

2. Do you mean the closet AppointedDate to its ActualDate? Which would be the record P102345.

Is it #1 or #2? You never tell us what "nearest" is in reference to.

 

[DaRTHY]

you should check 2. picture to understand my data records to understand. and yes i mean 2. one.

For P102345 => actual 28.02.2016 but there is 11 different Appointed date (closest Appointed Date to Actual Date) thats mean 17.03.2016 is closest Appointed Date for this ID.

If i talk about P102390 => Actual Date is 31.12.2015 and for this date the closest Appointed Date is 26.01.2016

Could i explain myself better ?

 

[plog]

you should check 2. picture to understand my data records to understand. and yes i mean 2. one.
...
Could i explain myself better ?

Yes. Use more nouns and be more explicit. "you should check 2" is pretty ambigous, what is 2? The second picture? The line I labled #2 in my previous post?

Actually, it would be best to just use data to demonstrate what you want. Give us 2 sets of data

A. Starting sample data (like you did in your pictures). Include table and field names and enough sample data to cover all cases.

B. Resulting data. Show us what you expect your query to return based on the data you provide in A.

You never show us what you want, you keep trying to explain and it throws everyone off. Don't try to explain, use data to demonstrate it.

 

[DaRTHY]

I cant send content of my DB. Somehow i must explain... i ll try and try and try. If i send any of records that ll be end for me at this company and i can go in jail. And if i create for every question different DB, i must work on DB forever.

That's why i will try to explain again :

my second Picture "Storno" there is ID at this picture. There is only 3 repeating ID's . Those are
P102345
P102390
P102405

For every ID has a different groups numbers (there is only "ALLE" you can see but normally much more. It can be 1,10,30,40,50....)

Now Picture of "Storno" again:

For ID => P102345 as you can see there is as Actual Date only 28.02.2016 but 11 different Appointed Date. And closest Appointed Date to Actual Date is (if you check picture of "Storno" again) 23.02.2016 is not closest one it past. what is next one 17.03.2016. I need to create and write on new Field, Next to Appointed Date in Design View And there shall i see as date 17.03.216

For ID P102390 => Actual Date is 31.12.2015 and for this date the closest Appointed Date is 26.01.2016
And the last one
For ID P102405 =>Actual Date is 31.03.2016, and for this date the closest Appointed Date is 26.04.2016

at the end there will be total : 3 Data Records.

now im answering your question :

#2

if could not explain myself Again. Please close this topic and delete it.

thanks.

 

[gemma-the-husky]

and I said.

you need to evaluate the absolute date difference for every record (a "select" query)

then, use this query as the basis for another query - for each different product ID, select the minimum difference. (a "totals query")

Have you tried doing it at all?