MS ACCESS Cross table DATA

Question

I have two tables, one with employee details looks like this

Employee ID | Employee Name | Start Date | Termination Date |

2234        |  John Smith  | 2014-01-03  |                  |   

3333        | Jane Doe     | 2014-04-22  |  2014-10-31      |

1234        | Bobby Wilson | 2013-12-10  |                  |

My second table has there sales by month-year

looks like so

Employee ID | 2013-12 |   2014-01   | 2014-02  | 2014-03  |  2014-04 | etc | etc 

2234        |         |   199.99    |  130.00  |  300.00  |  230.99  | etc | etc

3333        |         |             |          |          |  204.02  | etc | etc

1234        |  455.99 |   332.32    |  334.00  |  553.00  |  334.99  | etc | etc

So what i need is to make a new Query that helps me show the trend of the employee on how their first month employeed sales number looks like, second month sales, third moneth sales etc. based on when the month one being when the employee started. Over a spand of 12 month or more

So the new table would look as follows

Employee    |  Month 1  |  Month 2  |  Month 3  | Month 4  | etc |  etc |  

2234        |   199.99  |  130.00   |  300.00   |  230.99  | etc |  etc |

3333        |  240.02  |  (month 2) |  (month 3)| (month 4)| etc |  etc |       

1234        |  455.99  |  332.32    |   334.00  |  553.00  | etc |  etc |

So I know we can join the two tables by Employee ID and I know we can determine an employee's month one based on their start date but how do you write this query out in SQL?

This is where poor table design gets you into trouble. I can theorhetically see where in VBA you can strip the day off the starting date and then use the Year-Month as a pointer, but I don't think you can do that in straight SQL. Since the Year-Month is different for everyone, there's no way to join one table to the other and display the data the way you want it to. — Johnny Bones, Jan 15 '15 at 15:56
Does this question differs from it: http://stackoverflow.com/questions/27951872/ms-access-database-broken-into-month ? — Maciej Los, Jan 15 '15 at 16:03
well you can strip out day using FORMAT in SQL and we strip it into month that's not difficult. But figuring how to check every single employees month one against their start date is forigen to me — Sao Tith, Jan 15 '15 at 16:05
@MaciejLos it's actually the second part of that first question i asked yesterday — Sao Tith, Jan 15 '15 at 16:06
Is there a way to check month one against their first month sale? even if we strip it to just yyyy/mm ? — Sao Tith, Jan 15 '15 at 16:22
At the first look, your second table design is wrong. Is it possible to change it this way: Sales(EmpId, SalesDate, Sale)? SalesDate should be the first day of each month. — Maciej Los, Jan 15 '15 at 16:36
By The Way: Access does not supports functionality as MS SQL Server does (ROW_NUMBER() function). So, it's bit complicated to "convert" StartDates and SalesMonths to corresponding months collection: {MONTH1, MONTH2, etc.}. — Maciej Los, Jan 15 '15 at 16:45
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/68897/discussion-between-maciej-los-and-sao-tith). — Maciej Los, Jan 15 '15 at 16:59

score 2 · Accepted Answer · answered Jan 15 '15 at 17:48

As i mentioned in my comment to the question, you need to change the design of your second table as follow (pseudo-code):

CREATE TABLE Sales(
    SaleId Autonumber PK
    [Employee ID] FK (reference to Employees table)
    SalesDate DateTime
    Sales Decimal/Double
);

Then you'll be able to save your data this way:

SaleID  EmpId   SalesDate   Sales
1       2234    2014-01-01  199,99
2       1234    2013-12-01  455.99
3       1234    2014-01-01  332.32
4       2234    2014-02-01  130
5       1234    2014-02-01  334
6       2234    2014-03-01  300
7       1234    2014-03-01  553
8       2234    2014-04-01  230.99
9       3333    2014-04-01  204.02
10      1234    2014-04-01  334.99

Finally, your pivot table might look like:

TRANSFORM Sum(S.Sales) AS SumOfSales
SELECT E.[Employee Id], E.[Employee Name]
FROM Employees AS E INNER JOIN SalesByMY AS S
     ON E.[Employee Id] = S.[Employee Id]
GROUP BY E.[Employee Id], E.[Employee Name]
PIVOT 'Month-' & DateDiff('m',[E].[StartDate],[S].[SalesDate])+1;

The result:

EmpId   EmpName        Month-1  Month-2 Month-3 Month-4 Month-5
1234    Bobby Wilson   455.99   332.32  334     553     334.99
2234    John Smith     199.99   130     300     230.99  
3333    Jane Doe       204.02

You need to find out why it happens. I tested this solution on real data, as you see in my example. — Maciej Los, Jan 15 '15 at 19:25
Nevermind this worked really well for my application, it's my data that is messsed up (i.e some one change the start date of an employee and yet they still had sales before their start date) — Sao Tith, Jan 15 '15 at 19:46
On the note above, is there away to ignore any sales number if it is before their start date? — Sao Tith, Jan 15 '15 at 20:50
WHERE E.StartDate <= S.SalesDate worked ! thanks again @MaciejLos — Sao Tith, Jan 15 '15 at 21:29

MS ACCESS Cross table DATA

1 Answers1