Can't make a query compare and exclude...

[surgicalstrike]

Hi Ajax. As soon as I get home I will do so.



Have a good day.

[surgicalstrike]

Hi Ajax;

I have posted an excel file with the records mentioned above. RawData has the original dataset (sheet1) and FilteredData (sheet2) has the "anticipated" dataset.

Thanks again for your help on this.

New Query Filtered Examples.zip

have a busy day today, so not sure I'll be able to help with this. Can I suggest you attach a small dataset (in excel will be fine and only require the relevant fields) of four or 5 patients who match the criteria so they will be reported.

[CJ_London]

I've had a look at the data and have corrected my last query which had a couple of corrections required and now looks like this

SELECT DISTINCT YAGPRUNED.*
   FROM (YAGPRUNED 
        LEFT JOIN SELECT * FROM YAGPRUNED AS T WHERE CCPXCode IN ("27.3 A", "26.98A", "26.52A"))  AS Grp1 ON (YAGPRUNED.ServiceEndDate = Grp1.ServiceEndDate) AND (YAGPRUNED.PatientLinkCode = Grp1.PatientLinkCode))
           LEFT JOIN (SELECT * FROM YAGPRUNED AS T WHERE  CCPXCode IN ("28.2 B", "28.2 C", "28.4 A", "28.4 B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B"))  AS Grp2 ON (YAGPRUNED.ServiceEndDate = Grp2.ServiceEndDate) AND (YAGPRUNED.PatientLinkCode = Grp2.PatientLinkCode)
WHERE (((Grp1.AccessIDCode) Is Null) AND ((Grp2.AccessIDCode) Is Not Null)) OR (((Grp1.AccessIDCode) Is Not Null) AND ((Grp2.AccessIDCode) Is Null));

It will be the basis for your next requirement. Can you run it on your original data and confirm that it produces the same output. It should also be faster - I can't tell on a small dataset but would be interested to know the comparative timings

And to clarify your next requirement, you are only interested in returning records where a record in Group1 ("27.3 A", "26.98A", "26.52A") has the next record (ordered by ServiceEndDate) in group2 ("28.2 B", "28.2 C", "28.4 A", "28.4 B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B") - this to be broken down to

<14 days
14-30 days
31-90 days
>90 days

Just out of curiosity - why have the records on separate rows - wouldn't it be better to have them on one row - i.e. twice the 'width' - would be easier to do - see attached

[surgicalstrike]

Hi Ajax;

The query below when used in Access gives me a "Syntax Error in FROM clause", perhaps I'm using it incorrectly?

The reason why I think it makes sense to have the records on two different rows as opposed to on one line is because we might want to analyze the data if there is a different surgeonID or if there is a different ICD diagnosis.

Yes, clarifying what the next part of the query you are correct. I want to include only records that have a grp1, then a grp2 and the time between them and only if there is a grp 1 before a grp2.

Hope that helps.

Surge

I've had a look at the data and have corrected my last query which had a couple of corrections required and now looks like this

SELECT DISTINCT YAGPRUNED.*
   FROM (YAGPRUNED 
        LEFT JOIN SELECT * FROM YAGPRUNED AS T WHERE CCPXCode IN ("27.3 A", "26.98A", "26.52A"))  AS Grp1 ON (YAGPRUNED.ServiceEndDate = Grp1.ServiceEndDate) AND (YAGPRUNED.PatientLinkCode = Grp1.PatientLinkCode))
           LEFT JOIN (SELECT * FROM YAGPRUNED AS T WHERE  CCPXCode IN ("28.2 B", "28.2 C", "28.4 A", "28.4 B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B"))  AS Grp2 ON (YAGPRUNED.ServiceEndDate = Grp2.ServiceEndDate) AND (YAGPRUNED.PatientLinkCode = Grp2.PatientLinkCode)
WHERE (((Grp1.AccessIDCode) Is Null) AND ((Grp2.AccessIDCode) Is Not Null)) OR (((Grp1.AccessIDCode) Is Not Null) AND ((Grp2.AccessIDCode) Is Null));

It will be the basis for your next requirement. Can you run it on your original data and confirm that it produces the same output. It should also be faster - I can't tell on a small dataset but would be interested to know the comparative timings

And to clarify your next requirement, you are only interested in returning records where a record in Group1 ("27.3 A", "26.98A", "26.52A") has the next record (ordered by ServiceEndDate) in group2 ("28.2 B", "28.2 C", "28.4 A", "28.4 B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B") - this to be broken down to

<14 days
14-30 days
31-90 days
>90 days

Just out of curiosity - why have the records on separate rows - wouldn't it be better to have them on one row - i.e. twice the 'width' - would be easier to do - see attached

[CJ_London]

The query below when used in Access gives me a "Syntax Error in FROM clause", perhaps I'm using it incorrectly?

I created a table from your data but tidied up the query layout once I pasted it into the post to make it easier to read - doing that it seems I dropped a bracket

SELECT DISTINCT YAGPRUNED.*
   FROM (YAGPRUNED 
        LEFT JOIN (SELECT...

The reason why I think it makes sense to have the records on two different rows as opposed to on one line is because we might want to analyze the data if there is a different surgeonID or if there is a different ICD diagnosis.

still easier to do on the same row.

I'll try to look at this later

[CJ_London]

OK this seems to work

SELECT YAGPRUNED.*, IIf([grp2].[accessIDCode]=[yagpruned].[accessidcode],[daysdiff],Null) AS daysbetween
FROM YAGPRUNED INNER JOIN (SELECT Grp1.AccessIDCode, Grp2.AccessIDCode, DateDiff("d",[grp1].[serviceenddate],[grp2].[serviceenddate]) AS daysdiff FROM YAGPRUNED AS Grp1 INNER JOIN YAGPRUNED AS Grp2 ON Grp1.PatientLinkCode = Grp2.PatientLinkCode WHERE (((Grp2.AccessIDCode) In (SELECT Top 1 AccessIDCode FROM YAGPRUNED AS T WHERE PatientLinkCode=grp1.PatientLinkCode AND CCPXCode IN ("28.2 B", "28.2 C", "28.4 A", "28.4  B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B") AND ServiceEndDate>grp1.ServiceEndDate ORDER BY Serviceenddate, accessidcode)) AND ((Grp1.CCPXCODE) In ("27.3 A","26.98A","26.52A")) AND (([grp2].[serviceenddate]>[grp1].[serviceenddate] And [grp2].[serviceenddate]<Nz((SELECT TOP 1 serviceenddate FROM YAGPRUNED AS T WHERE PAtientLinkCode=grp1.PatientLinkCode AND CCPXCODE IN ("27.3 A", "26.98A", "26.52A") AND ServiceEndDate>grp1.ServiceEndDate ORDER BY ServiceEndDate),#12/31/3000#))=True)) ORDER BY Grp1.PatientLinkCode, Grp1.ServiceEndDate)  AS Use ON YAGPRUNED.AccessIDCode = Use.Grp1.AccessIDCode or YAGPRUNED.AccessIDCode = Use.Grp2.AccessIDCode ORDER BY PatientLinkCode, ServiceEndDate;

Note that this query is not visible in the query grid - if you want to do that, remove the bit in red which simplifies the join.

The bit highlighted in green is the guts of the query - copy and paste just that bit and you will get a single row result, everything else is just window dressing to get you separate rows.

I'm away from the office for much of tomorrow and the weekend so won't be able to offer much more assistance

good luck

[surgicalstrike]

Hi Ajax, hope you had a good weekend.

I tried the query listed below. It took about 4 hours to run and when it was complete Access stated that at least one record must be returned on query. I tried it again with the same results and also tried removing the extra red code that you recommended to no avail.

Perhaps I am missing something from the code?

Surge

OK this seems to work

SELECT YAGPRUNED.*, IIf([grp2].[accessIDCode]=[yagpruned].[accessidcode],[daysdiff],Null) AS daysbetween
FROM YAGPRUNED INNER JOIN (SELECT Grp1.AccessIDCode, Grp2.AccessIDCode, DateDiff("d",[grp1].[serviceenddate],[grp2].[serviceenddate]) AS daysdiff FROM YAGPRUNED AS Grp1 INNER JOIN YAGPRUNED AS Grp2 ON Grp1.PatientLinkCode = Grp2.PatientLinkCode WHERE (((Grp2.AccessIDCode) In (SELECT Top 1 AccessIDCode FROM YAGPRUNED AS T WHERE PatientLinkCode=grp1.PatientLinkCode AND CCPXCode IN ("28.2 B", "28.2 C", "28.4 A", "28.4  B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B") AND ServiceEndDate>grp1.ServiceEndDate ORDER BY Serviceenddate, accessidcode)) AND ((Grp1.CCPXCODE) In ("27.3 A","26.98A","26.52A")) AND (([grp2].[serviceenddate]>[grp1].[serviceenddate] And [grp2].[serviceenddate]<Nz((SELECT TOP 1 serviceenddate FROM YAGPRUNED AS T WHERE PAtientLinkCode=grp1.PatientLinkCode AND CCPXCODE IN ("27.3 A", "26.98A", "26.52A") AND ServiceEndDate>grp1.ServiceEndDate ORDER BY ServiceEndDate),#12/31/3000#))=True)) ORDER BY Grp1.PatientLinkCode, Grp1.ServiceEndDate)  AS Use ON YAGPRUNED.AccessIDCode = Use.Grp1.AccessIDCode or YAGPRUNED.AccessIDCode = Use.Grp2.AccessIDCode ORDER BY PatientLinkCode, ServiceEndDate;

Note that this query is not visible in the query grid - if you want to do that, remove the bit in red which simplifies the join.

The bit highlighted in green is the guts of the query - copy and paste just that bit and you will get a single row result, everything else is just window dressing to get you separate rows.

I'm away from the office for much of tomorrow and the weekend so won't be able to offer much more assistance

good luck

[CJ_London]

It works for me - see attached

Suggest you reduce your dataset so you can test it more quickly - take perhaps the first 10000 rows - to make sure it runs

I suspect there may be something wrong in your data somewhere, perhaps nulls where there shouldn't be any so you need to check for that - easiest way is to open the table and sort on each of the relevant columns in turn and see what it at the top

Also suggest you test the bit that is highlighted in green first since as I said that is the guts of the query (just copy and paste into another query), the rest is window dressing to get your two line reporting

I'm not around much the next few days, so I may not be able to respond

Good luck!

[surgicalstrike]

Hi Ajax;

I looked at your database file and you are correct it works flawlessly. Copying the text into my database does not however and I'm not entirely certain why not.

I have done as you recommended, and I have selected the first 10000 records from the YAGPRUNED table and made a new table that I called CopyForTesting. I then changed in your query all the YAGPRUNED references and replaced them with the CopyForTesting table (after ensuring that all the fields you recommended were indexed and that the field AccessIDCode (now named ID for some reason which I named back to AccessIDCode) was the primary key).

After doing all of that and running the query (which runs for about 6 minutes) I get the following Access dialog box: "At most one record can be returned by this subquery".



What am I doing wrong?

Surge.

[CJ_London]

as previously suggested,

run just the bit in green to see if that works
check your data -if you are getting the issue with 10000 records, reduce to 1000 and keep reducing until you don't get the error (or if no error increase in stages until you do) - then you can 'home in' on the faulty data. You can also try sorting the source on the fields used in the query to see if anything stands out there

[surgicalstrike]

Hi Ajax;

I have done the following to ensure data integrity as you recommended:
1. I have filtered all of the data fields and deleted any with null or zero's in them. This did not make a change to the query.
2. I began with 1000 records--query works (15 seconds).
3. I added about 500 records each time with the query taking a few seconds longer each time--query works.
5. At about 5900 records, the query runs for about a minute and then gives the previous reported error "At most one record can be returned by this subquery".

I am running the 32bit version of Access. I have searched online to see if there is some limitation to the data or if there is some issue with memory addressing or processing, but have not been able to find any thing relating to computer performance. For reference I am running a rather strong workstation:



I have included a copy of the database including 2 tables and 2 queries. The first one that works is the table CopyForTesting with 4991 records and the query (query1) that returns 344 record. The other table (CopyForTesting1) has 5913 records and the query (query2) does not work. There are no null records in the dataset and I cannot determine there difference between the two testing tables OTHER than their record count. Any thoughts on where to go from here?

Strange that it would work with a limited data set but not a larger one...have you seen this type of problem before?

Surge

Database2.zip

as previously suggested,

run just the bit in green to see if that works
check your data -if you are getting the issue with 10000 records, reduce to 1000 and keep reducing until you don't get the error (or if no error increase in stages until you do) - then you can 'home in' on the faulty data. You can also try sorting the source on the fields used in the query to see if anything stands out there

[CJ_London]

have you seen this type of problem before?

yes - and it is a data issue, it is just a case of running it down.

I've done some checking, and you have duplicates for these (grp1) codes (CCPXCODE 27.3A is in more than once) in the set that fails

AccessIDCode	PatientLinkCode	ServiceEndDate	CCPXCODE
10588	1228578	20/04/2010	27.3 A
10589	1228578	20/04/2010	27.3 A
12472	19941078	01/04/2010	27.3 A
12473	19941078	01/04/2010	27.3 A
13426	29696518	01/05/2008	27.3 A
13427	29696518	01/05/2008	27.3 A
14112	36265418	15/06/2010	27.3 A
14113	36265418	15/06/2010	27.3 A
14137	36555078	22/11/2010	27.3 A
14138	36555078	22/11/2010	27.3 A
14491	40202478	23/03/2010	27.3 A
14492	40202478	23/03/2010	27.3 A
14569	40941098	17/11/2006	27.3 A
14570	40941098	17/11/2006	27.3 A

So you need to remove one of each duplicate and this message "At most one record can be returned by this subquery".
will go away.

The part of the 'big' query which causes the error is shown here in red - this can only return 1 record, but because of the duplicates it is returning 2, hence the error message, To get around the duplicates, add the word DISTINCT where shown in green

[grp2].[serviceenddate]>[grp1].[serviceenddate] And [grp2].[serviceenddate]<Nz((SELECT DISTINCT TOP 1 serviceenddate FROM YAGPRUNED AS T WHERE PAtientLinkCode=grp1.PatientLinkCode AND CCPXCODE IN ("27.3 A", "26.98A", "26.52A") AND ServiceEndDate>grp1.ServiceEndDate ORDER BY ServiceEndDate),#31/12/3000#)

However it is better to not have the duplicates so run this query on your data and you will find all the relevant duplicates

SELECT YAGPRUNED.[PatientLinkCode], YAGPRUNED.[ServiceEndDate], YAGPRUNED.[CCPXCODE], YAGPRUNED.[AccessIDCode]
FROM YAGPRUNED
WHERE (((YAGPRUNED.[PatientLinkCode]) In (SELECT [PatientLinkCode] FROM [YAGPRUNED] As Tmp GROUP BY [PatientLinkCode],[ServiceEndDate],[CCPXCODE] HAVING Count(*)>1 And [ServiceEndDate] = [YAGPRUNED].[ServiceEndDate] And [CCPXCODE] = [YAGPRUNED].[CCPXCODE])) AND ((YAGPRUNED.CCPXCODE) In ("27.3 A","26.98A","26.52A")))
ORDER BY YAGPRUNED.[PatientLinkCode], YAGPRUNED.[ServiceEndDate], YAGPRUNED.[CCPXCODE];

That's not so many you can't delete by hand - just step through the results, deleting one or other record for each PatientLinkCode

[surgicalstrike]

Hi Ajax;

So I have done what you recommended by removing the duplicates by hand. They must have been billing mistakes over the last 10 years. Now when I run the duplicates query from below that you suggested I do not get any (duplicates). Even after that I STILL can't get the query to run beyond about 3000 records. I'll post another version doing the same thing as before with two tables but again I'm flumoxed.

Surge

yes - and it is a data issue, it is just a case of running it down.

I've done some checking, and you have duplicates for these (grp1) codes (CCPXCODE 27.3A is in more than once) in the set that fails

AccessIDCode	PatientLinkCode	ServiceEndDate	CCPXCODE
10588	1228578	20/04/2010	27.3 A
10589	1228578	20/04/2010	27.3 A
12472	19941078	01/04/2010	27.3 A
12473	19941078	01/04/2010	27.3 A
13426	29696518	01/05/2008	27.3 A
13427	29696518	01/05/2008	27.3 A
14112	36265418	15/06/2010	27.3 A
14113	36265418	15/06/2010	27.3 A
14137	36555078	22/11/2010	27.3 A
14138	36555078	22/11/2010	27.3 A
14491	40202478	23/03/2010	27.3 A
14492	40202478	23/03/2010	27.3 A
14569	40941098	17/11/2006	27.3 A
14570	40941098	17/11/2006	27.3 A

So you need to remove one of each duplicate and this message "At most one record can be returned by this subquery".
will go away.

The part of the 'big' query which causes the error is shown here in red - this can only return 1 record, but because of the duplicates it is returning 2, hence the error message, To get around the duplicates, add the word DISTINCT where shown in green


However it is better to not have the duplicates so run this query on your data and you will find all the relevant duplicates


That's not so many you can't delete by hand - just step through the results, deleting one or other record for each PatientLinkCode

[surgicalstrike]

Hi Ajax;

I still have not been able to figure out what the new problem is. I have attached a dbase like before. Each of the tables are named CopyForTesting and CopyForTesting1 with two queries. The first one has 5105 records and when run it returns a record set of 358 records in the right format.

The second table CopyForTesting1 is the exact same table with a total of 6176 records (~1000 more than the first table) and the query does not work. It runs for about 2 minutes and then returns the "At most one record can be returned by this subquery" problem that I was getting before.

Here is what I have done to see if I can track down the problem:
1. Filtered all of the fields and removed any blanks (there were none).
2. Manually removed all instances of GRP A duplicates and run the query to be sure there are none (the one that you wrote to find GRP A duplicates)
3. Ensured that the dates are all in the right format.
4. Confirmed the right datatypes for each of the fields.
5. 10 copies of random table data that is less then the first ~5100 records works every time with the current query regardless of the size (provided it's less than the full number of records from CopyForTesting)
6. Copied random chunks of data from other parts of YAGPRUNED (say between 45,000 and 50,000 records from YAGPRUNED) and tested those with similar results.
7. Run duplicate records wizard with no duplicates showing.
8. Taken the first CopyForTesting table and added to it another 5000 records (that were tested to work as well from another part of the original YAGPRUNED table) to make a total of 10,100 records. The two separate copied sets of data combined and run as one query return a result. This tells me that something is different between the selections but I'm not sure what.

Have I missed anything in the rundown of the errors that you suggest?

What do you think is the difference between CopyForTesting and CopyForTesting1 that is causing the query to give the above error?

Surge.

~[ATTACH]21675[/ATTACH

Hi Ajax;

So I have done what you recommended by removing the duplicates by hand. They must have been billing mistakes over the last 10 years. Now when I run the duplicates query from below that you suggested I do not get any (duplicates). Even after that I STILL can't get the query to run beyond about 3000 records. I'll post another version doing the same thing as before with two tables but again I'm flumoxed.

Surge

[CJ_London]

Back in Post #13, I recommended that you should use a new column CCPXGroup for faster performance and error trapping. If you had done so, this would have been picked up earlier.

You have a duplicate (PatientLinkCode 54516578) where you have two different 'group1' codes for the same date.

By adding the grouping I have also identified a CCPXCode which belongs to neither group - 26.91B for patient 59400618 in the dataset provided, there may be others in the full set (but perhaps their existence does not matter?)

So, do the following:

1. Go into the YAPRUNED table design and add a new field called CCPXGroup, as a number type (integer) and indexed, duplicates OK.

2. Then run this query to update the new field with the values for Group1 and Group2 type codes

UPDATE YAGPRUNED SET YAGPRUNED.CCPXGroup = Switch([ccpxcode] In ("27.3 A","26.98A","26.52A"),1,[ccpxcode] In ("28.2 B","28.2 C","28.4 A","28.4 B","28.5 A","28.71A","28.72B","28.74A","28.74B"),2)

3. Once run, open the table and sort the CCPXGroup column (or filter on blank) to find the records which do not belong to either group - decide what you want to do with them - at the moment they are excluded from the returned data

4. then run this query to identify group1 duplicates

SELECT YAGPRUNED.PatientLinkCode, YAGPRUNED.ServiceEndDate, YAGPRUNED.CCPXGroup, YAGPRUNED.AccessIDCode, YAGPRUNED.CCPXCODE
FROM YAGPRUNED
WHERE (((YAGPRUNED.PatientLinkCode) In (SELECT [PatientLinkCode] FROM [YAGPRUNED] As Tmp GROUP BY [PatientLinkCode],[ServiceEndDate],[CCPXGroup] HAVING Count(*)>1 And [ServiceEndDate] = [YAGPRUNED].[ServiceEndDate] And [CCPXGroup] = [YAGPRUNED].[CCPXGroup])) AND ((YAGPRUNED.CCPXGroup)=1))
ORDER BY YAGPRUNED.PatientLinkCode, YAGPRUNED.ServiceEndDate, YAGPRUNED.CCPXGroup

5. As before, decide which is the genuine record and delete the other (or just delete the 1 in CCPXGroup to exclude it)

6. now run this amended query which works with the groups

SELECT YAGPRUNED.*, IIf([grp2].[accessIDCode]=[yagpruned].[accessidcode],[daysdiff],Null) AS daysbetween
FROM YAGPRUNED INNER JOIN (SELECT Grp1.AccessIDCode, Grp2.AccessIDCode, DateDiff("d",[grp1].[serviceenddate],[grp2].[serviceenddate]) AS daysdiff FROM YAGPRUNED AS Grp1 INNER JOIN YAGPRUNED AS Grp2 ON Grp1.PatientLinkCode = Grp2.PatientLinkCode WHERE (((Grp2.AccessIDCode) In (SELECT Top 1 AccessIDCode FROM YAGPRUNED AS T WHERE PatientLinkCode=grp1.PatientLinkCode AND CCPXGroup=2 AND ServiceEndDate>grp1.ServiceEndDate ORDER BY Serviceenddate, accessidcode)) AND (Grp1.CCPXGroup=1) AND (([grp2].[serviceenddate]>[grp1].[serviceenddate] And [grp2].[serviceenddate]<Nz((SELECT TOP 1 serviceenddate FROM YAGPRUNED AS T WHERE PAtientLinkCode=grp1.PatientLinkCode AND CCPXGroup=1 AND ServiceEndDate>grp1.ServiceEndDate ORDER BY ServiceEndDate),#12/31/3000#))=True)))  AS Use ON YAGPRUNED.AccessIDCode = Use.Grp1.AccessIDCode or YAGPRUNED.AccessIDCode = Use.Grp2.AccessIDCode ORDER BY PatientLinkCode, ServiceEndDate;

7. if you run this code instead, it provides the same information, but on one line and takes a fraction of the time of the one above to run (it is basically the core of the above). Suggest you use it to test your data once you have removed duplicates etc - if this runs OK, so will the one above.

SELECT Grp1.*, Grp2.*, DateDiff("d",[grp1].[serviceenddate],[grp2].[serviceenddate]) AS daysdiff
FROM YAGPRUNED AS Grp1 INNER JOIN YAGPRUNED AS Grp2 ON Grp1.PatientLinkCode = Grp2.PatientLinkCode
WHERE (((Grp2.AccessIDCode) In (SELECT Top 1 AccessIDCode FROM YAGPRUNED AS T WHERE PatientLinkCode=grp1.PatientLinkCode AND CCPXGroup=2 AND ServiceEndDate>grp1.ServiceEndDate ORDER BY Serviceenddate, accessidcode)) AND ((Grp1.CCPXGroup)=1) AND (([grp2].[serviceenddate]>[grp1].[serviceenddate] And [grp2].[serviceenddate]<Nz((SELECT TOP 1 serviceenddate FROM YAGPRUNED AS T WHERE PAtientLinkCode=grp1.PatientLinkCode AND CCPXGroup=1 AND ServiceEndDate>grp1.ServiceEndDate ORDER BY ServiceEndDate),#12/31/3000#))=True));