Linking Question

[Petersona01]

Hello,




I'm new to access and need to use it for my job. I think it's a great tool, but I'm lost right now. I just got this job and I really want to impress my manager, I hope you can help me. I have a project were I need to take 8 years worth of sales data and give my manager a table with the data. He wants to review all of his sales reps from 2011 and compare them throughout the years (we only have data since 2003). The data is for every sales person in the company and managing their sales goals and commission totals measured by 4 quarters in a year. This info was made in 8 different excel spread sheets. For 2011 there are about 1300 sales people, so to go through it manually, it'll be over 9000 key strokes....I think there's an easier and faster way. I tried to play around with access. I imported all 8 excel sheets and tried to link all 8 years, but it is not working too well for me. I linked 2011 info with all 8 years separately by linking their sales ID. I started out with approximately 1300 sales people for 2011 and ended up with just 230 sales people when I completed all 8 years of linkage. I think its taking people who have been with the company since 2003-2011, and not accounting for new hires after 2003. I essentially would like to see people who are still with the company (2011) and track all of their sales throughout the years. Some people might only have 1 year with the company; some might have been with the company for all 8 years of data we have, of something in between. I just need t o combine all 8 years into one spreadsheet. Is this possible, Can someone please help me? Thank you.

[June7]

Start with the 'sticky' thread tutorials found at http://forums.aspfree.com/microsoft-access-help-18/

Then post your data schema and/or the actual project for analysis.

[obiron]

Assuming the 8 spreadsheets are all in the same format, you can use the UNION operator to combine them into a single query

SELECT SalesmanID,YEAR,MONTH,SALES,Comission FROM Excel001

UNION

SELECT SalesmanID,YEAR,MONTH,SALES,Comission FROM Excel002

UNION

...



This should then allow you to report on all salesmen irrespective of their start dates. you may also want to run the UNION query as a MAKETABLE query so that the data is permanently joined.

You need to read up on database normalisation 1st, 2nd and 3rd normal form. Getting your data into 3NF will help greatly in adding functionality to the process.