I have several tables that are formatted the same. Each table has the fields,
Work Center, Cost Center, Week Beginning, Monday, Tuesday, Wednesday,
Thursday, Friday, Saturday & Sunday. I have seperate tables for each work
center supervisor to use to input tem labor hours...how can I combine all of
these tables to create one query or report that will show me each work
centers hours by day?
Annette, I realize you may not like the answers you've been getting in
the other thread where this was posted... but those answers are
exactly correct. Your database structure is WRONG, twice over: storing
data in tablenames (supervisor tables) and storing data in fieldnames
(day names).
If the supervisors should see only their own data, it is NOT necessary
to have a separate table for each supervisor. Instead, you should have
a SupervisorID field in the (single) timesheet table; the supervisors
would be able to see a Query using their ID to see their own data. If
there are security issues (i.e. if supervisor Jane should not be
allowed to see supervisor Joe's data or vice versa), use Access
security to lock the tables, and use RWOP (Run With Owner's
Permission) queries to let each see only their own data.
A table with fields
TimesheetID Autonumber Primary Key
WorkDate Date/Time
SupervisorID Long Integer (link to Supervisors)
LaborHours Number (Double)
will let you do what you need to do.
That said... since you seem to be rejecting the option of designing
the database correctly due to bureaucratic interference, for which you
have my sympathy... you can use a UNION query
SELECT [Work Center], [Cost Center], [Week Beginning], [Monday],
[Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday]
FROM TableA
UNION ALL
SELECT [Work Center], [Cost Center], [Week Beginning], [Monday],
[Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday]
FROM TableB
UNION ALL
SELECT [Work Center], [Cost Center], [Week Beginning], [Monday],
[Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday]
FROM TableC
<etc>
using your own tablenames of course. You'll need to build this query
in the SQL window, the query grid won't work. Save the query as
uniALlHours, and then base your Totals queries on uniAllHours. Don't
expect lightning fast performance, but it will work.
John W. Vinson[MVP]
