Stacked graph data filter

dutchbob

Registered User.
Local time
Today, 04:23
Joined
Jun 16, 2015
Messages
17
Hi all,

I have a problem with making a stacked graph with my data with a movable filter. I shall explain myself.

I have a (simplified) table with productnr., period (year and month) of sales, and sales (in currency). Now I want to know the contribution of the products that have their period of sales after a certain number. This will calculate the contribution to sales of new products. Simple stuff.. (i hope), but I was not able to do so...

I have managed to separate the table with multiple queries and I have managed to have now to queries that can show the sales of products that have been launched before the date and after the date, but combining the two tables is impossible for me.. (might be cumbersome)

Can you help me?

Thank you in advance!

Kind regards, Bob
 
I don't really follow your explanation. Can you demonstrate with data? Provide 2 sets of data:

A. Starting sample data from your table(s). Include table and field names and enough sample data to cover all cases.

B. Expected results based on A. Show me what you expect to end up with when you feed this query the starting sample data.

I do see a potential issue--the period field. You say this is some sort of month/year code, but then you later on you want to see things that occur before/after some date. Access makes doing date manipulations/comparisons easy when you have actual dates, but when you make these truncated codes that represent dates it makes things messy. I fear that might be an issue here.
 
Hi Plog,

I want to create such a graph in Access:

postimg. org/i mage/f88leuhml

With this database (very simplified example):

Number ProductDescription CategoryNumber Category FiscalYear SalesEuros
1 ProductDescr1 Category1 First Jul-13 2080
1 ProductDescr1 Category1 First May-11 7219
1 ProductDescr1 Category1 First Feb-11 2757
1 ProductDescr1 Category1 First Nov-11 7053
1 ProductDescr1 Category1 First Sep-13 7230
1 ProductDescr1 Category1 First Jul-12 3615
1 ProductDescr1 Category1 First Nov-13 2424
1 ProductDescr1 Category1 First Jun-11 1064
1 ProductDescr1 Category1 First Sep-13 8179
1 ProductDescr1 Category1 First Feb-15 3596
1 ProductDescr1 Category1 First Jan-14 1366
1 ProductDescr1 Category1 First Jul-11 7874
1 ProductDescr1 Category1 First Jun-11 6127
1 ProductDescr1 Category1 First Oct-12 9074
1 ProductDescr1 Category1 First Dec-14 6497
1 ProductDescr1 Category1 First Jan-15 1017
1 ProductDescr1 Category1 First Jan-14 4848
1 ProductDescr1 Category1 First Jul-12 2884
1 ProductDescr1 Category1 First Apr-11 6173
1 ProductDescr1 Category1 First Mar-11 5350
1 ProductDescr1 Category1 First Oct-13 8786
1 ProductDescr1 Category1 First Jan-11 1133
2 ProductDescr2 Category1 First Dec-13 4417
2 ProductDescr2 Category1 First Jan-13 3093
2 ProductDescr2 Category1 First Nov-14 7235
2 ProductDescr2 Category1 First Nov-11 8186
2 ProductDescr2 Category1 First Nov-14 4015
2 ProductDescr2 Category1 First Aug-13 2575
2 ProductDescr2 Category1 First Nov-13 9284
2 ProductDescr2 Category1 First Feb-14 6642
2 ProductDescr2 Category1 First Mar-11 9608
2 ProductDescr2 Category1 First Oct-12 9293
2 ProductDescr2 Category1 First Apr-14 6707
2 ProductDescr2 Category1 First Jan-14 2833
2 ProductDescr2 Category1 First Nov-13 3992
2 ProductDescr2 Category1 First Sep-11 3235
2 ProductDescr2 Category1 First Nov-13 6981
2 ProductDescr2 Category1 First May-12 6306
2 ProductDescr2 Category1 First Jul-11 1788
2 ProductDescr2 Category1 First Feb-13 5595
2 ProductDescr2 Category1 First Feb-12 9672
2 ProductDescr2 Category1 First Dec-11 2247
2 ProductDescr2 Category1 First Aug-11 4577
2 ProductDescr2 Category1 First Mar-11 3168
3 ProductDescr3 Category2 Second Feb-15 7420
3 ProductDescr3 Category2 Second Nov-14 1148
3 ProductDescr3 Category2 Second Sep-13 4788
3 ProductDescr3 Category2 Second Mar-14 6535
3 ProductDescr3 Category2 Second Jan-15 4220
3 ProductDescr3 Category2 Second Sep-13 9019
3 ProductDescr3 Category2 Second Jul-12 8774
3 ProductDescr3 Category2 Second Nov-12 2066
3 ProductDescr3 Category2 Second Jun-12 6272
3 ProductDescr3 Category2 Second Sep-11 4468
3 ProductDescr3 Category2 Second Nov-13 2559
3 ProductDescr3 Category2 Second Jun-12 7307
3 ProductDescr3 Category2 Second Mar-14 9741
3 ProductDescr3 Category2 Second Aug-13 3022
3 ProductDescr3 Category2 Second May-13 8160
3 ProductDescr3 Category2 Second Jun-14 4575
4 ProductDescr4 Category2 Second Dec-14 2005
4 ProductDescr4 Category2 Second Oct-14 8371
4 ProductDescr4 Category2 Second Dec-14 5421
4 ProductDescr4 Category2 Second Aug-14 2304
4 ProductDescr4 Category2 Second Feb-15 9440
4 ProductDescr4 Category2 Second Oct-14 2030
4 ProductDescr4 Category2 Second Oct-14 9340
4 ProductDescr4 Category2 Second Jan-15 2850
4 ProductDescr4 Category2 Second Aug-14 6608
4 ProductDescr4 Category2 Second Oct-14 4998
4 ProductDescr4 Category2 Second Sep-14 4416
4 ProductDescr4 Category2 Second Jan-15 8379
4 ProductDescr4 Category2 Second Feb-15 4321
4 ProductDescr4 Category2 Second Jan-15 8690
4 ProductDescr4 Category2 Second Oct-14 8634
4 ProductDescr4 Category2 Second Feb-15 8468
4 ProductDescr4 Category2 Second Aug-14 7916
4 ProductDescr4 Category2 Second Oct-14 3765
4 ProductDescr4 Category2 Second Dec-14 2960
4 ProductDescr4 Category2 Second Sep-14 7495

Thank you!
 
D+. No table name, you didn't provide A & B, you didn't identify if you provided A or B and the data you did provide is hard for me to visually parse.

Your initial post (and the sub-forum you posted in, "Queries") made it seem like you needed help with a query to produce the correct results (then you would use that query upon which to build a chart). Getting that query to work is what I can help you with. But to do so, I would need everything I laid out in my initial post. I would need expected data, not what the chart is to look like.

When posting data, use this style:

TableNameHere
Field1Name, Field2Name, Field3Name, ...
Sally, 23, 10/12/2013,...
Dave, 18, 9/2/2014, ...
 
I will make it somewhat easier, sorry to be so vague, but the entries are confidential and I have no way to make it clearer. I have filled the entries in another database that you can find here:

dropbox. com /s/vou1a2tap2u0xt5/Access%20Forum.accdb?dl=0

Thanks in advance,

Bob
 
No offense, but I'm not downloading that. It's about 50MB in size, there's no need for me to download something that large for the issue you have described and what I have requested in solving it.
 
Hi vbaInet,

Thanks for helping me. I do not have a problem creating the graph itself, but I am trying to first split a table, values with first sale date before a certain value in one table and products with the first sale date after a certain data in a second table, and then combining the two (with the differentiation intact). It seems like a bit weird thing to do, thats is why I ve shared my database in a dropbox folder (see link above). It is 50 mb (dont know why) but it is not that extended.

Kind regards.

Bob
 
I don't tend to download files from other sites as well and it's fairly big for a db. All we need is a few sample data of what you currently have and how you would like it to look, put in a spreadsheet and uploaded on this site.
 
Next to the "Post Quick Reply" button you'll find a button captioned "Go Advanced", click that and on the next page you'll see a "Manage Attachments" button mid page.
 
I have an upload error, I states ivalid file. (this is the first thread that I've posted here)
 
Yes I notice that... zip the file and it should come through.
 
Yes, managed to do it. I have here the "raw" data of the system (it is only a set of quickly made test data) and the outcome figure. Notice that product 4 in the dataset is the only product with a date after 2013, so sales data of that file are split from the rest and then combined within the graph.

Thanks!

Gr. Bob
 

Attachments

Can you upload the before and the after in one sheet. You mentioned that creating the chart is not the problem, so the problem is of course changing the raw data, I need to see that final result.
 
After the separation the data looks like this, but combining them in one access table is the main problem.
 

Attachments

So you have multiple queries and now you want to combine them?

Copy and paste the SQL of the two queries involved here.
 
Note that this is the outcome of two queries. I want those to combined, but also that they are distinguished. I hope that I am clear
 

Users who are viewing this thread

Back
Top Bottom