RCheesley
Registered User.
- Local time
- Today, 17:44
- Joined
- Aug 12, 2008
- Messages
- 243
Hi all,
I have a query which is working really well, which I am using to generate a pivot table/graph to display the number of cases per month for three organisations. These are counted from a table with the following fields:
SpecimenID
PatientDetailsID
PatientDetailsNHSNumber
SpecimenDate
SpecimenOrganism
SpecimenLocation
SpecimenPreviousDischarge
SpecimenCurrentAdmission
At the moment it's showing a bar chart using a query which counts based on month, but I've been asked to add a line demonstrating that organisation's trajectory figures.
The query uses the following:
SpecimenDate By Month: Format$(tblSpecimen.SpecimenDate,'yyyy mm') - Group by, ascending
SpecimenOrganism Group by
SpecimenLocation Group by
HospitalCode Group by
Count Of tblSpecimen: Count(*) Expression
Loc: Location(Nz([SpecimenDate],0),Nz([SpecimenCurrentAdmission],0)) Group By - this calculates Acute/Pre-48h/Community based on specimen date
GPSurg Criteria <> OUT OF AREA GP Group By(excludes out of area patients) (not selected)
Year(tblSpecimen.SpecimenDate)*12+DatePart('m',tblSpecimen.SpecimenDate)-1 Group by (not selected)
I have the trajectory figures in a separate table with the following fields:
TrajectoryID
TrajectoryName
TrajectoryHospCode
TrajectoryMonth
TrajectoryYear
TrajectoryNumber
TrajectoryOrganism
Any ideas where to start? I tried having a query based on trajectory table and having a concatenated field of yyyy mm and maybe linking this somehow to the query above that is creating the chart, but not sure how.
Basically I need to put info from two tables into one chart.
Ruth
I have a query which is working really well, which I am using to generate a pivot table/graph to display the number of cases per month for three organisations. These are counted from a table with the following fields:
SpecimenID
PatientDetailsID
PatientDetailsNHSNumber
SpecimenDate
SpecimenOrganism
SpecimenLocation
SpecimenPreviousDischarge
SpecimenCurrentAdmission
At the moment it's showing a bar chart using a query which counts based on month, but I've been asked to add a line demonstrating that organisation's trajectory figures.
The query uses the following:
SpecimenDate By Month: Format$(tblSpecimen.SpecimenDate,'yyyy mm') - Group by, ascending
SpecimenOrganism Group by
SpecimenLocation Group by
HospitalCode Group by
Count Of tblSpecimen: Count(*) Expression
Loc: Location(Nz([SpecimenDate],0),Nz([SpecimenCurrentAdmission],0)) Group By - this calculates Acute/Pre-48h/Community based on specimen date
GPSurg Criteria <> OUT OF AREA GP Group By(excludes out of area patients) (not selected)
Year(tblSpecimen.SpecimenDate)*12+DatePart('m',tblSpecimen.SpecimenDate)-1 Group by (not selected)
I have the trajectory figures in a separate table with the following fields:
TrajectoryID
TrajectoryName
TrajectoryHospCode
TrajectoryMonth
TrajectoryYear
TrajectoryNumber
TrajectoryOrganism
Any ideas where to start? I tried having a query based on trajectory table and having a concatenated field of yyyy mm and maybe linking this somehow to the query above that is creating the chart, but not sure how.
Basically I need to put info from two tables into one chart.
Ruth