checking import values

[Darla]

So I really have no idea how to accomplish this and I am hoping for a little bit of direction. I have a list of employees and their hours worked and what project they that code their 40 hours to. I import all that from a single CSV into "TableImport" every week and do my thing with it. I also have an employee table with all their info, and a projects table that lists all the projects we have going on. A new thing is that about 50 of these employees now need to split their hours between 3 projects each week (lets call them ProjectA ProjectB and ProjectC) at 50% to ProjectA, 30% to ProjectB and 20% to ProjectC. I have set a flag on these employees in the employee table, but I need to add some sort of check to my import so that when it finds those employees that are flagged, it checks to make sure that the hours that are imported are split up at that 50/30/20




What is the best way to do this? I am so lost, thank you for any help!

[ranman256]

import the data to a 'staging' table.
run your analysis, breakups, etc, here, via update queries, etc...

youd have people pre-set and % of the projects. Apply this to the record.

when all looks good, run an append query to put the data into the final tables.

[CJ_London]

don't know how you are importing at the moment, but to do what you need do the following

1. either link to the csv file or import to a temporary table
2. use append queries to update your records from this linked/temp table

you haven't explained your table structure, if your data is properly structured you should only need one append query, if not you may need one for 'non project' people and one each for project.

Since this is a new development you may want to consider some 'what ifs'

what if there is a new projectD?
what if in the future the requirement is that people are split across projects differently - person1 split 50/30/20, person2 split 50/50/0 etc
what if the splits can vary week by week for some reason - shutdowns/bank holidays

etc

[Darla]

There won't ever be new projects, it will always be A/B/C and the splits will never change for any reason. I guess I just don't understand how to calculate the split and check for it. The data is already imported to a temporary table. Say Jane Doe is one of the employees who is flagged with a split and she worked 42 hours last week. When the data is imported, her portion looks like the example below. I need to understand how to automatically check that her hours for the week equal out to 21/8.4/12.6, which is 50/20/30 of her 42 hours. And if it equals out, perfect, if it doesn't, then a flag or something comes up with Jane's name so the HR person can go back to Jane and have her re-enter her hours properly. I hope that makes sense?

Project	EnployeeName	ProjectHours	TotalHours
ProjectA	Doe, Jane	21	42
ProjectB	Doe, Jane	8.4	42
ProjectC	Doe, Jane	12.6	42

[CJ_London]

create a group by query to group by employee and totalhours and sum projecthours

then a second query, based on the first to subtract the summed projecthours from the grouped totalhours - and to flag, include a criteria to only return those rows where the subtraction <>0

since you have decimals, ensure your values are properly rounded in the first query

[Darla]

create a group by query to group by employee and totalhours and sum projecthours

then a second query, based on the first to subtract the summed projecthours from the grouped totalhours - and to flag, include a criteria to only return those rows where the subtraction <>0

since you have decimals, ensure your values are properly rounded in the first query

This sounds like it will work perfectly, I will try it and let you know, thank you!