How do I get a report from various same-type tables, in Access?

[Guest]

Hi,

I have an Access Database, where every person in the company has one table.
There are like 45 or 50 different guys, and every table has this Info:
- Date of work
- Name
- Total Hours
- Total Deliveries Made
etc...

Now, I want to use this as a payroll and as a tool to measure the
effectiveness of the workers. I can print reports perfectly for just one
guys, but I want a single report that can give me the info, day by day of
every worker.

I having problems, and I don't know how to do it..

please help

 

[Rick Brandt]

Hi,

I have an Access Database, where every person in the company has one table.
There are like 45 or 50 different guys, and every table has this Info:
- Date of work
- Name
- Total Hours
- Total Deliveries Made
etc...

Now, I want to use this as a payroll and as a tool to measure the
effectiveness of the workers. I can print reports perfectly for just one
guys, but I want a single report that can give me the info, day by day of
every worker.

I having problems, and I don't know how to do it..

You should not have 45 - 50 tables. You should have ONE table that includes a
field (or fields) to identify the worker.

Any time you have multiple tables with identical structure that is a "big red
flag".

 

[Joseph Meehan]

Hi,

I have an Access Database, where every person in the company has one
table.
There are like 45 or 50 different guys, and every table has this Info:
- Date of work
- Name
- Total Hours
- Total Deliveries Made
etc...

I forgot to add one important thing. Everyone does the same thing when
getting started. It is part of the learning process. Don't let it get you
down. Sometimes we forget.

 

[Joseph Meehan]

Hi,

I have an Access Database, where every person in the company has one
table.
There are like 45 or 50 different guys, and every table has this Info:
- Date of work
- Name
- Total Hours
- Total Deliveries Made
etc...

Now, I want to use this as a payroll and as a tool to measure the
effectiveness of the workers. I can print reports perfectly for just one
guys, but I want a single report that can give me the info, day by day of
every worker.

I having problems, and I don't know how to do it..

please help

The question is why do you have all these tables.

It likely should be two tables. One for the names and demographics of
each employee and one for the daily work information. The two should be
linked by an employee ID.

Now that you have that mess, you can use that data and by adding an
employee ID to the existing tables populating it with the correct employee
ID then using an append query add each of those tables to one table. It
might be easier for you to add the Employee name and demographic information
rather than just an ID, combine all that into one table then split that
table to create the proper links.

Good Luck

 

[John Vinson]

Hi,

I have an Access Database, where every person in the company has one table.
There are like 45 or 50 different guys, and every table has this Info:
- Date of work
- Name
- Total Hours
- Total Deliveries Made
etc...

I'm sorry, but this is NOT an appropriate design. It's maybe ok for a
spreadsheet, but this is simply wrong for a relational database.

Instead of fifty tables, you need TWO:

Employees
EmployeeID
LastName
FirstName
<other bio information>

WorkLog
EmployeeID <<< link to Employees
DateOfWork
TotalHours
TotalDeliveriesMade
...

Now, I want to use this as a payroll and as a tool to measure the
effectiveness of the workers. I can print reports perfectly for just one
guys, but I want a single report that can give me the info, day by day of
every worker.

I having problems, and I don't know how to do it..

A Report based on the table structure above will be very much simpler
to construct. You'll need to run fifty Append queries to migrate the
data from your "spreadsheets" into this table (or, using UNION
queries, maybe five Appends based on five UNION queries, with a little
prep work).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps