Comparing multiple all having the same data structure
HI everyone,
I have been working on a data comparison project for a while now. I finally have my data in Access, however I need a little push in the right direction.
My scenario is:
I have a company which provides me a 52 week forecast every month.
I have imported the forecast for each month into Access.
My tables are all identicle. [ID], [PN] Part Nr, [Date], [FQ] Forecast Quantity.
I need to find a way to compare any two ore more tables to find the changes in the forecast qty for the same PN and Date period.
an example
January table
PN 12345, 6/1/2011, 25
PN 12345, 7/1/2011, 150
PN 12345, 8/1/2011, 93
February table
PN 12345, 6/1/2011, 25
PN 12345, 7/1/2011, 230
PN 12345, 8/1/2011, 125
March table
PN 12345, 6/1/2011, 55
PN 12345, 7/1/2011, 230
PN 12345, 8/1/2011, 0
I'd want to know how the forecast numbers changed for each part number in the same period broken down by the monthly import table.
Each table has 496 PNs, is actually broken into 52 Date ranges with a forecast value for each so I have 25,792 individual records to move thorugh per table.
I appreciate any guidence that can be given.
MC
I have been working on a data comparison project for a while now. I finally have my data in Access, however I need a little push in the right direction.
My scenario is:
I have a company which provides me a 52 week forecast every month.
I have imported the forecast for each month into Access.
My tables are all identicle. [ID], [PN] Part Nr, [Date], [FQ] Forecast Quantity.
I need to find a way to compare any two ore more tables to find the changes in the forecast qty for the same PN and Date period.
an example
January table
PN 12345, 6/1/2011, 25
PN 12345, 7/1/2011, 150
PN 12345, 8/1/2011, 93
February table
PN 12345, 6/1/2011, 25
PN 12345, 7/1/2011, 230
PN 12345, 8/1/2011, 125
March table
PN 12345, 6/1/2011, 55
PN 12345, 7/1/2011, 230
PN 12345, 8/1/2011, 0
I'd want to know how the forecast numbers changed for each part number in the same period broken down by the monthly import table.
Each table has 496 PNs, is actually broken into 52 Date ranges with a forecast value for each so I have 25,792 individual records to move thorugh per table.
I appreciate any guidence that can be given.
MC
<<<<<Each table has 496 PNs, is actually broken into 52 Date ranges with a forecast value for each>>>>>
Your sample data does not reflect this layout.
Et
Your sample data does not reflect this layout.
Et
etsherman - the sample data is just an excerpt of the larger table. My impression is that all 496 part numbers have 52 weekly values for FQ.
MCaliebe - you say that you need to compare one or more tables and you need to know the changes by period by month changed.
So, how do you envision the results? (comparing two tables vs comparing "or more" tables)
Just the variance months listed with total FQ value?
January 12345 1/1/11 120
February 12345 1/1/11 125
June 12345 1/1/11 130
August 12345 1/1/11 123
or just the variance (+5 or -5... etc)
February 12345 1/1/11 +5
June 12345 1/1/11 +5
August 12345 1/1/11 -7
Also - as a forecast becomes actual, are you recording and reporting that as such?
I'm wondering if you you shouldn't organize your information into Access in a different manner
For example, you have 12 tables that contain:
PN, Period, FQ
You could have one table that contains:
PN, FMonth, Period, FQ
However, the answer to the first question of the data origin might also affect suggestions.
MCaliebe - you say that you need to compare one or more tables and you need to know the changes by period by month changed.
So, how do you envision the results? (comparing two tables vs comparing "or more" tables)
Just the variance months listed with total FQ value?
January 12345 1/1/11 120
February 12345 1/1/11 125
June 12345 1/1/11 130
August 12345 1/1/11 123
or just the variance (+5 or -5... etc)
February 12345 1/1/11 +5
June 12345 1/1/11 +5
August 12345 1/1/11 -7
Also - as a forecast becomes actual, are you recording and reporting that as such?
I'm wondering if you you shouldn't organize your information into Access in a different manner
For example, you have 12 tables that contain:
PN, Period, FQ
You could have one table that contains:
PN, FMonth, Period, FQ
However, the answer to the first question of the data origin might also affect suggestions.
Wileecoy: You are correct in that this was just an excerpt. The data originated in Excel, and I imported each forecast into a table, for each of the 496 part numbers, the pn is listed (52 times), date (52 times) and Forecast value (52 times)
I could easily combine the tables and yes, I can see how this would simplify the query.
Perhaps a better explination of what the data needs to be used for.
We manufacturer our products with a 3-4 month lead time. Therefore, knowing how the customer is trending on parts and "like" parts is helpful to ensure product is available once the forecast becomes current.
Catching large swings in the forecast is critical as it will either give us heads up that we need to make arangements outside our normal manufacturing channels, or if we are too far into the manufacturing process, use the past forecast data as grounds to have the cusotmer take the product that was built based on the previous data provided.
Typically, our anticipated manufacturing schedule is based on past sales history. However, this is usually an average across many previous months. If we can use the data to place a time in the future where products might be required heavier then other times, we can distribute our products to other customers in lighter times, and reseve products for the key cusotmer as we approach a heavier month.
I am increasing the point values to the maximum as I can see this may become a lot of exploration rather then simple solutions.
Thanks,
MC
I could easily combine the tables and yes, I can see how this would simplify the query.
Perhaps a better explination of what the data needs to be used for.
We manufacturer our products with a 3-4 month lead time. Therefore, knowing how the customer is trending on parts and "like" parts is helpful to ensure product is available once the forecast becomes current.
Catching large swings in the forecast is critical as it will either give us heads up that we need to make arangements outside our normal manufacturing channels, or if we are too far into the manufacturing process, use the past forecast data as grounds to have the cusotmer take the product that was built based on the previous data provided.
Typically, our anticipated manufacturing schedule is based on past sales history. However, this is usually an average across many previous months. If we can use the data to place a time in the future where products might be required heavier then other times, we can distribute our products to other customers in lighter times, and reseve products for the key cusotmer as we approach a heavier month.
I am increasing the point values to the maximum as I can see this may become a lot of exploration rather then simple solutions.
Thanks,
MC
I have the same Q as wileecoy regardiing results.
I can (probably) work out what to do to compare 2 tables.
I don't have any idea what to do with more than 2.
And if the number of tables to be used is variable then I think you are going to be into some heavy coding and the development of a solution turns into a 'project'.
I can (probably) work out what to do to compare 2 tables.
I don't have any idea what to do with more than 2.
And if the number of tables to be used is variable then I think you are going to be into some heavy coding and the development of a solution turns into a 'project'.
Ok - this is going to sound like a step backwards, but to be honest, this is how I would do it if it was me (and it has been me that had to do these reports in the past).
I would keep it in excel <gasp from crowd!>
I would have the original spreadsheet contain 14 tabs; Comparison, Summary, Jan, Feb, Mar... etc.
The Summary Tab would be in the following format:
Part, Period, Jan FQ, Feb FQ, Mar FQ... etc with the FQs all being a simple "=" formula pulling from the appropriate tab.
The Comparison tab would be the same format, but instead of showing the raw value, would show the first month as the baseline and all future months as the difference from the prior month.
Ex:
Jan Feb Mar
12345 1/1/11 250 -5 +5
12345 1/8/11 255 -2 +3
etc...
Then, you can hide the columns for the months you don't need and either show the raw numbers (summary tab) or the difference (comparison tab).
I know this seems like you'd be stepping back a decade, and it could be done with crosstab reports ... etc.
However, since the info is already given to you in separate monthly forecasts, you can just drop those into a tab and have your next report ready to go - one time setup - many times benefit.
If that isn't what you need, we can go the other route.
I would keep it in excel <gasp from crowd!>
I would have the original spreadsheet contain 14 tabs; Comparison, Summary, Jan, Feb, Mar... etc.
The Summary Tab would be in the following format:
Part, Period, Jan FQ, Feb FQ, Mar FQ... etc with the FQs all being a simple "=" formula pulling from the appropriate tab.
The Comparison tab would be the same format, but instead of showing the raw value, would show the first month as the baseline and all future months as the difference from the prior month.
Ex:
Jan Feb Mar
12345 1/1/11 250 -5 +5
12345 1/8/11 255 -2 +3
etc...
Then, you can hide the columns for the months you don't need and either show the raw numbers (summary tab) or the difference (comparison tab).
I know this seems like you'd be stepping back a decade, and it could be done with crosstab reports ... etc.
However, since the info is already given to you in separate monthly forecasts, you can just drop those into a tab and have your next report ready to go - one time setup - many times benefit.
If that isn't what you need, we can go the other route.
i may need to consider less comparing and more plotting. Use the data at the part number level one PN at a time, and display a series of numbers in a graph form, and use the forecast month to place multiple series on it. Then, we could use visual cues to research on a simpler level.
Wileecoy: thanks for the suggestion. I was also considering the same. The format that the cusotmer uses has so much overhead that it was a huge undertaking just to get the data out of it. However, now that it is out and simplified, Excel may need to be considered as the tool to use.
Well - any time you're looking at 496 parts with 52 forecast periods and 12 months of that to trend... there is going to be a lot of noise that you'll have to navigate in order to get to what you need.
The first task would be identifying what the decision-makers believe would be noteworthy items to look at. However, if yours are like most parts sold, some might be more seasonal than others or they may not be indicators of swings with other part numbers - so you can't go with a simple "if it changes by more than 10% show it" because some might always swing 10%+ while others might never.
If I was trying to make this a robust application, mine would be an application (VS or web based) with a database. It would include a setup table of threshholds to identify what trends were worth taking a closer look at.
I like the idea of plotting the data points - that would be a good way to look at the filtered group of items to review, with the raw data as backup to a graph.
The key obstacle in what you are doing is identifying the threshholds and applying them to the data you have so you don't have to look at everything, just what needs to be "managed".
If you have uniform criteria it would be easy, but if you have variance by part numbers on the criteria then it becomes "application" vs. excel unless you manually formulate the criteria by part number.
The first task would be identifying what the decision-makers believe would be noteworthy items to look at. However, if yours are like most parts sold, some might be more seasonal than others or they may not be indicators of swings with other part numbers - so you can't go with a simple "if it changes by more than 10% show it" because some might always swing 10%+ while others might never.
If I was trying to make this a robust application, mine would be an application (VS or web based) with a database. It would include a setup table of threshholds to identify what trends were worth taking a closer look at.
I like the idea of plotting the data points - that would be a good way to look at the filtered group of items to review, with the raw data as backup to a graph.
The key obstacle in what you are doing is identifying the threshholds and applying them to the data you have so you don't have to look at everything, just what needs to be "managed".
If you have uniform criteria it would be easy, but if you have variance by part numbers on the criteria then it becomes "application" vs. excel unless you manually formulate the criteria by part number.
Write a query on each table, adding a source field that identifies what table the query is from (Source: “January”, for example), include all other fields from the table. Save the queries as “QJanuary”, etc.
Then write a union query to bring all the data into one dynaset.
Select * from [Qjanuary]
Union all select * from [Qfebruary]
Union all select * from [Qmarch]
Do this for all months, ending the statement with a semicolon.
Save query as “AllMonths”. Then write a query on that, that includes all fields except FQ and ID. Group the query, then write a formula for each month Jan: sum(iif([Source] = “January”,[FC],0)), change the Total line in the query design grid to read “Expression” for all of these fields. You will then have all your data lined up for easy comparison.
Then write a union query to bring all the data into one dynaset.
Select * from [Qjanuary]
Union all select * from [Qfebruary]
Union all select * from [Qmarch]
Do this for all months, ending the statement with a semicolon.
Save query as “AllMonths”. Then write a query on that, that includes all fields except FQ and ID. Group the query, then write a formula for each month Jan: sum(iif([Source] = “January”,[FC],0)), change the Total line in the query design grid to read “Expression” for all of these fields. You will then have all your data lined up for easy comparison.
wileecoy and Mcaliebe ....
<<<<<etsherman - the sample data is just an excerpt of the larger table.>>>>>
I'm aware the sample data is partial ... but I was trying to confirm that for each month, each PN has 52 records with a date representing each week of the year. Mcalieb's sample would give the impression that it is monthly!!!!
It can be done but as Peter57r commented ... you are getting into a "Project" not just a question/answer.
This will involve extensive VBA coding and Temp tables combined with a couple of established parameters.
1.) Temp table to hold the transactions .... (496 PN's X 52 dates). This table will have 12 columns (1 for each calendar month).
2.) VBA Code/Function or SQL to loop through each PN/Date based on the month you are processing and update the temp table. You would have to dynamically base this on the month end you are processing.
3.) Once you have all PN's in one table for the YTD month you are processing ... then pull any variance needed.
Just and overview but it can be done.
ET
<<<<<etsherman - the sample data is just an excerpt of the larger table.>>>>>
I'm aware the sample data is partial ... but I was trying to confirm that for each month, each PN has 52 records with a date representing each week of the year. Mcalieb's sample would give the impression that it is monthly!!!!
It can be done but as Peter57r commented ... you are getting into a "Project" not just a question/answer.
This will involve extensive VBA coding and Temp tables combined with a couple of established parameters.
1.) Temp table to hold the transactions .... (496 PN's X 52 dates). This table will have 12 columns (1 for each calendar month).
2.) VBA Code/Function or SQL to loop through each PN/Date based on the month you are processing and update the temp table. You would have to dynamically base this on the month end you are processing.
3.) Once you have all PN's in one table for the YTD month you are processing ... then pull any variance needed.
Just and overview but it can be done.
ET
Premium Content
You need an Expert Office subscription to comment.Start Free Trial