Need to allocate annual sales by month and work day

[Randy]

on the surface this seems easy, but I am struggling. two tables
tbldata (four fields 6,270 rows)
custid 2015cellcode 2015qty 2015amt
12673 1243 100 1,000.00
12673 1250 200 2,000.00
etc etc

tblmntlyalloc (Three fields and 12 rows i.e. one row for each month)
2015mnth, 2015allocation, 2015wrkdays
jan 98 20
feb 93 19
etc etc etc

so in order to handle seasonality of sales, the sales department is given 1,200 points. you can then allocation any number of points (75 - 125) per month. But the total number of points must equal 1,200. So I created a table with the monthly allocation and workdays. I could hard code the % and work days into the formula like this
Jan1[2015amt]*(98/1200)) / 20
Feb1[2015amt]*(93/1200)) / 19

and this does work very well to get me my daily sales amount per month/wrkday

however I know sales will change the monthly allocation (still totals 1200) and HR could even have a change to the holiday schedule. So I want to utilize the table.

so my query brings in both tables, but they are not linked. And this is my formula.

Jan1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
Feb1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
Mar1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
Apr1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
etc etc for 12 months


so I need it to find the value for Jan in the Jan formula, and Feb in the feb formula etc. For some reason my brain is not working today and I cannot determine the correct query formula.

 

[plog]

on the surface this seems easy,

Not to me, mostly because you've inconsistently described your data between your explanation and your screen shot. Your table and field names don't jive between the two, so I have no idea how the two relate.

I might be able to help you, but what I will need is 3 sets of sample data:

A. Sample data from the first table (including table and field names).

B. Sample data from the second table (including table and field names).

C. The results you expect based on A & B.

 

[Randy]

sorry you are correct, I wrote this question, then tried something and forgot I changed the field names and then just copied paste the question in. give me a minute and I will update.
the screen shot is the updated information, but hold on

 

[Randy]

tbldata:
custid
cellcode
2015qty
2015forecast

tblsalesallcoation
mnthallocation
mnthworkdays
mnth

 

[Randy]

example for tbldata
769 1234 100 1,000.00
769 1235 200 2,000.00
769 1236 300 3,000.00

 

[Randy]

example for tblsalesallocation
93 20 jan
95 20 feb
100 22 mar
98 21 apr
102 20 may

the total of field mnthallocation will ALWAYS total 1,200. So sales manager could change Jan to 98, but then some other month(s) would be lowered by 3 points. Regardless the divider is always 1,200

 

[Randy]

so for query output

custid cellcode 2015qty 2015forecast jan feb mar apr may
769 1234 100 1,000 3.875 3.958 3.788 3.889 4.25

is calculated as follows (where the number in BLUE are pulled from tbldata, and the numbers in RED are pulled from tblsalesallocation)
Jan (1,000 * (93/1200)) / 20 = 3.875
Feb (1,000 * (95/1200)) / 20 = 3.958
Mar (1,000 * (100/1200)) / 22 = 3.788
Apr (1,000 * (98/1200)) / 21 = 3.889
May (1,000 * (102/1200)) / 20 = 4.25

 

[plog]

This code will generate the data you want:

TRANSFORM Max(([2015forecast]*([mnthallocation]/1200))/[monthworkdays]) AS MonthTotal
SELECT tbldata.custid, tbldata.cellcode, tbldata.[2015qty], tbldata.[2015forecast]
FROM tbldata, tblsalesallocation
GROUP BY tbldata.custid, tbldata.cellcode, tbldata.[2015qty], tbldata.[2015forecast]
PIVOT tblsalesallocation.mnth;

You've also got some issues with your table that I would fix:

1. You shouldn't store relevant data in table or field names. That '2015' shouldn't be in a field name, but stored in a column. This way you can throw in multiple years of data into the same table.

2. You shouldn't store months as 3 character codes. My recomendation would be to store them as a date field and then for every month, store the first date of that month (mar = 3/1/2015, dec = 12/1/2015). Doing this allows you to store multiple years of data in the same table (like my prior suggestion) and allows you to use native Date functions (http://www.techonthenet.com/access/functions/) on them. Also, it will allow you to sort and order the dates properly. Using 3 characters, the only way to sort your dates is like this:

apr
aug
feb
...

IF you use a date type for the field, you can then have Access sort them in the proper order (jan, feb, mar...)

 

[Randy]

all good points thanks. I was just hired 2 months ago. All of this was prior work I am just starting to clean up. I had a quick deadline so I am using what was there, and then updating the processes for 2015. I am sure I will be asking more questions later. Many thanks, I had about 2 lines of the code correct I am going to have to read up on Transform.