473,320 Members | 2,073 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,320 software developers and data experts.

How to pull records BEFORE 1/1/2008

105 100+
Hi everyone,

I have a Budget table with two fields that I am concerned about (StartDate and CompleteDate). Items can be entered into the table more than once. Some items have StartDate = 3/4/2005 and CompleteDate = 4/4/2005, THEN, another record in the table lists that SAME itemID and lists its StartDate = 3/4/2008 and CompleteDate = 4/4/2008. So, there are two separate records!

What I want to do, is...If the StartDate >= 1/1/2008. then DON'T pull that StartDate = 3/4/2008 and CompleteDate = 4/4/2008 entry, but pull the MOST RECENT entry BEFORE that entry (in this case, it would be StartDate = 3/4/2005 and CompleteDate = 4/4/2005).

However, some itemID's have 4 or 5 multiple entries and not just 2. One has a StartDate = 6/9/1997 and CompleteDate = 7/9/1997, then the next entry is StartDate = 4/9/1999 and CompleteDate = 5/9/1999, and the NEXT entry is StartDate = 6/9/2003 and CompleteDate = 7/9/2003, and the LAST entry is StartDate = 6/9/2008 and CompleteDate = 7/9/2008.

Therefore, I only want to query the StartDate = 6/9/2003 and CompleteDate = 7/9/2003 entry!

Can this be done in a table such as I have?

Thanks for your help!!!
Jul 16 '08 #1
3 1181
FishVal
2,653 Expert 2GB
Hi, jmarcrum.

I guess you need to select record with Max([CompleteField]) from those where [CompleteField]<#1/1/2008#

Regards,
Fish
Jul 16 '08 #2
jmarcrum
105 100+
Hey Fish!

I try what you are suggesting in an Access query...

SELECT tblTreeTrimming.StartDate, tblTreeTrimming.CompleteDate, Max([StartDate]) AS MaxStartDate FROM tblTreeTrimming WHERE (([StartDate]<#1/1/2008#));

But it says that StartDate and CompleteDate are not part of an aggregate function?
Jul 16 '08 #3
jmarcrum
105 100+
I used StartDate because it's more important than CompleteDate for the company
Jul 16 '08 #4

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

Similar topics

5
by: Julia Baresch | last post by:
Hi everyone, I haven't found any reference to this problem on this group or in Access 97 help. I hope someone here knows this - probably a simple thing, but not obvious. I designed a query to...
1
by: RiesbeckP | last post by:
Hi All, I have a DB where there are customer numbers and a few other fields. I want to be able to pull all of the null records for a particular field as well as all the other customer numbers...
4
by: sparks | last post by:
We have a new project here, one that I have never tried maybe its easy I don't know yet. We have people that have records dating back over 5 yrs on a sql server. We have to build an access 2003...
3
by: ITSimTech | last post by:
I'm trying to learn how/do two things here: 1) If the user searches for "Data" ($searchtext = "Data") the output should also include the fourth record because Field1 contains "all". 2) But the...
12
by: crs27 | last post by:
Hai All, i want to retrive records from a table between perticular datetime range. the query is select * from geo_trip_history where t.tr_start_date between '2008-02-02' and '2008-02-29' and...
0
by: RubyRed | last post by:
Basically, I am trying to calculate each minute of an employee scheduled time during their work shift. In the table below shows only the times that the shift begin (start_minute), ended...
1
by: RubyRed | last post by:
Using SQL Server 2000. Basically, I am trying to calculate each minute of an employee scheduled time during their work shift. In the table below shows only the times that the shift begin...
8
by: Dr Al | last post by:
I have a table with four date fields, some of which may not be filled in based on our data entry needs. I have a criteria set as <date()-180 which is supposed to pull dates older than 180 days ago....
3
by: jmckown | last post by:
I'm attempting to pull a list of Google search terms from url's stored in a database. Here is an example of the data in the table I'm dealing with http://www.google.com/search?hl=en&q=proxy...
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: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
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...

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.