query does not work after saving (1 Viewer)

[pbuethe]

I have a query which has worked in the past. Today it ran slow, and when it finally came up with results, it hung and did not display the number of records. I tried changing the query. Before saving, I ran the query and it ran quickly, giving the expected number of records (162). After I saved the query, it hung again when I ran it. I tried re-creating the query from scratch. Again it ran before saving but hung after saving. Compact and repair also did not help. I am running Access 2007. Hope you can help.

 

[MSAccessRookie]

Does the Query have a Sub-Query that is being lost? Sometimes MS Access does not store Sub Queries the way that you write them and they can becoime corrupt. Right now, all we can do is guess, but if you can show us the Query, then perhaps we can be more helpful.

 

[pbuethe]

The query is:

SELECT qryHasHealthRelated.CaseID, tblFacilities.FacilityName, qryHasHealthRelated.ColumnNo, qryHasHealthRelated.PageSeq, qryHasHealthRelated.AUID, qryHasHealthRelated.Sample, qryHasHealthRelated.RevType, qryHasHealthRelated.BillDate, qryHasHealthRelated.NoVisit, qryHasHealthRelated.MedVisit, qryHasHealthRelated.NurseVisit, qryHasHealthRelated.MSWLCSW, qryHasHealthRelated.Nutrition, qryHasHealthRelated.MedGrp, qryHasHealthRelated.MedicalGrp, qryHasHealthRelated.SubstGrp, qryHasHealthRelated.MHSupGrp, qryHasHealthRelated.NutriGrp, qryHasHealthRelated.CAT, qryHasHealthRelated.PT, qryHasHealthRelated.CaseMgt, qryHasHealthRelated.PrevRisk, qryHasHealthRelated.RoutAssess, qryHasHealthRelated.Acupunct, qryHasHealthRelated.Chiropract, qryHasHealthRelated.GroupExercise, qryHasHealthRelated.Massage, qryHasHealthRelated.PastoralCare, qryHasHealthRelated.LifeSkills, qryHasHealthRelated.OtherGroup, qryHasHealthRelated.CommMtg, qryHasHealthRelated.ArtCrafts, qryHasHealthRelated.Games, qryHasHealthRelated.Movies, qryHasHealthRelated.FieldTrips, qryHasHealthRelated.Exercise, qryHasHealthRelated.CurrEvents, qryHasHealthRelated.VocationEd, qryHasHealthRelated.CompSkills, qryHasHealthRelated.PeerSup, qryHasHealthRelated.PageNo, qryClaims.PaymentAmount
FROM (((qryHasHealthRelated INNER JOIN qryNoCore ON (qryHasHealthRelated.CaseID = qryNoCore.CaseID) AND (qryHasHealthRelated.BillDate = qryNoCore.BillDate)) INNER JOIN qryNoNonHealthRelated ON (qryHasHealthRelated.CaseID = qryNoNonHealthRelated.CaseID) AND (qryHasHealthRelated.BillDate = qryNoNonHealthRelated.BillDate)) INNER JOIN tblFacilities ON qryHasHealthRelated.AUID = tblFacilities.AUID) INNER JOIN qryClaims ON (qryHasHealthRelated.CaseID = qryClaims.CaseID) AND (qryHasHealthRelated.BillDate = qryClaims.ServiceDate);

 

[pbuethe]

I got it to work. The query qryClaims was causing the problem. I updated CaseID in one of the underlying tables. Then I was able to simplify qryClaims so it (and the queries based on it) would run quickly.