combining temp & direct emp

  • Thread starter Thread starter Mandy
  • Start date Start date
M

Mandy

I have a database with 2 different tables. I have one
for direct employees and another for temp employees. I
want to be able to combine them into 1 list and sort them
by shift. I want to be able to create 2 different
reports once they are combined. The first being time
card labels and the second being a report with just their
names grouped under their shift so that we can check off
who is here and who isn't and there will be a line next
to it to hand write in what dept they are in that day.
The dept changes daily, so that's why we aren't tracking
which dept they are in daily, but just how many we have
in each dept. That goes into the production database.
 
First of all, you should have all of these record on only 1 table. Add a
field to differentiate Direct from Temp employees and you're all done.

Second, to use the tables you have, you have to use a UNION query to put the
tables together. You can't use the Query Builder for that, you'll have to
type it in to an SQL window. Something like this:

SELECT * FROM DirectEmp
UNION ALL
SELECT * FROM TempEmp

Union queries must have the same number of columns, so if the two files have
different number of columns or they're in a different order, you'll have to
specify the fields in the field list:

SELECT EmpID, LastName, FirstName, HireDate FROM DirectEmp
UNION ALL
SELECT EmployeeID, [Last Name], [First Name], [Hire Date] FROM TempEmp

As you can see, the column names do not have to be the same.
 
Roger,

Thanks. I'll try the union query in SQL. I would put
them into 1 table, but the ID# is a required field that
must be different and it is generated from our payroll
company.

Mandy
-----Original Message-----
First of all, you should have all of these record on only 1 table. Add a
field to differentiate Direct from Temp employees and you're all done.

Second, to use the tables you have, you have to use a UNION query to put the
tables together. You can't use the Query Builder for that, you'll have to
type it in to an SQL window. Something like this:

SELECT * FROM DirectEmp
UNION ALL
SELECT * FROM TempEmp

Union queries must have the same number of columns, so if the two files have
different number of columns or they're in a different order, you'll have to
specify the fields in the field list:

SELECT EmpID, LastName, FirstName, HireDate FROM DirectEmp
UNION ALL
SELECT EmployeeID, [Last Name], [First Name], [Hire Date] FROM TempEmp

As you can see, the column names do not have to be the same.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org


Mandy said:
I have a database with 2 different tables. I have one
for direct employees and another for temp employees. I
want to be able to combine them into 1 list and sort them
by shift. I want to be able to create 2 different
reports once they are combined. The first being time
card labels and the second being a report with just their
names grouped under their shift so that we can check off
who is here and who isn't and there will be a line next
to it to hand write in what dept they are in that day.
The dept changes daily, so that's why we aren't tracking
which dept they are in daily, but just how many we have
in each dept. That goes into the production database.
Click to expand...


.
Click to expand...
 
Roger,

Ideas have changed. They want the report to have one of
these options:

1. direct employees listed and then temp employees

or

2. direct employees listed next to temp employees.

I have been working on the 2nd way. I put 2 subreports
into the detail section and put them side by side. The
problem there is that it lists 1 direct employee and then
all the temp employees side by side and then for the next
direct employee it lists next to it all the temp
employees again. How can I get it to list all the direct
and all the temp next to each other in seperate columns.
These will vary in length due to the ever changing amount
of employees.

They would really prefer the first way though.

Mandy
-----Original Message-----
Roger,

Thanks. I'll try the union query in SQL. I would put
them into 1 table, but the ID# is a required field that
must be different and it is generated from our payroll
company.

Mandy
-----Original Message-----
First of all, you should have all of these record on only 1 table. Add a
field to differentiate Direct from Temp employees and you're all done.

Second, to use the tables you have, you have to use a UNION query to put the
tables together. You can't use the Query Builder for that, you'll have to
type it in to an SQL window. Something like this:

SELECT * FROM DirectEmp
UNION ALL
SELECT * FROM TempEmp

Union queries must have the same number of columns, so if the two files have
different number of columns or they're in a different order, you'll have to
specify the fields in the field list:

SELECT EmpID, LastName, FirstName, HireDate FROM DirectEmp
UNION ALL
SELECT EmployeeID, [Last Name], [First Name], [Hire Date] FROM TempEmp

As you can see, the column names do not have to be the same.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org


Mandy said:
I have a database with 2 different tables. I have one
for direct employees and another for temp employees. I
want to be able to combine them into 1 list and sort them
by shift. I want to be able to create 2 different
reports once they are combined. The first being time
card labels and the second being a report with just their
names grouped under their shift so that we can check off
who is here and who isn't and there will be a line next
to it to hand write in what dept they are in that day.
The dept changes daily, so that's why we aren't tracking
which dept they are in daily, but just how many we have
in each dept. That goes into the production database.
Click to expand...


.
Click to expand...
.
Click to expand...
 
It sounds like your mainform has a Record Source of the Direct Employees
table and so does one of your subforms. Try removing the Record Source from
the mainform (unless there is something I'm missing, you don't need it)
also, remove the Link Parent./ Link Child properties of the subreport.

As for solution 1, that should be even easier. You can add fields to your
query that do not exist in the table. I would add one that will allow you
to order the employees. Something like this:

SELECT EmpID, LastName, FirstName, HireDate,
"1" As EmpOrder FROM DirectEmp
UNION ALL
SELECT EmployeeID, [Last Name], [First Name],
[Hire Date],"2" As EmpOrder FROM TempEmp

This will add a 1 to every direct employee record and a 2 to every temp.
Now in your report, you can use this field to put all the direct employees
first.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Mandy said:
Roger,

Ideas have changed. They want the report to have one of
these options:

1. direct employees listed and then temp employees

or

2. direct employees listed next to temp employees.

I have been working on the 2nd way. I put 2 subreports
into the detail section and put them side by side. The
problem there is that it lists 1 direct employee and then
all the temp employees side by side and then for the next
direct employee it lists next to it all the temp
employees again. How can I get it to list all the direct
and all the temp next to each other in seperate columns.
These will vary in length due to the ever changing amount
of employees.

They would really prefer the first way though.

Mandy
-----Original Message-----
Roger,

Thanks. I'll try the union query in SQL. I would put
them into 1 table, but the ID# is a required field that
must be different and it is generated from our payroll
company.

Mandy
-----Original Message-----
First of all, you should have all of these record on only 1 table. Add a
field to differentiate Direct from Temp employees and you're all done.

Second, to use the tables you have, you have to use a UNION query to put the
tables together. You can't use the Query Builder for that, you'll have to
type it in to an SQL window. Something like this:

SELECT * FROM DirectEmp
UNION ALL
SELECT * FROM TempEmp

Union queries must have the same number of columns, so if the two files have
different number of columns or they're in a different order, you'll have to
specify the fields in the field list:

SELECT EmpID, LastName, FirstName, HireDate FROM DirectEmp
UNION ALL
SELECT EmployeeID, [Last Name], [First Name], [Hire Date] FROM TempEmp

As you can see, the column names do not have to be the same.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org


I have a database with 2 different tables. I have one
for direct employees and another for temp employees. I
want to be able to combine them into 1 list and sort them
by shift. I want to be able to create 2 different
reports once they are combined. The first being time
card labels and the second being a report with just their
names grouped under their shift so that we can check off
who is here and who isn't and there will be a line next
to it to hand write in what dept they are in that day.
The dept changes daily, so that's why we aren't tracking
which dept they are in daily, but just how many we have
in each dept. That goes into the production database.


.
Click to expand...
.
Click to expand...
Click to expand...
 
Roger,

Thank you oh so much. This worked perfectly and they are
so happy with it. Thanks again.

Mandy
 
Back
Top