Aug 04 2020 05:41 AM
I have to create a report where the main information is the week number. I have a table in which each week number is associated with a date (TBLperiods). I then have a table for the companies (TBLcompanies) and finally I have a table for the hours worked (TBLhours).
I created a Query (QRYhours) where TBLcompanies is linked to TBLhours by the company name and TBLperiods is linked with a Code as I have 2 different types of periods (PeriodA and PeriodB).
I need to create a report that would look like this
Week1 | Week2 | Week3 | Week4 | |
Date | Date | Date | Date | |
COMPANY1 | ||||
employee1 | 20.00 | 20.00 | 20.50 | 20.00 |
employee2 | 10.50 | 10.50 | 10.50 | 11.00 |
employee3 | 35.00 | 9.00 | 35.00 | 35.00 |
employee4 | 8.25 | 8.25 | 8.50 | 9.00 |
COMPANY2 | ||||
employee1 | 30.00 | 28.50 | 30.00 | 30.00 |
employee2 | 7.00 | 7.00 | 10.25 | 7.00 |
employee3 | 4.00 | 4.00 | 4.00 | 8.00 |
employee4 | 15.25 | 22.00 | 22.00 | 22.00 |
I am really not sure how to go about getting each week across the page
Any help would be greatly appreciated
Jeannie
Aug 04 2020 09:40 AM
Let's step back and get a more detailed description of each of these tables. You can either provide screenshots or a sample accdb with enough sample data to see how it needs to work. Remove or replace sensitive data, of course.
There are probably a handful of different ways to get to a usable result, but knowing the actual structures involved makes it possible to offer one of them.
Aug 04 2020 10:20 AM
@George Hepworth - Thank you for your reply, the DB is in French so I will try to explain the tables
TBLcompany
- Company: name of the company
- Code: code assigned for the pay period use (A or B)
TBLhours
- Company: same as TBLcompany
- Name: employee name
- PaidOn: date of the period
TBLperiodA
- Week: Week number
- Period: Date for the period
- Code: same as the one in TBLcompany
Let me know if you need more information
Thank
Jeannie
Aug 04 2020 10:29 AM
@Jeanne Cote Thanks, I'll work out a potential query that provides the desired output based on that exact structure.
Aug 04 2020 10:53 AM
@George Hepworth Unfortunately, that doesn't seem to quite make sense.
First, I would expect there to be a table of employees. I would expect that, instead of repeating the employee name over and over in that table, you'd use the Primary Key from that other employee table as the Foreign Key for the Hours table.
Second, you have a table, apparently, called TBLperiodA, and a field in that same table called "Code" which would, apparently, refer to either "A" or "B"? Is that the actual structure? If so, it implies a second table called TBLperiodB. And that would be inappropriate.
Can I see the REAL, FULL tables, with sample data please?
My French is not that great, but I do speak fluent "Database" and I can make more sense of the actual tables.
Thanks.