Query Stops after finding first Record

[XcuseBlondeness]

Ok, if this question was answered previously, I can't find it. After many days of beating my head against a wall it seems (and realizing that I am TRULY a blonde.. ), I finally was able to get my query to do what I wanted - with one exception....it stops after it matches the first record. I know there is a simple input I need to make it "Loop" until no more records are found, but I can't find it.
Here's my code (I didn't write this in SQL myself, this is just copied from SQL view of Query Design):

SELECT July04EKG.RecordNum, July04EKG.Last_name, July04EKG.First_name, July04EKG.MI, July04EKG.Address1, July04EKG.Address2, July04EKG.City, July04EKG.State, July04EKG.Zip, July04EKG.SSN, July04EKG.Phone, July04EKG.Sex, July04EKG.DOB, July04EKG.Primary_Ins, July04EKG.Secondary_Ins, July04EKG.Primary_Group, July04EKG.Secondary_Group, July04EKG.Primary_ID, July04EKG.Secondary_ID, July04EKG.DOS, July04EKG.Referring_MD, July04EKG.DX, July04EKG.Location, July04EKG.Precert, (SELECT [Reading_Dr] FROM [EKGdates] WHERE [Ekgdates].[DOS]=[July04EKG].[DOS]) AS Reading_Dr
FROM July04EKG
WHERE ((((SELECT [Reading_Dr] FROM [EKGdates] WHERE [Ekgdates].[DOS]=[July04EKG].[DOS]))<>False))
ORDER BY July04EKG.DOS;

Please tell me what I'm missing, I've tried deleting the <>False portion, but that doesn't work.
Thanks so much!!!

 

[FoFa]

Queries do not "STOP" after the first row is returned. What you are saying is this query is only returning one row and I know there should be more. I suspect you need something more along the lines of this instead, but this may not be correct either, you may have to tweak it a bit also:
SELECT July04EKG.RecordNum, July04EKG.Last_name, July04EKG.First_name, July04EKG.MI, July04EKG.Address1, July04EKG.Address2, July04EKG.City, July04EKG.State, July04EKG.Zip, July04EKG.SSN, July04EKG.Phone, July04EKG.Sex, July04EKG.DOB, July04EKG.Primary_Ins, July04EKG.Secondary_Ins, July04EKG.Primary_Group, July04EKG.Secondary_Group, July04EKG.Primary_ID, July04EKG.Secondary_ID, July04EKG.DOS, July04EKG.Referring_MD, July04EKG.DX, July04EKG.Location, July04EKG.Precert, (SELECT [Reading_Dr] FROM [EKGdates] WHERE [Ekgdates].[DOS]=[July04EKG].[DOS]) AS Reading_Dr
FROM July04EKG
LEFT JOIN EKGdates ON [Ekgdates].[DOS]=[July04EKG].[DOS]
WHERE Ekgdates.DOS is NULL (or you may want IS NOT NULL)
ORDER BY July04EKG.DOS;

But I could be offbase and not understand you question fully.

 

[XcuseBlondeness]

Thanks for help. This gave me the same results as before. I have a feeling that I'm just not doing this right at all. Let me explain what I need.
2 tables
EKGdates which has only DOS (date of service) and Reading_Dr (numeric value based upon the Dr that read EKG's that day). DOS is unique fields and PK.
July04EKG which has numerous fields as you see from the code, and is imported from a csv file received from the hospital each month. The only common field is DOS, but in this table they are not unique obviously because more than 1 patient's EKG is read on any given day.

They need reports output that have the Reading_Dr value along with the info from the July04EKG table with all the patient info for billing. the only way to get the Reading_Dr is by comparing the DOS in the EKGdates to the DOS in the July04EKG. I have report that has the query as the Record Source, but can't get this pesky step done correctly so that the Reading_Dr value appears on report.

Am I just way off base on approaching this? I'm sure it's much easier than I'm making it.
Thank you for any help you can give. I'm not very good at coding just yet so this is new ground for me.

 

[FoFa]

Your real issue is going to be if the only field you have to match on is DOS, you will get each row in the EKGdates table that matches the same date in the JULY04EKG table, not a unique match to a (person?) which is what I think you are looking for. So if you had 3 people in JULY04EKG table and 3 Entries in the ECKdates (basically matching those records)

JT ET
P1 E1
P2 E2
P3 E3
Results would be:
P1, E1
P1, E2
P1, E3
P2, E1
P2, E2
P2, E3
etc.

You are going to need something else to match on besides date of service I think if I understand you correctly.

 

[XcuseBlondeness]

Let me explain it another - probably simpler way, just in case it makes a difference.

There are about 25 doctors each assigned a numeric code for their billing system.
Dr X=1
Dr Y=2
Dr Z=3
etc.

The EKGdates table has every date (up to today) entered 1 time and the code for the doctor that did the readings that day.

The table with the patients, has about 1600 patients, their info and the date the EKG was done. And some are in there 2 or 3 times, depending upon how many EKG's were done on them that month and or that day.

The output will obviously have several patients that were read by each doctor, so I don't need unique data.

Also, I pulled up my EKGdates table and due to relationship, it has subdatasheet and there is only 1 date that has data in the subdatasheet?

Did I totally lose you? ha ha

Thank you so much for trying to help me. I really appreciate it.

 

[FoFa]

Try This, may not be what you want either, but what does it give you?

SELECT July04EKG.RecordNum, July04EKG.Last_name, July04EKG.First_name, July04EKG.MI, July04EKG.Address1, July04EKG.Address2, July04EKG.City, July04EKG.State, July04EKG.Zip, July04EKG.SSN, July04EKG.Phone, July04EKG.Sex, July04EKG.DOB, July04EKG.Primary_Ins, July04EKG.Secondary_Ins, July04EKG.Primary_Group, July04EKG.Secondary_Group, July04EKG.Primary_ID, July04EKG.Secondary_ID, July04EKG.DOS, July04EKG.Referring_MD, July04EKG.DX, July04EKG.Location, July04EKG.Precert, [EKGdates].[Reading_Dr]
FROM July04EKG
INNER JOIN EKGdates ON [Ekgdates].[DOS]=[July04EKG].[DOS]
ORDER BY July04EKG.DOS;

 

[XcuseBlondeness]

Thank you for responding.
There must be something in the tables that is not right so I'm going to go back and review all data. The same results came from this 2nd query you gave me and I moved the 1 record that it pulls to the bottom of the table (it was the 1st record) just to make sure and it's still only giving that record in the query results.

Thanks again. Hopefully, I'll find somethg in my data that is messing it up and be able to use your suggestions.

 

[XcuseBlondeness]

Got it - it was because EKGdates.DOS was hardset with slashes and the other table is imported from CSV and no slashes were there until it was in table with Input mask set. Redid the EKGdates data and all records pull with original query.

Thanks to both of you for your help. You made me think about it different ways which helped me figure out problem!!!

 

[XcuseBlondeness]

Yes Pat that was it.
Your suggestion of the update query made me go back and look at their data. This data that I have was given to me by the client and I made the mistake of "assuming" they knew what they were doing when they set up data....LOL

Thank you so much for your help