Access 2010 Transpose and smoothed data

[ENNIN12]

I have an access query that I need to:
1. smooth data if sales for one week is greater than 400% of previous week to override with the average of previous 4 weeks.
2. transpose the data so that it displays vertically first 6 columns repeats Rep#, City_Name, State, Retailer, Brand, and week and then populates, unadjusted and smoothed

Rep #	City_Name	State	Retail	Brand	Week

[ranman256]

your data is stored wrong. You dont put dates across as fields, they are stored as values;

ID	zip	Retailer	Brand	Date	Amt
1	00603	(Unassigned)	Prod1	01/10/2015	3023.90465927124
2	00603	(Unassigned)	MT	01/10/2015	23559.1827716827
3	00623	(Unassigned)	Prod2	01/10/2015	1539.59830379486
4	00646	(Unassigned)	Prod3	01/10/2015	21124

then you can run Crosstab queries to get what you want.
you can rebuild this table with a series of append queries to normalize the table.

[ENNIN12]

your data is stored wrong. You dont put dates across as fields, they are stored as values;

ID	zip	Retailer	Brand	Date	Amt
1	00603	(Unassigned)	Prod1	01/10/2015	3023.90465927124
2	00603	(Unassigned)	MT	01/10/2015	23559.1827716827
3	00623	(Unassigned)	Prod2	01/10/2015	1539.59830379486
4	00646	(Unassigned)	Prod3	01/10/2015	21124

then you can run Crosstab queries to get what you want.
you can rebuild this table with a series of append queries to normalize the table.

The challenge I have is that the file exceeds a million rows in excel and thus why I have the dates horizontally vs. vertically. So the goal is to bring into access with all columns and then transpose, and smooth data using 400% rule. Thanks

[aytee111]

Use Excel for Excel stuff and Access for Access stuff. This is an Excel spreadsheet masquerading as an Access table. With some serious VBA you could accomplish what you are trying to do, but then do it in Excel.

[ENNIN12]

I am comfortable using excel, but per my earlier note the rows will exceed 1 million and thus why importing into access to do the manipulation there as there are limitations in excel

[CJ_London]

Excel and Access are like trains and planes. Both carry lots of passengers from one fixed location to another, but in completely different ways.

To do what you want in Access you'll need to run a number of append queries to get the data into one that access can work with - per Ranmans' suggestion.

If you are concerned about space, change the retailer column to Boolean, provide a lookup table for brand and if zip is always numeric, convert zip to a number in both alignment table and raw data table (you can always format it to provide the preceding zeros). With this volume of records, also ensure the necessary fields (ones you link, filter or sort on) are indexed.

FYI

text requires 10 bytes plus 1 byte per character
Booleans require 2 bytes
numbers require 2 bytes for an integer, 4 bytes for a long. If your postcode is never greater than 32767 then you can use integer.
dates require 8 bytes
doubles require 8 bytes



So

you currently have a 1m rows of a record that has 1 long (8), 5 chars(15), say 10 chars avg(20), say 3 chars avg(13)+100 columns of 8 bytes(80) - total 136 bytes + 16 bytes for the ID index=152 bytes x 1m=approx. 14mb

convert as above and you will have in theory 100m rows of 1 long(8), 1 integer(2), 1 Boolean(2),1 long(8), 1date(8), 1 double(8)=36 + 42 bytes for the indexes for ID, zip, brand =78

x100m= approx. 7gb, BUT, many of your values are zero and do not need to be stored. From the 100 odd rows provided, I would estimate perhaps only 10% contain values - so instead of needing 7gb, you need about 0.7, well within the 2Gb limit of Access.


Also advise remove the # from Rep - because # is used to identify text as a date in SQL, leaving it in can cause problems

good luck with your project