Solved Combine two Queries in a chart and format

[chizzy42]

Hi All, hope all is good. I have a question I cant seem to get an answer on and I was wondering if it is possible . I've attached two charts I produce on a database showing cases still open and cases closed. My question is can I combine the two charts into one and keep the formatting i.e. closed results columns stay green and and open columns stay red. I could have a query that returns all but cant see how I could format the chart to show what's open and what's closed

query for open chart :

SELECT tblRPS.RPSno, tblRPS.DateRaised, tblRPS.Owner, tblRPS.PartDesc, tblRPS.FailureMode, tblRPS.signoffdate, tblRPS.signoffdate2, tblRPS.signedoff, (Date()-[Dateraised]) AS DaysOpen, tblRPS.Partno
FROM tblRPS
WHERE (((tblRPS.DateRaised)<Date()-5 And (tblRPS.DateRaised)>=Date()-365) AND ((tblRPS.signedoff)=No));

query for closed chart :

SELECT tblRPS.RPSno, tblRPS.DateRaised, tblRPS.Owner, tblRPS.PartDesc, tblRPS.FailureMode, tblRPS.signoffdate, tblRPS.signoffdate2, tblRPS.signedoff, (Date()-[Dateraised]) AS DaysOpen, tblRPS.Partno
FROM tblRPS
WHERE (((tblRPS.DateRaised)<Date()-5 And (tblRPS.DateRaised)>=Date()-365) AND ((tblRPS.signedoff)=Yes));

Any feedback on this would be greatly appreciated as always

regards

 

[June7]

Possibly not. I analyze chart issues best when I can work with data. If you want to provide db for analysis, follow instructions at bottom of my post.

Could overlay charts. One would be set transparent with axes and lines turned off. Make sure the scales are the same.

 

[arnelgp]

i think it is possible since you are retrieving same columns and
somewhat the same on the Criteria.

only remove signedOff in the criteria and create a Calculated column (2 columns), like:

SignOff: IIF(signedOff=-1, (Date()-[Dateraised]))
NotSignOff: IIF(signedOff=0, (Date()-[Dateraised]))

 

[chizzy42]

Thanks arnelgp, that looks to give me the combined data on the chart I was looking for , I was trying to find a way to add two series and got well confused in the process with modern charts and wasn't sure it could be done on modern charts.
Thanks also June7 the overlay transparency idea has helped me on another idea I was looking at to build up data visually.