Multiple Appends (1 Viewer)

veshand

New member
Local time
Today, 09:49
Joined
Jun 10, 2018
Messages
9
OK Guys! this one is challenging! :D


We've got three connected tables:
1. tblEmployees (Fields: ID, EmployeeName)
2. tblDays (Fields: ID, EmployeeID, Date)
3. tblTimesheet (Fields: ID, DateID, Project, Hour)

these tables are one-to-many in regard with the next one. In other words, for each employee, there are many Days, and for each Day, there are many Timesheet records.

There is a whole another separate table:
1. tblTimesheetGroup (Fields: ID, EmployeeID, FirstDayofMonth, TotalHours)

the operator should enter the EmployeeID, FirstDayofMonth and Totalhours in the separate table and access shall add N records (N being the quantity of days per each month) in tblDays for each nominated EmployeeID. Subsequently, access shall divide the value of TotalHour in tblTimesheetGroup by N, create a single record in tblTimesheet for the attributing record of tblDays, and put the divided value in Hour field!

Now here is the problem:
I need an Append query which adds a variable number of records (N being equal to 30 or 31 based on month which is selected as FirstDayofMonth) to a table (tblDays), and this append query must allocate sequential dates (field Date in tblDays from FirstDayOfMonth to FirstDayOfMonth+N).

I know that a loop might be handy, but not sure how to code it, any ideas?!
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:49
Joined
Sep 21, 2011
Messages
14,044
I did this with a technique that arnelgp posted.
Create a table for days, (1-31), one for months (1-12) and another for years.

Create a form with start and end date.

Then a button to run a query that is

Code:
INSERT INTO tblDates ( DayDate )
SELECT DateSerial([YearNo],[MonthNo],[DayNo]) AS Dates
FROM tblDay, tblMonth, tblYear
WHERE (((DateSerial([YearNo],[MonthNo],[DayNo])) Between [Forms]![frmGenerateDates]![txtStartdate] And [Forms]![frmGenerateDates]![txtEnddate]) AND ((IsDate([MonthNo] & "/" & [DayNo] & "/" & [YearNo]))<>False))
ORDER BY DateSerial([YearNo],[MonthNo],[DayNo]);


HTH

OK Guys! this one is challenging! :D


We've got three connected tables:
1. tblEmployees (Fields: ID, EmployeeName)
2. tblDays (Fields: ID, EmployeeID, Date)
3. tblTimesheet (Fields: ID, DateID, Project, Hour)

these tables are one-to-many in regard with the next one. In other words, for each employee, there are many Days, and for each Day, there are many Timesheet records.

There is a whole another separate table:
1. tblTimesheetGroup (Fields: ID, EmployeeID, FirstDayofMonth, TotalHours)

the operator should enter the EmployeeID, FirstDayofMonth and Totalhours in the separate table and access shall add N records (N being the quantity of days per each month) in tblDays for each nominated EmployeeID. Subsequently, access shall divide the value of TotalHour in tblTimesheetGroup by N, create a single record in tblTimesheet for the attributing record of tblDays, and put the divided value in Hour field!

Now here is the problem:
I need an Append query which adds a variable number of records (N being equal to 30 or 31 based on month which is selected as FirstDayofMonth) to a table (tblDays), and this append query must allocate sequential dates (field Date in tblDays from FirstDayOfMonth to FirstDayOfMonth+N).

I know that a loop might be handy, but not sure how to code it, any ideas?!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:49
Joined
Feb 19, 2013
Messages
16,553
no need to create a table of dates - you can do it in a query.

create a table which contains one numeric field and populate 10 records with the values 0 to 9

in this example the table is called admCounter and the field ctr

Code:
SELECT DateAdd("d",([tens].[ctr]*10)+[ones].[ctr],Date()-Day(Date())) AS myDate
FROM admCounter AS tens, admCounter AS ones
WHERE (((DateAdd("d",([tens].[ctr]*10)+[ones].[ctr],Date()-Day(Date()))) Between Date()-Day(Date())+1 And DateAdd("m",1,Date())-Day(Date())))
ORDER BY DateAdd("d",([tens].[ctr]*10)+[ones].[ctr],Date()-Day(Date()));

Save the query to be used whenever you need - then left join (if required) to your other tables

you can change the BETWEEN statement to reference your form selection if required
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:49
Joined
May 21, 2018
Messages
8,463
If you want a code solution, you can pass the user ID, month, and year. You could wrap this in another procedure that would then pass all empID and months.

Code:
Public Sub InsertDays(EMP_ID As Long, TheMonth As Integer, TheYear As Integer)
  Dim strSql As String
  Dim startDate As Date
  Dim endDate As Date
  Dim tempDate As Date
  Dim i As Integer
  startDate = DateSerial(TheYear, TheMonth, 1)
  endDate = DateSerial(TheYear, TheMonth + 1, 0)
  tempDate = startDate
  Do
    strSql = "insert Into tblDays (EmployeeID,TheDate) values (" & EMP_ID & ", #" & Format(tempDate, "mm/dd/yyyy") & "#)"
    tempDate = tempDate + 1
    CurrentDb.Execute strSql
  Loop Until tempDate > endDate
End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:49
Joined
Oct 29, 2018
Messages
21,358
no need to create a table of dates - you can do it in a query.

create a table which contains one numeric field and populate 10 records with the values 0 to 9

in this example the table is called admCounter and the field ctr

Code:
SELECT DateAdd("d",([tens].[ctr]*10)+[ones].[ctr],Date()-Day(Date())) AS myDate
FROM admCounter AS tens, admCounter AS ones
WHERE (((DateAdd("d",([tens].[ctr]*10)+[ones].[ctr],Date()-Day(Date()))) Between Date()-Day(Date())+1 And DateAdd("m",1,Date())-Day(Date())))
ORDER BY DateAdd("d",([tens].[ctr]*10)+[ones].[ctr],Date()-Day(Date()));
Save the query to be used whenever you need - then left join (if required) to your other tables

you can change the BETWEEN statement to reference your form selection if required
I like this idea better than my own way of doing it. Thanks!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:49
Joined
Feb 19, 2013
Messages
16,553
thanks - I use this query to create a dynamic calendar. Credit should go to a suggestion from June some time ago which uses the id of the msysobjects table (id mod 10) to create the ctr values 0-9

Code:
SELECT [Id] Mod 10 AS ctr
FROM MSysObjects
GROUP BY [Id] Mod 10
HAVING ((([Id] Mod 10)>=0))

e.g.

Code:
SELECT DateAdd("d",([tens].[ctr]*10)+[ones].[ctr],Date()-Day(Date())) AS myDate
FROM (SELECT [Id] Mod 10 AS ctr
FROM MSysObjects
GROUP BY [Id] Mod 10
HAVING ((([Id] Mod 10)>=0))) AS tens, (SELECT [Id] Mod 10 AS ctr
FROM MSysObjects
GROUP BY [Id] Mod 10
HAVING ((([Id] Mod 10)>=0))) AS ones
etc

you can also easily up the numbers as far as you want

Code:
SELECT ([hundreds].[ctr]*100)+([tens].[ctr]*10)+[ones].[ctr] AS ctrNum
FROM admCounter AS hundreds, admCounter AS tens, admCounter AS ones

or step 10 or some other value
 

Mark_

Longboard on the internet
Local time
Today, 09:49
Joined
Sep 12, 2017
Messages
2,111
veshand,

One thing to think about if you are doing timekeeping, how do you want to handle holidays /weekends?
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:49
Joined
Sep 21, 2011
Messages
14,044
Trying to understand this and failing miserably. :banghead:

If I use
Code:
SELECT DateAdd("d",([tens].[ctr]*10)+[ones].[ctr],Date()-Day(Date())) AS myDate
FROM (SELECT [Id] Mod 10 AS ctr
FROM MSysObjects
GROUP BY [Id] Mod 10
HAVING ((([Id] Mod 10)>=0))) AS tens, (SELECT [Id] Mod 10 AS ctr
FROM MSysObjects
GROUP BY [Id] Mod 10
HAVING ((([Id] Mod 10)>=0))) AS ones
I get a result of a date every 10 days?
30/06/2019
10/07/2019
20/07/2019
30/07/2019 etc.?


thanks - I use this query to create a dynamic calendar. Credit should go to a suggestion from June some time ago which uses the id of the msysobjects table (id mod 10) to create the ctr values 0-9

Code:
SELECT [Id] Mod 10 AS ctr
FROM MSysObjects
GROUP BY [Id] Mod 10
HAVING ((([Id] Mod 10)>=0))

e.g.

Code:
SELECT DateAdd("d",([tens].[ctr]*10)+[ones].[ctr],Date()-Day(Date())) AS myDate
FROM (SELECT [Id] Mod 10 AS ctr
FROM MSysObjects
GROUP BY [Id] Mod 10
HAVING ((([Id] Mod 10)>=0))) AS tens, (SELECT [Id] Mod 10 AS ctr
FROM MSysObjects
GROUP BY [Id] Mod 10
HAVING ((([Id] Mod 10)>=0))) AS ones
etc

you can also easily up the numbers as far as you want

Code:
SELECT ([hundreds].[ctr]*100)+([tens].[ctr]*10)+[ones].[ctr] AS ctrNum
FROM admCounter AS hundreds, admCounter AS tens, admCounter AS ones

or step 10 or some other value
 

Users who are viewing this thread

Top Bottom