Stacked graph data filter

[vbaInet]

You may have missed my last post Bob.

 

[dutchbob]

This query separates the data by looking for the productnumbers with selling data before 2013

SELECT [RoI Actual].[Number]
FROM [RoI Actual]
WHERE ((([RoI Actual].FiscalYear)<#1/1/2013#));

This query searches the complement of those data points and makes a list of the product number of products launched after 2013

SELECT DISTINCT [RoI Actual].Number
FROM [RoI Actual] LEFT JOIN SeparateTheDate ON [RoI Actual].[Number] = SeparateTheDate.[Number]
WHERE (((SeparateTheDate.Number) Is Null));

This generates the same list with the before numbers

SELECT DISTINCT SeparateTheDate.Number
FROM SeparateTheDate;

And these two generate the linkage of the numbers in the list with the data in the original table

SELECT [RoI Actual].[Number], [RoI Actual].FiscalYear, [RoI Actual].SalesEuros, [RoI Actual].Category
FROM After2013 INNER JOIN [RoI Actual] ON [After2013].[Number]=[RoI Actual].[Number];

SELECT Before2013.Number, [RoI Actual].FiscalYear, [RoI Actual].SalesEuros, [RoI Actual].Category
FROM Before2013 INNER JOIN [RoI Actual] ON Before2013.[Number] = [RoI Actual].[Number];

 

[vbaInet]

I'm sure that you can do all this in one query, but to answer you original question you use a UNION query to combine both:

SELECT [RoI Actual].[Number], [RoI Actual].FiscalYear, [RoI Actual].SalesEuros, [RoI Actual].Category
FROM After2013 INNER JOIN [RoI Actual] ON [After2013].[Number]=[RoI Actual].[Number];
[COLOR="Blue"]UNION ALL[/COLOR]
SELECT Before2013.Number, [RoI Actual].FiscalYear, [RoI Actual].SalesEuros, [RoI Actual].Category
FROM Before2013 INNER JOIN [RoI Actual] ON Before2013.[Number] = [RoI Actual].[Number];

Just ensure that the fields on both sides match.

 

[dutchbob]

I will try this, thank you. I am a rookie in Access, this is the first project with it and so far so good.

 

[dutchbob]

This is excelent, but I only need an indicator that tells me which products are launched before and which after the set date. The rest is excellent.

 

[vbaInet]

Words like Number and Category should be enclosed in square brackets because I suspect that both are (or at least Number is a) reserved keyword(s). And avoid spaces in your names too.

 

[vbaInet]

Ok, just add another field to both sides.

 

[dutchbob]

Thank you! I wish you a nice day, problem solved.

 

[vbaInet]

Good to hear!