Tying to compare dates.

[Whompa1]

Hi all, i am trying to create a database query to compare dates. What i have is i find duplicates of serial numbers now i need to find out if the PGI Date and the Close out date are within 3 months of each other.

ID Serial No# PGI Date Close Out
590 04034-06-034 17/05/2012 22/05/2012
27 04034-06-034 28/12/2011 29/12/2011

ID Serial No# PGI Date Close Out
352 3160023 21/03/2012 11/04/2012
634 3160023 28/05/2012

So in the first one it has found that there is a duplicate of the serial number but the Close Out date of ID 27 is not with in 3 months of the PGI Date of 590. Where as on the second example ID 352 the Close out date is within 3 months of ID 634 PGI Date.

If anyone knows of a way to do this it would be greatly appreciated or if you would like further details just let me know.

Thanks in advance.

 

[VilaRestal]

I think this should do it:

SELECT tA.ID, tA.[Serial #], tA.[PGI Date], tA.[Close Out]
FROM Table1 AS tA
INNER JOIN Table1 AS tB ON tA.[Serial #] = tB.[Serial #]
WHERE tB.ID <> tA.ID
AND (Abs(DateDiff('m', tB.[PGI Date], tA.[Close Out])) < 4
OR Abs(DateDiff('m', tA.[PGI Date], tB.[Close Out])) < 4)

Change Table1 to the name of your table

 

[Whompa1]

This is going to sound stupid, but where do i put it?

 

[VilaRestal]

Create a new query (not the query wizard)
Cancel adding a table
Switch to SQL view
Paste it in there (remembering to change Table1 to what it should be)

 

[Whompa1]

I keep getting Syntax error in FROM clause.

 

[Whompa1]

I changed it to:

SELECT tA.ID, tA.[Serial #], tA.[PGI Date], tA.[Close Out]
FROM Instrument DB AS tA
INNER JOIN Instrument DB AS tB ON tA.[Serial #] = tB.[Serial #]
WHERE tB.ID <> tA.ID
AND (Abs(DateDiff('m', tB.[PGI Date], tA.[Close Out])) < 4
OR Abs(DateDiff('m', tA.[PGI Date], tB.[Close Out])) < 4)

 

[VilaRestal]

It needs square brackets if table name has spaces:

SELECT tA.ID, tA.[Serial #], tA.[PGI Date], tA.[Close Out]
FROM [Instrument DB] AS tA
INNER JOIN [Instrument DB] AS tB ON tA.[Serial #] = tB.[Serial #]
WHERE tB.ID <> tA.ID
AND (Abs(DateDiff('m', tB.[PGI Date], tA.[Close Out])) < 4
OR Abs(DateDiff('m', tA.[PGI Date], tB.[Close Out])) < 4)

 

[Whompa1]

Hey thanks that seems to have worked but it has brought all the blanks. But i will just have to data mine and fill in all the blanks as much as i can.

1 more question is how do i add more columns now?

 

[VilaRestal]

Blanks? Where PGI Date is null?

you can add another criteria for that:

SELECT tA.ID, tA.[Serial #], tA.[PGI Date], tA.[Close Out]
FROM [Instrument DB] AS tA
INNER JOIN [Instrument DB] AS tB ON tA.[Serial #] = tB.[Serial #]
WHERE tB.ID <> tA.ID
AND tA.[PGI Date] IS NOT NULL
AND tB.[PGI Date] IS NOT NULL
AND (Abs(DateDiff('m', tB.[PGI Date], tA.[Close Out])) < 4
OR Abs(DateDiff('m', tA.[PGI Date], tB.[Close Out])) < 4)

To add more fields:

You can switch it to design view and tick more fields from tA and even join tA to another table and add fields from that.

There's a danger when switching to design that Access will screw up the SQL. It will certainly rearrange it and should do it in a way that doesn't break it but sometimes it can.

Or you can put them in by changing the SQL.

 

[Whompa1]

Thanks that worked a treat. I hope i am not getting annoying but if i wanted to compare another field as in make sure no records contain the same notification number in that same SQL.

Any ideas.

 

[Whompa1]

sorry not the same notification

 

[VilaRestal]

Do you mean filter out those that don't or those that do?

AND tA.[notification number] = tB.[notification number]

or

AND tA.[notification number] <> tB.[notification number]

in between the other AND lines


(And no not getting annoying but feel free to click the Thanks button or the Add to Reputation button: I know it's sad but I do actually value them )

 

[Whompa1]

Hey VilaRestal, just wondering does this get rid of the records with this field empty?

AND tA.[PGI Date] IS NOT NULL
AND tB.[PGI Date] IS NOT NULL

 

[Whompa1]

Hey VilaRestal, this is what i have so far but i am still getting empty Notification cells.

FROM [Instrument DB] AS tA
INNER JOIN [Instrument DB] AS tB ON tA.[Serial No#] = tB.[Serial No#]
WHERE (((Abs(DateDiff('m',[tB].[PGI Date],[tA].[Close Out])))<4)
AND ((tB.ID)<>[tA].[ID]))
AND (tA.Notification)=[tB].[Notification])
AND ((tB.Notification) Is Not Null)
AND ((tA.Notification) Is Not Null)
AND ((Abs(DateDiff('m',[tA].[PGI Date],[tB].[Close Out])))<4));

Any ideas?

 

[VilaRestal]

Zero length strings perhaps

Try:


FROM [Instrument DB] AS tA
INNER JOIN [Instrument DB] AS tB ON tA.[Serial No#] = tB.[Serial No#]
WHERE (((Abs(DateDiff('m',[tB].[PGI Date],[tA].[Close Out])))<4)
AND ((tB.ID)<>[tA].[ID]))
AND (tA.Notification)=[tB].[Notification])
AND (Nz(tB.Notification,"") <> "")
AND (Nz(tA.Notification,"") <> "")
AND ((Abs(DateDiff('m',[tA].[PGI Date],[tB].[Close Out])))<4));

And you've changed the OR to an AND. Shouldn't it be:

FROM [Instrument DB] AS tA
INNER JOIN [Instrument DB] AS tB ON tA.[Serial No#] = tB.[Serial No#]
WHERE tB.ID<>tA.ID
AND tA.Notification=tB.Notification
AND Nz(tB.Notification,"") <> ""
AND Nz(tA.Notification,"") <> ""
AND (Abs(DateDiff('m',[tA].[PGI Date],[tB].[Close Out]))<4
OR Abs(DateDiff('m',[tB].[PGI Date],[tA].[Close Out]))<4);

?

 

[Whompa1]

Thanks VilaRestal, but i just cant seem to get this to work. I have tried that formula but it now displays only 1 record. i know there are more in there.
Here is a link to my DB. It is very messy i know it is in need of some fine tuning.

https://www.dropbox.com/s/6yw6h1hqlxh3v1e/Copy (2) of Service.accdb

 

[Whompa1]

Hey VilaRestal, i am having trouble getting this one right. It is now displaying only 1 record. I know there are more then this.

Here is a link to my DB. It is very messy i know it is in need of some fine tuning. The query i am looking at is Infanct Fail.

https://www.dropbox.com/s/6yw6h1hqlx...0Service.accdb

 

[VilaRestal]

Those links don't work for me Whompa

If it's not showing all the records it should then your filter is too strict in one way or another.

Note what I said in #15: The OR had changed to AND. That will make a huge difference to the results it shows. If you haven't put that back, do so.

If that's not it then turn off the other filters you added one by one until you get back to the results you need (and perhaps others too and then rethink that last filter).

Most importantly: read the SQL. It's not hard. It is of the form:

SELECT
field(s) to display
FROM
table(s)
WHERE
filter(s)

The filters are single statements joined together by ANDs and/or ORs with positioning of brackets making a big difference:

X AND (Y OR Z) is very different to (X AND Y) OR Z

Play with it: make a change (make a backup first and preferrably make a thoughtful change) and run it and see what happens. So long as there's no DELETE, UPDATE, INSERT or INTO words in the SQL then it won't make any changes to the data: you can't hurt it.

It would be far better for both of us (and especially yourself) if you manage to work this out for yourself.