Help with Sub from & Main form

[scottappleford]

Hi

Appreciate your help in advance.

The db is related around timecards.

I have a tblcompany & tbl employee, these are not linked (maybe they should be) but tblcompany is linked to tbltimecard. Tbl timecard is linked to tbltimecardhrs and tblemployee is linked to tbltimecardhrs.

Now tbltimecard is used to create the main form and tbltimecardhrs is used for the subform (datasheet).

I select the company on the mainform but the employee in the subform and i would to filter only employees who work for the company in the main form.

Now i have written this it only makes sense (i have to for it to work) to link the company and employee tables but i am not sure how to link them so they link back in to the forms, once the link is done correctly I suppose it just a matter of cascading combobox, which i should be able to manage.

Thanks again

 

[gromit]

Ah - A fellow traveler in the realm of Timesheet databases!

A couple of design thoughts. It does seem to me that tblEmployees should include a key to the tblCompany entries. Assuming each employee works for a single company, it makes sense to associate this information with the employee, rather than the timecard. This also allows you to generate a list of employees by company, which seems natural. So I would first suggest getting the company info from tblTimecards to tblEmployees.

Enter the appropriate fields and then ensure that you have established the correct relationships (Tools / Relationships)

Next, I'm not quite clear what is the function of the form you are describing. Do you want just to display a list of timecards? Are you wanting to do data entry?

I have attached a small example that has tblEmployees associated with each company. There is a form (frmCompanyDisplay) that shows the employees with timecars for that particular company.

Take a look and see if this helps. Ask questions if I'm not making sense (frequent occurrence).

- g

 

[scottappleford]

Hi

Thanks for the sample - I understand what you mean about relating the tblcompany and tblemployee.

The main theme is the timecard (mainform) and will be for data entry - and one time card will have a date ie. one card per company, per day, company and work description fields. The work description will be a main description with sub descriptions, but they will all be standard so I use a combo box for these which will filter and only show the subtasks related to the main one (side issue but similare to the tblcompany and tblemployee issue).

Now in the main form there is a subform (datasheet), this will be the tbltimecardhrs (the many side of the relationship to tbltimecard) in this will be an employee field, but this can only show employees related to the company selected on the main form (my problem)

Then they will put in the number of hours. There will also be three choices of:
1. RT - Right time
2. OT - Overtime
3. DT - Double time

If someone works RT & OT in the same day this will mean there name will appear twice (do i have a normalisation problem as well?)

What I am trying to achieve is weekly report that will show by company - all the employees for that week and there hours for Mon-Tues-Wed etc along with the hours for RT, OT, DT.

Hope this is a bit clearer - appreciate your help.

Thanks

Scott

 

[gromit]

Thanks, I think I'm starting to get a better picture.

The frmCompanyDisplay in my sample might be a starting point for your Timecard main form. It should be linked to the timecard table though so that you can navigate through the timecard records.

The company combo box illustrates the basic idea of using VBA to filter records on the subform. For your case, rather than timecards on the subform, it should be timecard hours. The key to the combo box is that it takes the CompanyID and appends it to the RecordSource statement for the subform.

So, first create a subform based on tblTimecardhrs that includes EmployeeID, number of hours, type of hours (RT, OT, DT).

Next, make your main form based on the tblTimecards. Have a combo box (or some other control) with the CompanyID. Add the other fields that are appropriate for the timecard, ie date and work description. Include the subform on the form. Important -- do *not* create a parent/child relationship between the two. Use the VBA to handle the record source.

There are a number of niceties that can be implemented as you go. E.g. disabling the subform until the company is selected, duplicating existing timecards, etc. But get the basics right and build on it.

Once you have the all the data in the database, then you should be able to produce the report that you want (Although you probably will wind up doing a crosstab query to break the workdays into days of the week).

As for your question about multiple Timecardhrs for a single employee on a given day: No, I do not believe that this violates the normalization. It is similar to having different records for different days, the Hour Type (RT, OT, DT) is just another characteristic of the time.

Does this answer your question(s)? If you are still stuck, let me know and I'll see if I can be more specific.

- g

 

[scottappleford]

Getting there

Hi

I think i am understanding now - thanks for all your help. Like you say when i get the basic structure correct i can build on it.

The only thing i am still not sure about is the hour classification (RT, OT, DT).

Can i not just have fields with this headings as number and just fill in the applicable ones rather than have a name appear more than once on the timesheet?

I have done some work so far - just to link the tables up etc - i will tidy it up when correct - could you look for me to see if i have done this right?

Thanks again

scott

 

[scottappleford]

attached

sorry now attached

 

[gromit]

Looking better and better!

I think that some of the devil in the details of the forms that you will need to create, so it would probably benefit you to flesh these out as you go. For example, you may decide that you need to change your data tables to accomplish something particular on a form. I really like the approach of getting the basic structure and concepts established before spending time on the pretty stuff: formatting, etc.

As for the hours classification, are you talking about the data entry form or the report? I think that good normalization would probably indicate that the hours should have a separate hour_type field associated with it, e.g. 4 hours, type RT; 6 hrs, type DT, etc.

However, my personal opinion is that you shouldn't let normalization for its own sake (or any other guiding principle) get in the way of what you are trying to do. Part of deciding what is the right approach is knowing how you will use the resulting data. Is there ever going to be a time that you might have two separate DT entries for the same employee on the same day? If so, and the separation is legitimate, then you need to do the hours_type field. On the other hand, if you can never see a need for the hours_type field, then why do it?

[Just as a short note, in the Timecard database that I worked on recently, I did a lot of things that were "atypical" - including unbinding the records from the form so that I could get things into a format that I wanted. As long as you are doing things for a reason, and not because you don't know any better, then it's your show.]

Your tables generally look good - some of the relationships are not showing as "1-to-many" though. Go to Tools / Relationships and you can edit these.

Let me know if you have more questions and good luck!