Comparing data in different rows AND columns? (1 Viewer)

[Hejren]

I am relatively new to MS Access (and totally fresh in this forum ), and I have been asked to create the following query on a single table:

First - my table's structure:

ID - (PK) int
CprNr - int
InstId - int
IndmDato - Date
UdmDato - Date
...

And here's the challenge:

Find posts, where DateDiff("d",IndmDato,UdmDato)=1 - but in DIFFERENT rows, and then merge them into one row - as in the example below:

CprNr InstId IndmDato UdmDato
1234567 17 14-03-2009 31-12-2010
1234567 17 01-01-2011 01-05-2013 (1 day between the green dates)

Merge to:

CprNr InstId IndmDato UdmDato
1234567 17 14-03-2009 01-05-2013 (This can of course easily be done in another query)

Is this even possible It seems to me that I have looked everywhere...
And if it is - then how? I'm completely stuck here, so any help will be much appreciated.

 

[jzwp22]

I think you will need a nested query (shown in blue below). It would go something like the following. You will have to substitute your own table name in place of table4. I assumed that nextudmDato had to be pulled from records that had the same CprNR and InstId values as the record supplying the IndmDato:



SELECT Table4.CprNR, Table4.InstId, Table4.IndmDato, (Select Top 1 Q1.UdmDato from table4 as Q1 where Q1.indmDato>table4.udmDato and DateDiff("d",table4.UdmDato,Q1.IndmDato)=1 and Q1.CprNR=table4.CprNr and Q1.InstId=Table4.InstId Order by Q1.udmDato ASC) AS NextUdmDato
FROM Table4
ORDER BY Table4.IndmDato;

Please be aware that SQL assumes dates are in the mm/dd/yyyy format, so you may have to convert the date fields in the WHERE clause of the nested query to long numbers using the clng() function.

 

[philben]

Hello,

Maybe, one another solution :

SELECT 
  T1.CprNr, 
  T1.InstId, 
  T1.IndmDato, 
  T2.UdmDato
FROM 
  [B][COLOR=red]MyTable[/COLOR][/B] T1 
  INNER JOIN 
   [B][COLOR=red]MyTable[/COLOR][/B] T2 
   ON T1.InstId = T2.InstId AND T1.CprNr = T2.CprNr
WHERE 
  Int(T2.IndmDato-T1.UdmDato)=1 AND T1.Id<>T2.Id;

I assumed that the name of the table is "MyTable".

Best regards,

Philippe

 

[Hejren]

Thanks a million times to both of you! This works perfectly - at least when there are only two rows, but now at least I know the way to go from here.

 

[jzwp22]

What do you mean by "at least when there are only two rows"? Can you explain further?

If you only want to show the records that return a date and ignore those that do not, then you can create another query that is based on the query I provided that ignores when the NextUdmDato is null

SELECT *
FROM myquery
WHERE NextUdmDato is Null

I'm not sure if the above is what you are after.

 

[Hejren]

What I mean is, that the result of the query will still give me two rows, if there were originally three dates following each other with only a day between IndmDate and UdmDate. So if this is the original data:

CprNr InstId IndmDate UdmDate
1234567 17 14-03-2009 31-10-2010
1234567 17 01-11-2010 31-12-2010
1234567 17 01-01-2010 01-05-2013

the result of your query still gives me two rows in stead of three. I would like to end up with only one row - even if the original data have more than two rows pr. CprNr/InstId.

I hope this explanation makes sense

However I solved this using the Min(IndmDate), Max(UdmDate) in a new query based on the first, and this works at least, even though it may not be the most elegant solution.

 

[jzwp22]

I'm glad you were able to solve it. The next step would have been to use an aggregate query with the min() or max().

 

[Hejren]

Hmmm - now I have a new problem in relation to this: is it possible to just find these rows WITHOUT merging them?

I have to find them and replace them with the data from the query described above, and this will probably be done by

1. Finding & merging data (solved)
2. Create tempTable and inserting data found in above mentioned query (solved)
3. Find the unmerged data and delete them from the original table (I thought this would be easy )
4. Insert data from tempTable
5. Drop tempTable

I have to make sure I do not delete rows where the difference between UdmDato and (next)IndmDato > 1, like in this example:

IndmDato UdmDato
01-01-2009 31-10-2010
01-01-2011 01-05-2013

I feel a headslapping moment approach... (quote from unknown )

 

[jzwp22]

I think the only way to delete the records is if you have the IDs (the PKs) of both of the records that were merged in the query. I think you will have to create 2 new queries, the first will join the query previously back to table one via the fields they have in common, select only the ID (pk). Similarly, create another query that brings in the query you created previously and join it to table2 via the common fields. Select only the ID(pk) of table2.

The problem comes in when trying to delete the records because the two delete queries cannot be run simultaneously. Ir you run one, the results from the orginal query will change, so SQL will not know which records to delete when you run the delete query for table 2.

I think the only way to handle this is to open the two queries as recordsets in Visual Basic for Application code and accummulate the ID's in a variables (one for table 1 and another for table 2). Once you have the ID's in variables you can run the two delete queries to delete the records where the ID are not included in the list held in the variables.