Hopefully I might get the right help here - I’ve tried to solve this for a couple of days now.
I have a tab, that shows 5 users, and their working hours pr. week (they fill a form). I want the data from the hours they have worked pr. user, to show in a another tab - but summed up. Does that make sence? I’ll try to illustrate:
As you can see, I have tried different ways to get the hours in the Users tab, but without any luck. When I try to make a rollup from a relation, it doesn’t sum up the hours, but the numbers of users instead - or it changes the hours to a number (0,01258 etc.).
Can somebody help me?
Best regards, Claudia (Apologies for my bad english, I’m from DK).
So… each row in that table represents the data for one user, for one week. With separate columns for in/out for each day of the week.
Is that correct?
Once you’ve summarised the data, do you have a clear picture in your mind of how it would look on the screen? Can you provide a mockup that shows how you would want it to appear?
ps. @nathanaelb this topic is relevant to our discussion yesterday, I think. A clear violation of 1NF (repeating group of data items)
Once one realizes the data structure isn’t the best, does one continue with what they have, or does one correct the original architecture?
In Claudia’s case, would she create a new Days Of The Week Worked table and use a user-specific column to store hours worked? I think I just realized writing this that USC’s are a visual trickery, I need to ponder this one.
When I create tables, I will now have to practice checking if they follow the NF rules, and adapting the database architecture if they don’t. This is going to take a while to get used to.
I would have a question: how do you plan to scale gathering the data? In other words, if you plan to gather hours worked for this week, next week, the week after and the next 500 weeks, how will this work in the tables in your current setup? If you feel like it doesn’t scale by design, then this might be a good indication that the structure of your tables could be improved (as Darren seems to be suggesting, though there might be a way around it).
I don’t think we have enough information to say just yet. I have some ideas about how I would structure it, but Claudia’s requirements might be different to how I imagine them. Hence it would be good to get answers to both your question and mine.
Correct, each row represents the data for one user, for one week - with seperate columns for each day.
The goal is to sum up the data, given each week, making it easy for the user to see their hours (if they have to many or not enough). They use it to plan the next week.
If each user (having their own row) can get a total amount for every week, I would gladly make a column for each week - but I just can’t seem to get the information from one tab to the other like that.
I would gladly make a column pr. week for a year, to get the information on the tab, where the users are displayed - but it seems to be difficult to get the hours from one tab, summed up in another tab.
It actually does this at the moment, for every row. It sums up the weekly hours just fine - but if Louise has row 1 and row 4, I need to sum up all the rows for her, in one column in another tab. It is this task that kills me, it just simply won’t work.
You can do this in your User Profile table. Create a multiple relation that matches the user name with the user name in your table where the hours are logged. Then add a rollup column that takes a sum of total hours via the relation column.
Actually they didn’t point to the same column - I’ve made that change now, but now it turns the hours into zero and digits? Is it because it can’t understand hours, and tries to make it to digits? It’s useless like that
But when you do a Rollup->Sum through that on the “Flex pr. uge” column, you get zero for all users, right?
So we need to take a closer look at that “Flex pr. uge” column. Can you show me what it looks like in the Data Editor, as well as how it is configured please?
