How to pull together information

[KarenG]

I have a system that collects usage data by month by individual within the company. Each month's data does not necessarily include the same individuals. For example, if there are 500 employees in the company, in January, I may have a listing with 100 employees. In February, I might have 75 employees. There may be individuals who are on the January & February lists, or there may be some that are on only one list. In other words, if there is no data for an individual, they are not on the list
I also have a list of all employees and the team they are on
What I need to be able to do is aggregate the data by the team the employee is on and show team totals for each month. I have been able to do this for any given month, but I can't figure out how to create one report/query/pivottable that shows the totals for all months by team

 

[Les]

Is your usage data by month in a separate table for each
month? If so, you probably should have it all in 1 table,
and have key be individual and month. Once you have this,
you can create a new query. Left join your employees
table to this table. (Gives you all employees and any
time that is in new table for employee).
Then group by team, month, sum on usage. You can
create a new report that uses this query as record source.

Hope this helps.

-----Original Message-----
I have a system that collects usage data by month by

individual within the company. Each month's data does not
necessarily include the same individuals. For example, if
there are 500 employees in the company, in January, I may
have a listing with 100 employees. In February, I might
have 75 employees. There may be individuals who are on the
January & February lists, or there may be some that are on
only one list. In other words, if there is no data for an
individual, they are not on the list.

I also have a list of all employees and the team they are on.
What I need to be able to do is aggregate the data by the

team the employee is on and show team totals for each
month. I have been able to do this for any given month,
but I can't figure out how to create one
report/query/pivottable that shows the totals for all
months by team

 

[KarenG]

yes, my monthly data is currently in separate tables, because I get the data from separate reports. I thought about trying to put them into one table but I'm not sure how to do that from my source data. My source starts in Excel. Each tab of the workbook represents a different month's data. The issue I can't figure out is the fact that each month the people listed in the report may be different. So I can't easily create a spreadsheet such as this:

Name Month #hits

If I simply merge each month's reports in the above format, there will entries where the "Name" field has duplicate data. When I import this spreadsheet in Access, what will the table be like? Is there an easy way to merge together all my source so there isn't any duplication -- without me doing a lot of manual editing?

 

[Les]

Hi,
I'm not too familiar with importing excel data into
access. If you could just merge all your data together,
and pull it into access, it wouldn't matter that there
were duplicate names, as long as the combination of name
and month are unique. That should be the key to your
table. No manual editing would be required. This table
should just have your 3 columns (Name,Month,#hits). The
primary key for the table should be Name + Month.
If you can create this, you can follow my suggestions on
the previous post.
Hope this helps.

-----Original Message-----
yes, my monthly data is currently in separate tables,

because I get the data from separate reports. I thought
about trying to put them into one table but I'm not sure
how to do that from my source data. My source starts in
Excel. Each tab of the workbook represents a different
month's data. The issue I can't figure out is the fact
that each month the people listed in the report may be
different. So I can't easily create a spreadsheet such as
this:

Name Month #hits

If I simply merge each month's reports in the above

format, there will entries where the "Name" field has
duplicate data. When I import this spreadsheet in Access,
what will the table be like? Is there an easy way to merge
together all my source so there isn't any duplication --
without me doing a lot of manual editing?