Field based on month/year of separate field (1 Viewer)

[RCheesley]

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