Help Needed With Populating Fields in Table

Well u dont need to store the zero hours, not worth it

the queries that get it need to handle it

one way is to write a query like this

SELECT tblEmployees.EmpNo, tblEmployees.Emp_firstname, tblEmployees.Emp_lastname, (SELECT UnitCode & " " & tblUnitName FROM tblUnit, tblLink WHERE tblUnit.UnidID = tblLink.UnitID AND EmpID = e.EmpID)
FROM tblEmployees e LEFT JOIN tblLink ON tblEmployees .EmpID =tblLink .EmpID;

This does everything

all we need to do is check the dates
i.e.

WHERE [Date] = ...

urm, because of alias, we have to use that, sorry
also recorded hours

SELECT e.EmpNo, e.Emp_FirstName, e.Emp_LastName, (SELECT UnitName FROM tblUnit, tblLink WHERE tblUnit.UnitID = tblLink.UnitID AND tblLink.EmpID = e.EmpID) AS Unit, NZ(Hours,0) AS HoursRecorded
FROM tblEmployees e LEFT JOIN tblLink ON e.EmpID = tblLink.EmpID;

Explanation of query

SELECT all employess from database, display there employee number and name
then for any units they are linked to, display the units, and the hours recorded


oops, just explaining this, I realise this

do u want a list of employes against all units? then record hours recorded against it in a given month? or zero if nothing there

in terms of level if point of adding

on your entry form, u load an employee, then month then you click load
u can check tblLink for any records

if none exist then u can run this query

Dim sSql as String

sSql = "INSERT INTO tblLink (EmpID, UnitID, Hours, [Date]) SELECT " & lCurrentEmpIDSelected & ", UnitID, 0, #" & lCurrentDateSelected & "# FROM tblUnit"
DoCmd.RunSQL sSql

this sql equates to (if current empid is 1 and date is 16/5/2005)

insert into tblLink (EmpId, UnitID, Hours, [Date]) SELECT 1, UnitID, 0, #16/5/2005]# FROM tblUnit


so basically we create a entry for all units for that employee in a given month

then your entry form, it only ever does updates, no need for adding or deleting.




Note, one thing Date is a reserved word so try not to use that as a fieldname

Hi again
Gosh you post quick!
This is what I need to get out - one of many things. I've anonymised the data - but it works fine so long as there's an entry for every unit against every employee in the hours column of a given date.
http://www.ghost-watch.co.uk/report.html
The data is filled in from a main form with a sub-form attached. In the main form the clerk will select the employees name from a combo box. This then fills in the employees data on the main form. In the subform there are the following fields - all the relevant fields from tblLink  - with a datasheet where the unit can be picked from a drop down list - this then tabs to hours where the hours are logged against the unit and the last field is the Date field (I'll rename that) which is set to a default value of Now - 1 month as the sheet will always get filled in the month after the timesheet is submitted. Snap shot of the form here:
http://www.ghost-watch.co.uk/form.html
Whilst your last post seems like a good idea we need the reports for every month over a year - ie we'll need historical data. If the Date field is constantly updated wouldn't we lose that?
Thanks
Deb

P.s I may not be back on this until tomorrow now as I'm going out for dinner tonight :)

Hi Deb, how was your dinner?

okay lets go one of two routes. The 1st is much easier, but you will have more records in your DB. The second requires more work, all reports need to ensure they follow this

Lets go 1st route

Looking at your form, why dont you also display a date filter on the top half. So when the employee is loaded along with a date (can be just Month/Year - you can make it unbounded. I will show you how in another post on this thread).

The when they click "Add Employee" - you may want to change this to Edit Employee,

you are loading your subform up with a filter I presume.

Why dont u do this, when they click on Edit Employee, check the employee has records created for that one month, if they dont exist then create them. Or you can have a job somewhere that runs once a month (or once a year for all 12 months) that creates the employee to unit records to tblLink for all Employess and all Units. Default hours is zero

Then in your subforn, all units are listed, u simply need to record the hours. The date is based on the date in the mainform

Debs, as promised, how to create a unbounded combobox with dates in

this code u place iin your form and call it on form_load


Public Sub PopulateOne()
'We have a combobox, which is of row source type Value List and is named cboMonths
   
    Dim i, j As Integer
    Dim iCurrent As Integer
    Dim iCnt As Integer
   
   
    cboMonths.RowSource = ""
    iCnt = 1
    'Do 10 years back, 5 years forward, from current system date
    'If u wish to change, simply change the numbers below
    For i = Year(Now()) - 10 To Year(Now()) + 5
   
        'For each month in year
        For j = 1 To 12
       
            If cboMonths.RowSource <> "" Then cboMonths.RowSource = cboMonths.RowSource & ";"
            cboMonths.RowSource = cboMonths.RowSource & Format(DateSerial(i, j, 1), "MMM YYYY")
           
            If i = Year(Now()) And j = Month(Now()) Then
                iCurrent = iCnt - 1
            End If
            iCnt = iCnt + 1
        Next j
    Next i
   
    'Point to current month/year
    cboMonths.Value = cboMonths.ItemData(iCurrent)
End Sub


if u want two, then even easier.
the month combo simply has Jan to Dec,and year combo has the years u want

Option 2 - dont populate tblLink but produce on query is tricky

I cant imagine how to do it as queries, considering tblEmployee and tblUnit are seperate entities, they do not know each other except for the relationship in tblLink

Let me know what u think of 1st solution, we can take it from there, if option 2 required. I can help u on that

Hi
Just been fixing a pc at another site. Dinner was really good except we missed the motorway turn off and got there an hour late and ravenously hungry :)
I'll just go through all your posts and get back to you in a bit - thanks again!

OK - I'm thinking let's go with Route 1 - this database is supposed to be temporary until we start to use the inbuilt module for this on our finance application. Although temporary it could be 12 months or so. Are there any implications for having so many records? I will it slow it down significantly or even crash it at any point?

I tried the code to populate the table but I don't seem to be able to get it to work. How do I get that into my database and run the code? - (Am still learning)
Thanks
Deb :)

Ah - scrub that yes I can get it to work - I was just passing it a non-existent empID! Duh

Hi
Thanks for your help so far. I have pasted the code for Public Sub PopulateOne in a module. Then for employee of EmpID no.20 I run the following

PopulateLinkTable 20, DateSerial(2006, 4, 1)

This populates the table. If there are no prior entries then all the fields are populated. If there are prior entries in it then duplicate entries are added so table tblLink looks like:

DateID      EmpID      UnitID      Hours      WorkDate
4095      20.00      7.00      23.00      01/04/2006
4099      20.00      7.00      0.00      01/04/2006

I've tried setting the default workdate in the table tblLink for example to #01/04/2006# but it still doesn't only populate the fields for which there are no entries.
What I could also do with doing is once all the data has been entered for a month - then running this code against all the employees, so it'd need to loop through all the emps and append the 0 hours. Can you help?
Thanks
Deb :)

Sorry just to add: The reason I don't want the tables populated beforehand is that the data entry clerk will need to enter probably between 3-7 codes which then have hours logged against them. Finding these in the datasheet in the subform once it has been prepopulated is then an onerous task and one that they can get wrong quite easily.

I need to find an easy way for them to select these codes or input them in a way that error checks so they can't log the same code against the same date more than once. At the moment they select the codes from a combo box and that seems to work ok - well it will once I can get the tables populated after data entry.

Trip out of town for dinner, must be a good restaurant! The only time I go out is if my partner wants to go to a vege restarant, usually a trip up the M69.

Deb, there should not be any duplicate entries for one employee and one unit in a given month. So I checked and doh, what a dum .... I am

See where there is DCOUNT

I got the check the wrong way round!

this line (note, datemask is YYYYMM but Im checking against MMYYYY)

                If DCount("EmpID", "tblLink", "EmpID = " & rsE!EmpID & " AND UnitID = " & rsU!UnitID & " AND Format(WorkDate,'YYYYMM') = " &  Format(i, "00") & iYear) = 0 Then

should be

                If DCount("EmpID", "tblLink", "EmpID = " & rsE!EmpID & " AND UnitID = " & rsU!UnitID & " AND Format(WorkDate,'YYYYMM') = " & iYear & Format(i, "00")) = 0 Then

What if u got rid of the subform as a data entry, just leave it as a read only, so you can see for a given month and employee, all the hours recorded against all the units

but in the main form, u can have a drop down of units, and a prompt for number of hours

along with the date, u can then perform the update

cboUnits  (rowsource of SELECT UnitID, UnitCode & ' ' & UnitName FROM tblUnit)
txtHours
cboMonth
txtEmpID  (hidden field - I guess u got this already to identify a employee)

then a button which says Update Hours (or something along those lines)

then the click event for that button i.e.  private sub cmdUpdateHours_Click

if NZ(cboUnits.Column(0),"") = "") then
    msgbox "Please select a unit"
    exit sub
end if

.... etc continue validation


'Now we can assume that user has a entry already (prepopulation), or u can perform a check here

'If we assume, we simply to this

sSql = "UPDATE tblLink SET Hours = " & txtHours.Value & _
          " WHERE EmpId = " & txtEmpID.Value & _
          " AND UnitID = " & cboUnit.Column(0) & _
          " AND Format(WorkDate,'MMM YYYY') = cboMonths.Value

DoCmd.RunSQl sSQL


Note, u need to put in validation, confirmation prompts if u want, error checking etc



does this make sense?


   

populate for all employees, remember just pass in zero


All employees, for 12 2004

    PopulateLinkTable 0, DateSerial(2004, 12, 1)

All employees, for 2005

    PopulateLinkTable 0, 2005




ok, one thing to add to the function, before you do End Sub, ensure recordsets are closed

rsE.Close
rsU.Close
set rsE = Nothing
set rsU = Nothing

Hi Rocky (easier to type than rockiroads)
Thanks for the top tips - I'll try them out tomorrow (Thursday) and get back to you -  I just got in from a new hairdo plus shopping trip after work - helps morale when dealing with evil Access on top of crashing pc's, servers and group policy.
It was a very nice restaurant (I am spoiled) - one of these gastro-pubs. We were just so hungry by 10pm we practically inhaled the food though. The restaurant staff must have thought we'd been starving ourselves for days. Thanks for all your help, it is very much appreciated - I don't want to take the **** though and have you deal with too many things in one thread. Am glad my partner's not veggie though and M69 sounds not too far way from here (Birmingham - land of the Motorway of Doom (M6). Anyway thanks again and catch you hopefully tomorrow. (Next time I post a question I'll drop you a link to your email if that's ok and if you're free maybe you can take a look?)
Thanks again
Deb :))

Hi Deb, you are indeed spoilt, dinner, new hairdo now shopping trip. You obviously getting paid well :)
either that or your partner is really spoiling you

Acesss is evil indeed, my company keeps giving me work to do, then you get stuck with this label. I was strong in C++ before I joined this company, its been all downhill since :(

Depends what side of Birmingham u live, north side by M6, then M69 aint too far. Either way, Brm up the road from me (A45), Coventry!!!

No worries about dropping me a email - be delighted to help you out - but lets get this thread sorted first

Hi
Paid well - No
Spoiled - Yes

Thanks - got the tables populating fine now.

I've got the combo box cboMonths working - however it'd be good if I could get it to update the view in the subform for field tblLink.Workdate to match the date selected in cboMonths (if it exists or get the date of the first record in tblLink.Workdate to fill in with this value. I know that there's code that needs to go into the AfterUpdate event for this combo but I'm having trouble with the syntax for dates.

Re: Data Entry in Main form with update button - Thanks for the suggestion and I've tried it - I think though the data entry is easier if selecting from a drop down of units in the subform - just need to get it to check that there's an entry that's not 0 against a unit code if a unit code has been entered in the subform. Once all time sheets have been entered we can then populate the rest of the Link table from a command button for that month - if we could get the appropriate date passed to the PopulateLinkTable function after all the timesheets had been entered for that month via a command button that'd be great.
Currently I've got the following against a cmdbutton and it works fine to populate all employees for the month defined, but obviously I need to set the date manually.

DoCmd.SetWarnings False
PopulateLinkTable 0, DateSerial(2006, 4, 1)

I think we're nearly done here for this thread - Thanks so much and hope the weather's better in Cov than it is here!
Deb :))

Your probably on midlands pay like myself, then I guess asking if your paid well is a silly question - I should know better!

Regarding your subform, could you not set the filter for the subform?
Im assuming your subform is linked to the main form via emp id

in that case, try this bit of code when u want to filter by date
assuming my subform is called tblLink_Subform

cboMonths displays as MMM YYYY
so if we format WorkDate to be that then check against cboMonths

    Me.tblLink_Subform.Form.Filter = "Format(WorkDate,'MMM YYYY') = '" & cboMonths.Value & "'"
    Me.tblLink_Subform.Form.FilterOn = True






I was thinking of an alternative
get rid of subform

Have a combobox that lists the units, months
A textbox that user enters hours against then hits a button, which updates the record based on the month, selected unit and employee

Or you can have a big listbox which on the button Load, will populate based on what the user has enetered i.e. month, employee

SELECT LinkID, UnitCode, Hours FROM tblUnit, tblLink WHERE tblUnit.UnitID = tblLink.UnitID AND tblLink.EmpID = <selectedempid> AND Format(WorkDate,'MMM YYYY') = cboMonths.Value

then on the click event of the listbox, u load the hours textbox (value taken from listbox), user can then edit the hours
lstBox_Click
    txtHours.Value = lstBox.Column(2)

user edits the hours, then clicks a Save button
this will then update DB then refresh listbox



OR If your feeling really advanced, use a ms flexgrid


But I leave this up to you how you feel bext

Hi
Filter working fine now! Just one last thing before I close this - is there a way to have the default date for Workdate in tblLink set to the first date of the current month minus 1 month?
ie
Today's date 18/05/06
Default date would be: 1/04/06
This would be most useful!
Thanks so much - I've learned a lot - All I need to do now (I think) is sort out the queries and the reports,
Thanks
Deb

Now() returns the current date and time

you may find some experts prefer the use of Date()

I prefer Now() because it does not have any reference issues, whereas Date() does.

But forget that for the time being


In the code for cboMonths population, I set a variable in that loop which points to current month/year
u can just change that

here is the code

           If i = Year(Now()) And j = Month(Now()) Then
                iCurrent = iCnt - 1
            End If


Well, we dont want to check against Now()

If u use DateAdd("m",-1,Now())

this returns the current date - 1 month
so use that and replace it with Now()

Hi
Hadn't checked the population of the table since we checked the DCount fix earlier and had assumed that the day of the month matters. It doesn't - thanks ever so much! Have a really good day,
Best wishes
Deb :))

it did when I tried it, u wanna post that function here? lets have a look

Ok - will do tomorrow - but am sure that it didn't post duplicate entries like it did before. I'll run it again and let you know - don't have access to the code right now, but if there's a problem I'll post it,
Thanks
Deb :))

ok, tomorrow then

have a good evening


P.S. Hope u doing this on a test DB, we could go about clearing duplicate data
Access has a wizard that enables u to find duplicate data, clear that then see what happens

No worries - test db and making copies for test purposes as I go along! Not going to be live for another 4 weeks so I reckon I've got time. You have a good evening too :)

Morning - hope you had a good evening - here's the code but it's definitely not duplicating values in tblLink.

Code for cmd button-

Private Sub popempcmd_Click()
DoCmd.SetWarnings False
PopulateLinkTable 0, DateSerial(2006, 5, 1)

End Sub

'Pass in 0 for all employees or employee id
'Pass in YYYY for a specified year, or date to take in a month in a year
Public Sub PopulateLinkTable(ByVal lEmpID As Long, ByVal sDate As String)

    Dim sSql As String
    Dim sWhere As String
    Dim rsE As DAO.Recordset
    Dim rsU As DAO.Recordset
    Dim i As Integer
    Dim iStartMon As Integer
    Dim iEndMon As Integer
    Dim iYear As Integer
   
   
    'Build up our SQL sting. Filter by employee id, if specified
    sSql = "SELECT * FROM Employees "
    If lEmpID > 0 Then sSql = sSql & "WHERE EmpID = " & lEmpID
       
    'Define recordset for units
    Set rsU = CurrentDb.OpenRecordset("SELECT * FROM Units")
   
    'Now check the dates - must be either 4 characters (year), or a date
    If Len(sDate) = 4 Then
        'We have passed in a year, lets create link entries for all months in the year
        iStartMon = 1
        iEndMon = 12
        iYear = Val(sDate)
    Else
        'Otherwise its just the one month
        iStartMon = Month(sDate)
        iEndMon = iStartMon
        iYear = Year(sDate)
    End If
   
   
    'Open our recordset, keep looping (note, if empid specified, there should only be one record)
    Set rsE = CurrentDb.OpenRecordset(sSql)
    Do While Not rsE.EOF
   
        'Move to first unit
        rsU.MoveFirst
        Do While Not rsU.EOF
       
            'Keep reading for months specified, check tblLink record exists or not. Insert if not there
            For i = iStartMon To iEndMon
               
                'Check if record not already there
                If DCount("EmpID", "Link", "EmpID = " & rsE!EmpID & " AND UnitID = " & rsU!UnitID & " AND Format(WorkDate,'YYYYMM') = " & iYear & Format(i, "00")) = 0 Then
                'If DCount("*", "Link", "EmpID = " & rsE!EmpID & " AND UnitID = " & rsU!UnitID & " AND Format(WorkDate,'YYM') = " & Format(i, "00") & iYear) = 0 Then
                    'No records exist for this employee and this unit for specified month/year
                    sSql = "INSERT INTO Link (EmpID, UnitID, Hours, WorkDate) VALUES (" & rsE!EmpID & "," & rsU!UnitID & ",0,'" & DateSerial(iYear, i, 1) & "')"
                    DoCmd.RunSQL sSql
                End If
            Next i
            rsU.MoveNext
        Loop
        rsE.MoveNext
       
    Loop
End Sub

Hi Debs, sorry about late reply, got busy at work.

Lets just clarify one thing, the idea is not to have duplicates in tblLink. You should only have one record for a particular employee, unit and month. You could make these three fields a combined index (primary key) so that duplicates never occur.

So at the moment, is it working how you want it? Where is it going wrong if it is it going wrong?

I may or may not be able to respond quickly as Ive got a few issues at work to deal with

Hi

"Lets just clarify one thing, the idea is not to have duplicates in tblLink" - I know and there aren't any, which is top news. It's working fine so thatks for all your help
Deb :)

ok, cool

I got confused there for a minute, from reading one of your previous posts.
Im sure Im losing it. Either that or Im forgetting how to read