Report with each week across the page

%3CLINGO-SUB%20id%3D%22lingo-sub-1565057%22%20slang%3D%22en-US%22%3EReport%20with%20each%20week%20across%20the%20page%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565057%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20to%20create%20a%20report%20where%20the%20main%20information%20is%20the%20week%20number.%20I%20have%20a%20table%20in%20which%20each%20week%20number%20is%20associated%20with%20a%20date%20(TBLperiods).%20I%20then%20have%20a%20table%20for%20the%20companies%20(TBLcompanies)%20and%20finally%20I%20have%20a%20table%20for%20the%20hours%20worked%20(TBLhours).%3C%2FP%3E%3CP%3EI%20created%20a%20Query%20(QRYhours)%20where%20TBLcompanies%20is%20linked%20to%20TBLhours%20by%20the%20company%20name%20and%20TBLperiods%20is%20linked%20with%20a%20Code%20as%20I%20have%202%20different%20types%20of%20periods%20(PeriodA%20and%20PeriodB).%3C%2FP%3E%3CP%3EI%20need%20to%20create%20a%20report%20that%20would%20look%20like%20this%3C%2FP%3E%3CTABLE%20width%3D%22333%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EWeek1%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EWeek2%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EWeek3%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EWeek4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EDate%3C%2FTD%3E%3CTD%3EDate%3C%2FTD%3E%3CTD%3EDate%3C%2FTD%3E%3CTD%3EDate%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECOMPANY1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eemployee1%3C%2FTD%3E%3CTD%3E20.00%3C%2FTD%3E%3CTD%3E20.00%3C%2FTD%3E%3CTD%3E20.50%3C%2FTD%3E%3CTD%3E20.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eemployee2%3C%2FTD%3E%3CTD%3E10.50%3C%2FTD%3E%3CTD%3E10.50%3C%2FTD%3E%3CTD%3E10.50%3C%2FTD%3E%3CTD%3E11.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eemployee3%3C%2FTD%3E%3CTD%3E35.00%3C%2FTD%3E%3CTD%3E9.00%3C%2FTD%3E%3CTD%3E35.00%3C%2FTD%3E%3CTD%3E35.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eemployee4%3C%2FTD%3E%3CTD%3E8.25%3C%2FTD%3E%3CTD%3E8.25%3C%2FTD%3E%3CTD%3E8.50%3C%2FTD%3E%3CTD%3E9.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECOMPANY2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eemployee1%3C%2FTD%3E%3CTD%3E30.00%3C%2FTD%3E%3CTD%3E28.50%3C%2FTD%3E%3CTD%3E30.00%3C%2FTD%3E%3CTD%3E30.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eemployee2%3C%2FTD%3E%3CTD%3E7.00%3C%2FTD%3E%3CTD%3E7.00%3C%2FTD%3E%3CTD%3E10.25%3C%2FTD%3E%3CTD%3E7.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eemployee3%3C%2FTD%3E%3CTD%3E4.00%3C%2FTD%3E%3CTD%3E4.00%3C%2FTD%3E%3CTD%3E4.00%3C%2FTD%3E%3CTD%3E8.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eemployee4%3C%2FTD%3E%3CTD%3E15.25%3C%2FTD%3E%3CTD%3E22.00%3C%2FTD%3E%3CTD%3E22.00%3C%2FTD%3E%3CTD%3E22.00%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20really%20not%20sure%20how%20to%20go%20about%20getting%20each%20week%20across%20the%20page%3CBR%20%2F%3EAny%20help%20would%20be%20greatly%20appreciated%3CBR%20%2F%3EJeannie%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1565057%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1565515%22%20slang%3D%22en-US%22%3ERe%3A%20Report%20with%20each%20week%20across%20the%20page%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565515%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239390%22%20target%3D%22_blank%22%3E%40Jeanne%20Cote%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet's%20step%20back%20and%20get%20a%20more%20detailed%20description%20of%20each%20of%20these%20tables.%20You%20can%20either%20provide%20screenshots%20or%20a%20sample%20accdb%20with%20enough%20sample%20data%20to%20see%20how%20it%20needs%20to%20work.%20Remove%20or%20replace%20sensitive%20data%2C%20of%20course.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20probably%20a%20handful%20of%20different%20ways%20to%20get%20to%20a%20usable%20result%2C%20but%20knowing%20the%20actual%20structures%20involved%20makes%20it%20possible%20to%20offer%20one%20of%20them.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1565593%22%20slang%3D%22en-US%22%3ERe%3A%20Report%20with%20each%20week%20across%20the%20page%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565593%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F126074%22%20target%3D%22_blank%22%3E%40George%20Hepworth%3C%2FA%3E%26nbsp%3B-%20Thank%20you%20for%20your%20reply%2C%20the%20DB%20is%20in%20French%20so%20I%20will%20try%20to%20explain%20the%20tables%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETBLcompany%3C%2FP%3E%3CP%3E-%20Company%3A%20name%20of%20the%20company%3C%2FP%3E%3CP%3E-%20Code%3A%20code%20assigned%20for%20the%20pay%20period%20use%20(A%20or%20B)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETBLhours%3C%2FP%3E%3CP%3E-%20Company%3A%20same%20as%20TBLcompany%3C%2FP%3E%3CP%3E-%20Name%3A%20employee%20name%3C%2FP%3E%3CP%3E-%20PaidOn%3A%20date%20of%20the%20period%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETBLperiodA%3C%2FP%3E%3CP%3E-%20Week%3A%26nbsp%3B%20Week%20number%3C%2FP%3E%3CP%3E-%20Period%3A%20Date%20for%20the%20period%3C%2FP%3E%3CP%3E-%20Code%3A%20same%20as%20the%20one%20in%20TBLcompany%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20you%20need%20more%20information%3C%2FP%3E%3CP%3EThank%3C%2FP%3E%3CP%3EJeannie%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1565606%22%20slang%3D%22en-US%22%3ERe%3A%20Report%20with%20each%20week%20across%20the%20page%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565606%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239390%22%20target%3D%22_blank%22%3E%40Jeanne%20Cote%3C%2FA%3E%26nbsp%3BThanks%2C%20I'll%20work%20out%20a%20potential%20query%20that%20provides%20the%20desired%20output%20based%20on%20that%20exact%20structure.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1565653%22%20slang%3D%22en-US%22%3ERe%3A%20Report%20with%20each%20week%20across%20the%20page%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565653%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F126074%22%20target%3D%22_blank%22%3E%40George%20Hepworth%3C%2FA%3E%26nbsp%3BUnfortunately%2C%20that%20doesn't%20seem%20to%20quite%20make%20sense.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%2C%20I%20would%20expect%20there%20to%20be%20a%20table%20of%20employees.%20I%20would%20expect%20that%2C%20instead%20of%20repeating%20the%20employee%20name%20over%20and%20over%20in%20that%20table%2C%20you'd%20use%20the%20Primary%20Key%20from%20that%20other%20employee%20table%20as%20the%20Foreign%20Key%20for%20the%20Hours%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecond%2C%20you%20have%20a%20table%2C%20apparently%2C%20called%26nbsp%3B%3CSPAN%3ETBLperiodA%2C%20and%20a%20field%20in%20that%20same%20table%20called%20%22Code%22%20which%20would%2C%20apparently%2C%20refer%20to%20either%20%22A%22%20or%20%22B%22%3F%20Is%20that%20the%20actual%20structure%3F%20If%20so%2C%20it%20implies%20a%20second%20table%20called%26nbsp%3BTBLperiodB.%20And%20that%20would%20be%20inappropriate.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CBR%20%2F%3ECan%20I%20see%20the%20REAL%2C%20FULL%20tables%2C%20with%20sample%20data%20please%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EMy%20French%20is%20not%20that%20great%2C%20but%20I%20do%20speak%20fluent%20%22Database%22%20and%20I%20can%20make%20more%20sense%20of%20the%20actual%20tables.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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

 Week1Week2Week3Week4
 DateDateDateDate
COMPANY1    
employee120.0020.0020.5020.00
employee210.5010.5010.5011.00
employee335.009.0035.0035.00
employee48.258.258.509.00
     
COMPANY2    
employee130.0028.5030.0030.00
employee27.007.0010.257.00
employee34.004.004.008.00
employee415.2522.0022.0022.00

 

I am really not sure how to go about getting each week across the page
Any help would be greatly appreciated
Jeannie

4 Replies
Highlighted

@Jeanne Cote 

 

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.

Highlighted

@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

Highlighted

@Jeanne Cote Thanks, I'll work out a potential query that provides the desired output based on that exact structure.

Highlighted

@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.