match weekly time periods to monthly invoice period

[smg]

I have two tables, one with weekly timesheet periods and one with monthly invoice periods, example:

Time Periods
Period From Period To
08/24/2019 08/30/2019
08/31/2019 09/6/20109
09/7/2019 09/13/2019


09/14/2019 09/20/2019
09/21/2019 09/27/2019

Invoice Periods
Invoice Begin Invoice End Invoice Month
08/24/2019 09/27/2019 Oct-19

I need to add the invoice month column to the Time Periods Column with an append query but not sure how to write the formula to accomplish this and format it as mmm-yy

[Gicu]

Have a look at the attached file. I believe you actually need an update query (qryUpdateInvoiceMonthInWeeklyPeriod) to update the invoice month in the weekly periods table.

Cheers,

[ssanfu]

Is this a one time thing?

What is the TimeSheet table structure?
What is the data type of the field "Invoice Month" (shouldn't have spaces in names) in the table "monthly invoice"?


So the results should look like:
Time Periods

Period_From	Period_To	Invoice_Month
08/24/2019	08/30/2019	Oct-19
08/31/2019	09/6/2019	Oct-19
09/7/2019	09/13/2019	Oct-19
09/14/2019	09/20/2019	Oct-19
09/21/2019	09/27/2019	Oct-19

I would use an update query and VBA to loop through the "monthly invoice" table. You could have a starting invoice M/Y and an ending invoice M/Y.
Could you post a copy of your dB with the two tables?

[Bullschmidt]

Also there is the complication that in general a month is about 4.3 weeks so would need to decide each time if the "month" has 4 weeks or 5. Personally I would just input the time periods table by hand - doesn't seems like that much work - about 64 entries per year (52 weeks + 12 "months")...