SQL Query in MSAccess and mqSQL Browser
Budget: $30 – $250 AUD
Need a SQL query to generate a report. SORRY, NO SAMPLE DATA.
I have a table named "VISITS" and it has 3 columns. "PNUMBER" as double, "DNAME" as text field, "DDATE" as DATE. It holds visit of by each pet to a doctor and the date.
I need a SQL query/report that for each pet/DISTINCT PNUMBER in table VISIT in a given DATE RANGE:
1. COUNT the total number of for each visit for PNUMBER (Total Number of Visits per pet)
2. COUNT the number of each DISTINCT DNAME for that PNUMBER and then return the DNAME with the 1st highest count and it COUNT value. (Name of Doctor doctor has seen pet the most and number of times,)
3. COUNT the number of each DISTINCT DNAME for that PNUMBER and then return the DNAME with the 2nd highest count and it COUNT value (Name of Doctor doctor has seen pet the 2nd most and number of times,)
Report columns will be:
PNUMBER (UNIQUE), COUNT (PNUMBERS), DNAME(1st highest count), COUNT DNAME(1st), DNAME(2nd highest count), COUNT DNAME(2nd)
Run in MS Access and MySQL Broswer
I have a table named "VISITS" and it has 3 columns. "PNUMBER" as double, "DNAME" as text field, "DDATE" as DATE. It holds visit of by each pet to a doctor and the date.
I need a SQL query/report that for each pet/DISTINCT PNUMBER in table VISIT in a given DATE RANGE:
1. COUNT the total number of for each visit for PNUMBER (Total Number of Visits per pet)
2. COUNT the number of each DISTINCT DNAME for that PNUMBER and then return the DNAME with the 1st highest count and it COUNT value. (Name of Doctor doctor has seen pet the most and number of times,)
3. COUNT the number of each DISTINCT DNAME for that PNUMBER and then return the DNAME with the 2nd highest count and it COUNT value (Name of Doctor doctor has seen pet the 2nd most and number of times,)
Report columns will be:
PNUMBER (UNIQUE), COUNT (PNUMBERS), DNAME(1st highest count), COUNT DNAME(1st), DNAME(2nd highest count), COUNT DNAME(2nd)
Run in MS Access and MySQL Broswer