Getting to many records back on report

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

I am getting more records than I expected. what I am
trying to do is I have two different tables that I am
getting information from. First Table getting patient
information. Second Table getting Rx Information. I only
want to return the first record from the Second Table.

This is my code:

sSQL = "SELECT MPPTbl.FirstName,
MPPTbl.LastName,MPPTbl.MiddleName,
MPPTbl.DOB,MPPTbl.LastName & ', ' & MPPTbl.FirstName
& ' ' & MPPTbl.MiddleName AS Patients, MPPTbl.Address,
PerscriptionTbl.PHDate, PerscriptionTbl.PD,
PerscriptionTbl.ControlN From MPPTbl, PerscriptionTbl
WHERE ActivePatient = '1' ORDER BY MPPTbl.LastName ASC,
PHDAte DESC "

the problem that I am having now, is it is returning all
records in the second table with repeat information from
the first table. I only want to get the first record
showing in the second table.

Can anybody help.
 
I am getting more records than I expected. what I am
trying to do is I have two different tables that I am
getting information from. First Table getting patient
information. Second Table getting Rx Information. I only
want to return the first record from the Second Table.

This is my code:

sSQL = "SELECT MPPTbl.FirstName,
MPPTbl.LastName,MPPTbl.MiddleName,
MPPTbl.DOB,MPPTbl.LastName & ', ' & MPPTbl.FirstName
& ' ' & MPPTbl.MiddleName AS Patients, MPPTbl.Address,
PerscriptionTbl.PHDate, PerscriptionTbl.PD,
PerscriptionTbl.ControlN From MPPTbl, PerscriptionTbl
WHERE ActivePatient = '1' ORDER BY MPPTbl.LastName ASC,
PHDAte DESC "

the problem that I am having now, is it is returning all
records in the second table with repeat information from
the first table. I only want to get the first record
showing in the second table.
Click to expand...

Make it a Totals query grouping by the MPPTbl fields, and select First
as the aggregate function for the PrescriptionTbl fields. Note that
First returns the first record *IN DISK STORAGE ORDER* - an order
which is arbitrary and uncontrollable, giving you an essentially
random presecription.

sSQL = "SELECT MPPTbl.FirstName,
MPPTbl.LastName,MPPTbl.MiddleName,
MPPTbl.DOB,MPPTbl.LastName & ', ' & MPPTbl.FirstName
& ' ' & MPPTbl.MiddleName AS Patients, MPPTbl.Address,
First(PerscriptionTbl.PHDate) AS FirstPHDate,
First(PerscriptionTbl.PD) AS FirstPD,
First(PerscriptionTbl.ControlN) From MPPTbl AS FirstControlN
WHERE ActivePatient = '1' GROUP BY MPPTbl.LastName ASC,
PHDAte DESC"

If by "first" record you mean the one with the earliest PHDate, you'll
need a Subquery instead.
 
maybe you should check your folders to make sure you havent made any
mistakes!
plz reply and let me know how things go!
Joanne.Cravo
 
Back
Top