473,326 Members | 2,182 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Find closest value from a given date ?

215 128KB
I stuck at this verylong time, and decided call for help again.

example I have table1 (bill)
with ItemID and Date
ID -- BDate
1 -- 1
1 -- 3
1 -- 5
1 -- 9
2 -- 1
2 -- 4

and tableLookup (to get KM field)

ID - datestart - KM
1 --- 2 --- 50
1 --- 4 --- 80
1 --- 7 --- 30
2 --- 1 --- 20
2 --- 3 ---- 40

I need KM closest to the date start like this result

ID - Bdate - KM
1 --- 1 ---- 0
1 --- 3 ---- 50
1 --- 5 ---- 80
1 --- 9 ---- 30
2 --- 1 ---- 20
2 --- 4 ---- 40

How can I do this in Access 2010, any SQL code ? or special methods ?
Help me plz, I'm sinking again.
Oct 10 '15 #1
2 1486
Seth Schrock
2,965 Expert 2GB
Try looking at the following thread and then post back any further questions you may have. How do I find the record that is closest to a given number?
Oct 10 '15 #2
hvsummer
215 128KB
@seth I read so many similar article. but It's not suit to my situation.
their field is number have order. but In my situation, those number mixed by ID (1 and 2) and date too.

well, the good news, I found the way to do this
with your suggestion from other post.

Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.ID, Table1.Date, last(tableLookup.km) AS km
  2. FROM Table1 INNER JOIN tableLookup ON Table1.id = Tablelookup.id and table1.date >= tablelookup.datestart
  3. GROUP BY Table1.ID, Table1.Date;
  4.  
very clear and clever relationship combine with last function.

but I findout 1 bug with above code:
if i change datestart of tablelookup, the result will not correct anymore, how to solve this problem ?

edit: the problem is last function depend on index of ID fields, so if i switch 2 date like 1 switch to 3 (vise versa) of ID = 2 tablelookup then result won't be correct ? if i remove index of id, it'll be completely wrong result.

edit2:
ok, finally i have to use DAODB.recordset in a function
first, open that table with condition and order by ID and Datestart.
then use recordset.movelast
after that get that last value from function...

edit3:
recordset too slow and not giving correct answer, need too much tweak.
I'm trying subquery

Finnally: subquery with sometweak work well with stacked query in side total query... I didn't know this problem lead me to this far ==
Oct 11 '15 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: MONO | last post by:
Hello! I´m trying to get the weeknumber fom a given date but at some dates will return the wrong weeknumber. WeekNumber = DatePart("ww", TodayAsDate, vbMonday, vbFirstFourDays) like...
2
by: jsfromynr | last post by:
Hi all, I have two tables CREATE TABLE ( NOT NULL , NULL , -- CURRENT DESIGNATION OF EMPLOYEE NOT NULL ) ON GO
2
by: Hexman | last post by:
Hello All, Well I'm stumped once more. Need some help. Writing a simple select and update program using VB.Net 2005 and an Access DB. I'm using parameters in my update statement and when trying...
4
by: perryclisbee via AccessMonster.com | last post by:
I have dates of service for several people that range all over each month. ie: patient had dates of service of: 7/3/2006, 7/24/2006 and 7/25/2006. I need to create a new field via a query that...
2
by: sturgeob | last post by:
Yep. Newby. I am asked to find the closest value to user defined value if not right on the mark. I can get it to determin it I have selected one of the generated numbers, but am at a loss how to...
0
by: AxleWacl | last post by:
Hi, The below error is what I am receiving. The code im using is below the error, for the life of me, I can not see where any parameter is missing..... Server Error in '/FleetcubeNews'...
1
by: oyis | last post by:
Hi, I'm looking for a function,which finds week number of given date. I found this method before,but I can't find now... Please help me...
3
by: iheartvba | last post by:
Hi Everyone, I have been using access query builder for a while now and want to perfect my SQL coding. I am trying to run this query in VBA but it gives the following error: Run-time error...
13
by: halex uk | last post by:
Hi, I am new to bytes and I was hoping you could please help me with the Error - No value given for one or more parameters that occurs on the .Open MySql line of code. I m trying to extract data...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.