Reporting Union Query Data

[Jotun]

Morning Everyone,

I'm currently working on a database that will store Employee Names and their Hours Worked for bookkeeping purposes. The form we have composed currently takes information on a Job to Job basis (i.e., 1 sheet per job) but has several text input fields for the various employees who may have worked on that job (i.e., EmployeeNameA, EmployeeNameB, etc.). The problem lies in our report. Since we have to report on an employee basis rather than a job basis our report requires more complex code.

Through browsing several forums and doing a lot of googling, I learned that the best way to report several fields is to run a union query and compile the data. I did, and now all the EmployeeNameA's B's and C's are simply "Employee" under a new query titled "Employee Query".

I've hit a brick wall since I have no idea how to make the report produce one employee's name alphabetically from that query, then subsequently report all his hours worked for each day of a pay-period week; then list the next employee's name, then all his data, etc. I have the dates set up fine, and the employee union query includes the hours worked and everything else that needed to be compiled, but names refuse to show up when I try to DLookup the union query. I may not need to do a Dlookup, I may be way off. I need some help!

Thanks in advance and feel free to ask any questions!

 

[Jotun]

Just some info that may assist - my tbl looks like this:

JobID Date Zone EmployeeNameA TimeInA TimeOutA TotalHoursA as headers to track the employees entered in my "A" fields. Since the time card form requires possible input of six employees, my Table ranges from A to F. (I.E., EmployeeNameB, EmployeeNameC then D E F..., and TimeInA, TimeInB, C D... etc.) I do this for all the headings that have an "A" tag following them.

So the table would look like:

JobID Date Zone EmployeeNameA TimeInA TimeOutA TotalHoursA EmployeeNameB TimeInB TimeOutB TotalHoursB EmployeeNameC TimeInC TimeOutC TotalHoursC EmployeeNameD TimeInD TimeOutD TotalHoursD...all the way to F - it's a long drag across the horizontal.

I realize that may not have been the best way to do it, but its the only thing that made sense to me at the time (second week using access heh.)

Thanks!

 

[PeterF]

Your tables are not normalized and it will be a hell of a job to get reliable data from it. You said you have only worked for two weeks on this database so in my opinion your best solution would be to read up on a normalized database (do a search here) and change your database accordingly.

For a simple solution you need 3 tables:

A project table with a primary key (can be the project number or a auto-number)

A Employee table with a primary key (I would take a auto-number)

A working time table with a foreign key to the project table and a foreign key to the employee table. In the form there is a need for some checking on overtime for the employee and possible duplicate entry's.

The field names you have chosen for your current db contains a reserved word ("date") which can cause some strange results, so wile you're on it check these too.

 

[Jotun]

Thanks I'll give that a shot. Am I on the right track by unionizing all the different source-boxes for employee names? Thats the thing that really thows me... The bookkeeper has to have a minimum of six open spots for a job, even though one job may only have 2 employee's entered. Whats the best way to go about arranging the Employee table with that sort of format?

 

[PeterF]

Thanks I'll give that a shot. Am I on the right track by unionizing all the different source-boxes for employee names? Thats the thing that really thows me... The bookkeeper has to have a minimum of six open spots for a job, even though one job may only have 2 employee's entered. Whats the best way to go about arranging the Employee table with that sort of format?

If you normalize your tables you don't need a union table.
To enter data you can use a form for the project with subform to enter hours, if you make this a continuous form the number of employees on a form is endless.

 

[Jotun]

Thanks for your help Peter, I have a better understanding of 1NF and 2NF by doing a little more research. I wish I had known this from the start, it would have saved much time and many headaches!

I'll get my db normalized and work on rearranging my form so that the data is continuous. I suppose once all thats done, arranging the report won't be a problem at all!

Thank you!