Comparing dates from different records

Programming and Web Development Forums - MS Office Access - Microsoft Office Access

Skip to content

Comparing dates from different records

MS Office Access
Post a reply

    Next

  • 1. DateTime Query
    Hello, i have the foloowing query in access; SELECT dbo_AnalogHistory.DateTime, dbo_AnalogHistory.Tagname, dbo_AnalogHistory.Value FROM dbo_AnalogHistory WHERE (dbo_AnalogHistory.TagName="FIR_301"); The problem is that the results i get back are for today, i want to query for 12:00 am to 11:59:59 pm on the 11/25/2006? but how do i put this in DateTime criteria? ex. [DateTime] [TagName] [Value] criteria #11/26/2006# FIR_301 Is Not Null but this does not work, any help would be welcome.
  • 2. Getting the last record stored in a table
    I've tried to create a query that identifies the last record entered into a table. The field I'm using with this totals query is called projectID which is defined with an autonumber format. Each time I run the query it returns the record with ProjectID=93 but there are 108 records in the table, so shouldn't the query return a value of ProjectID=108? I can't figure out why this is happening. Does anyone have any suggestions?
  • 3. Cut Query?
    Is their a way I could create a "cut query". What I mean by that is to cut data from one table and place it in another. Sort of like an update or append query, but I need the data from the original location removed. If that is possible can that query be but on a form in a button?
  • 4. How do I query for this report?
    This one is driving me nuts. I need to show two types of related data from the same table in the same report. I send endowment reports which include a Change of Address card showing the recipient's address as we have it. The card also invites the recipient to tell me to send the report to others. To avoid wasting the recipient's time, the card also lists everybody else already receiving the report. (Those OTHER recipients receive cards with the converse information. I hope this is clear.) Both the recipient address and the other-recipient address(es) are in the same table. Here are the related tables, which allow mix-n-match of recipients and endowments: RecipientTbl: contains name, addr, unique ID# EndowTbl: contains endowment name, unique ID# MatchTbl: contains Recipient ID matched with Endow ID. What is the best approach to querying this so that each card shows the two types of related data from the same table? Do I need a subreport for the other receipients? Thanks! -- Dave

Comparing dates from different records

Postby U2FyYQ » Wed, 30 May 2007 03:02:00 GMT 

I have a date ordered field and have been asked to identify the number of 
days since the last 24 hour or more period with no orders. So if I have 
orders on 5/1/07, 5/2/07, none on 5/3/07 and then an order on 5/4/07 have to 
create the value 5/3/07 using the dates where I do have orders, and compare 
it to the current date (which I think would just be a straight subtraction). 
I can't figure out how to idenitfy the gaps between orders since each record 
has only a singel date field. Thanks in advance for any suggestions (PS I'm 
not much of a VB coder but can copy and paste :-)
Top

Re: Comparing dates from different records

Postby Marshall Barton » Wed, 30 May 2007 14:12:00 GMT 





You can use a subquery to determine the latest date before
each record's date.

SELECT [date ordered],
				(SELECT Max(X.[date ordered]
				 FROM yourtable As X
				 WHERE X.[date ordered] < yourtable.[date ordered]
				) As PreviousDate
FROM yourtable

-- 
Marsh
MVP [MS Access]
Top

Similar Threads:

1.Comparing dates from different records 

Hi,
I have a table that contains PatientNumber, EncounterNumber, AdmitDate and 
DischargeDate.  a Patient (and PatientNumber) may have several visits or 
encounters and therefore several EncounterNumbers.  I am trying to find all 
records in which the admit date for a return visit by a patient is within 30 
days of the previous discharge date.  Here is my data:

PatientNumber    EncounterNumber   AdmitDate     DischargeDate
100100                   3040560           06/01/2009       06/03/2009
100100                   4212045           06/26/2009       06/30/2009

In this instance, the admit date of the second encounter chronologically was 
within 30 days of the discharge date of the previous encounter.  Can someone 
help with how to identify these records?

Thanks,

2.Comparing different date types in a query 

Hi,

How do you use two different date types in a query?

For example, one table has a date stored as 'dd/mm/yy' but 
a second table holds only 'yyyy'

I am trying to build a query that selects records where 
the year (and only the year) in the two tables is equal.

Help please!

Thanks

3.Comparing Dates in 2 different Objects 

I have created a query to show all of the sales for a particular group of 
sales reps (leads) by date range showing customer number.
I have a second query for all of the remaining sales reps for the same date 
range by customer number.
What I want to do is now find out if there were additional sales on the same 
customer made on the same date or later on all of the remaining sales reps.
When I use both of these queries together in a new query, my records are 
multiplying if I have more than one of the same customer in the first leads 
query.
i.e. cust. # 12345 - sale for $99
                12345 - sale for $110

The above sales appear 4 times if I have a match in the remaining sales rep 
query.
Hope this makes sense!
Help!

4.compare 2 dates in different formats 

I want to do a datedif for  2 date/times, one has the year in it and one 
doesn't.  I'd like to only select records where the date/times are 2 minutes 
or less different.  I know how to do this if I could get rid of the year in 
one date field.  Any suggestions?

5.Comparing date field values and deleting the earlier date record 

Hi 

I have a table with many duplicate records...I have used the duplicate query 
wizard to select the duplicate records....I specified a duplicate as having 
the same person and task assigned fields.  The other field that I pulled out 
into the find duplicates query result is a Date Due field (with dates in 
it).....however, I want to go one step further...now that I've selected the 
duplicate records into a separate query, I want to compare the date due 
fields of the duplicate records  and delete the record(s) with the earliest 
date due date, thus leaving only the record with the most recent Date Due 
date.  So I want to compare one duplicate record with another duplicate and 
delete the record with the earlier date due date.


Got any ideas how I can do that?  Thank you for any hints, suggestions etc.

Regards

Craig

6. comparing records with same data in one field and different in ano

7. comparing records with same data in one field and different in

8. Comparing two fields in two different records

Post a reply

Return to MS Office Access

 

Who is online

Users browsing this forum: No registered users and 58 guest

Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group