Question Linking trajectories/targets to data in pivot graph

[RCheesley]

Hi guys,

I've managed to get my data displaying in a pivot graph by using a query, which displays number of samples by month at several locations (multi chart pivot) which pulls from a table called tblSpecimens.

I've now been asked to plot the corresponding trajectory onto the graphs and am a bit stumped on how to achieve this in access - I have it working fine in Excel.

The current graph displays as bars, but the trajectory would need to be a line.

I've created a table, tblTrajectories, with the following fields

TrajectoryID
TrajectoryName
TrajectoryLocationCode
TrajectoryDate
TrajectoryFigure
TrajectoryOrganism

The pivot chart currently filters on Organism and Location Code (the database contains a large number of different organisms, for the purposes of these graphs I am only showing one, and only showing from certain locations of interest) and is grouped by month, so it shows 08 2009 , 09 2009 etc. The dates these are based on come from a sample date in the format dd/mm/yyyy held in tblSpecimen.

Each Location will have different trajectories which are on a monthly basis, and they will have different trajectories for different organisms (hence the organism field)

I assume there must be some way of linking TrajectoryLocationCode, TrajectoryOrganism and the corresponding TrajectoryFigure for that month? I've tried various ways of adding the trajectories table to the query but not managed to get it working how I want it to yet!

Any suggestions greatly appreciated

Ruth