I have 2 tables, Hours and Lines
In hours i can see my Hours i worked on daily base. In Lines i see the lines i processed on weekly base. The Columns are:
hours.date, hours.year, hours.week, hours.hours
lines.year, lines.week, lines.lines
the first thing i want to do is to create an hour list on a weekly base so:
SELECT hours.year, hours.week, Sum(hours.hours) AS [Weekly Hours]
FROM hours
GROUP BY hours.year, hours.week;
Now i see the hours i worked for every week. I want to add the lines for that week.
I tried to make a key out of year&week but it didn't work.
SELECT hours.year, hours.week, Sum(hours.hours) AS [Weekly Hours], lines.lines
FROM hours
LEFT JOIN lines ON hours.year & hours.week = lines.year & lines.week
GROUP BY hours.year, hours.week;
Is it possible to connect this information of different levels (daily weekly)?
"&" is a binary AND operator in SQL, not a concatenation - and in 7cany case you can't concatenate numeric values without first converting them to String. I guess something like Javascript would be fine with that though 
Just make the ON clause into a pair of AND's on the inidivual columns
LEFT JOIN lines
ON hours.year = lines.year
AND hours.week = lines.week
By the by, if you put ANY sort of manipulation in the JOIN statement (e.g. concatenation or function calls) then the query is unlikely to be SARGable. A SARGable query is one where SQL will use indexes if suitable ones available and sensible to be used. So you want to try to have all your queries SARGable if possible. If your database grows "big" then this will become very important because, in the main, SARGable queries wills scale .. but "brute force" ones will run slower and slower as the data volume increases because they will SCAN all rows in the table, rather than using indexes to quickly identify the relevant rows.
Thank you for your quick response, for question i made the colums up because the original is partial in dutch.
Here is what it really looks like:
Maybe you can understand the error even though you don't understand the dutch language
SELECT UurRegKB.datum, UurRegKB.Afdeling, Sum(UurRegKB.Uren) AS [Sum Of Uren], Warehouse.Lines
FROM UurRegKB
LEFT JOIN Warehouse
ON UurRegKB.datum = Warehouse.datum
AND UurRegKB.Afdeling = Warehouse.Department
GROUP BY UurRegKB.Datum, UurRegKB.Afdeling;
I tried this but i get this error:
You tried to execute a query that does not include the specified expression 'Lines' as part of an aggregate function.
You have an AGGREGATE in your SELECT (i.e. COUNT, SUM, MIN, MAX etc.)
You have to include ALL the non-aggregate columns in your GROUP BY
This is your SELECT
SELECT UurRegKB.datum, UurRegKB.Afdeling, Warehouse.Lines,
Sum(UurRegKB.Uren) AS [Sum Of Uren]
but your GROUP BY is:
GROUP BY UurRegKB.Datum, UurRegKB.Afdeling;
so Warehouse.Lines is missing from your GROUP BY
This will give you one row PER Warehouse.Lines in your output - hopefully that is what you want 
Yes! Thanks you are awesome:
Now i have on daily base Date, Department, Hours, Lines
Next question:
i want to calculate the productivity (lines / hours) so this is my code:
SELECT UurRegKB.datum, UurRegKB.Afdeling, Sum(UurRegKB.Uren) AS [Sum Of Uren], Warehouse.Lines, Warehouse.Lines / Sum(UurRegKB.Uren)
FROM UurRegKB LEFT JOIN Warehouse ON (UurRegKB.Afdeling = Warehouse.Department) AND (UurRegKB.datum = Warehouse.datum)
WHERE UurRegKB.datum BETWEEN date() and date() -30
GROUP BY UurRegKB.datum, UurRegKB.Afdeling, Warehouse.Lines;
It works perfect but i have 82000 records in UurRegKB (Hours). Next thing i import the data to excel and make a 30 day graph with it. Is this the fastest way to work with the data or are there faster ways?
I also could calculate the productivity in excel for instance.
That should probably be the other way round?
WHERE UurRegKB.datum BETWEEN date() -30 AND date()
I would aggregate the data as much as possible in SQL - to transfer the MINIMUM number of rows to Excel.
If you don't actually need drill-down of 82,000 records in Excel?? then transfer just the next-level-up summary totals instead.
We don't do it like that here (but I can see that it would work as you describe it).
We use a report writer which runs in a browser. It can display a nice graph, as you describe, but then the user can "click" on something to drill down. The data for the Drill-Down is NOT present, what actually happens for the Drill Down is that parameters are created from what the user clicks on (e.g. a specific Week or User etc.) and then a new SQL query is run to get that data, the data is displayed and then the user can drill-down further on that data, or perhaps instead clicks on something different (in the original Graph) to see a drill down on just that section of data.
This avoids having all 82,000 rows available at any one time. This makes the APP much more scalable - if it was 8,000,000 rows it would not matter - the first query would only return enough (aggregate) rows for the initial graph, then each drill-down would return a small-ish number of rows. Generally the user is only interested in drilling down on things that "look odd" so rarely actually wants ALL the raw data.
Dunno how you would do that with Excel, and maybe not relevant to what you are trying to do, in which case it will just be "food for thought" ...