Using a form to enter multiple records (preferably without code)

[AJC]

Hey all,

I'm new to Access, self taught as of Xmas. I have some experience with programming but that was long ago, and I'd prefer not to use it if I don't have to, although may be no choice for this.

What I'm building is a labor tracking database. Employees, wages, task list, hours worked etc.

I would like to enter the data 1 week (7 days) at a time, so my idea is top of form(header) I put the first day of the week, and the employee name.



Then the main form would have about 5 tasks per day (35 total), so it would input: date, task #, hours #.

I have attached the data sheets of what I'm looking for however the form is woefully incomplete as I don't want to spend too much time since I'm not sure how to implement it.

Thanks!!

[rpeare]

this is a simple example of a data entry form

Labor Tracker_be.zip

[AJC]

Thanks, that will work for me.

I have been working with it and now have run into a unique problem in my report. In report view it shows none of my calculated fields, unless I click on them (there are no on click commands, they are set to visable). If I go directly to Print Preview everything shows as it should.

[rpeare]

that's not terribly unique

I don't use report view at all, but I've seen it happen. I don't know if there is a 'fix' because, for all practical purposes, there's no reason to use the report view. The only difference between report view and print preview is that report view is not broken up until separate pages where print preview is.

[AJC]

Makes sense, I have used report view on previous projects so I can include buttons to print or to close and go back to a menu.

Now the other issue I have is I'm sorting by Category (its own header/footer), Task, Month How can I make it so that the Category and everything below it is on the same page? The setting keep together is set to yes on all of them but it doesn't keep it all on same page.

[rpeare]

are you talking about forcing page breaks? there should be a button on your toolbar to put in a page break, you'd just have to insert it into your category footer after any subtotaling you're currently doing to make it force the new page.

[AJC]

Yes and no, I don't want each category on its own page, if 2 fit awesome, but if it splits off and is showing the month data and I'm not sure category it is without flipping back a page then I want it on its on page. Kind of like a frozen panes options in Excel(kinda except the category information will be changing based on the query, etc.)

May not even be possible.

[rpeare]

You can do that with the page header, putting the category there as well. Depending on the nature of your report you may be able to, then, suppress either of the two category text boxes depending on certain circumstances.

You can also play with the header sections of your category (if you're actually using 2010)

go to your category grouping and try the option for 'KEEP WHOLE GROUP TOGETHER ON ONE PAGE' or 'KEEP HEADER AND FIRST RECORD TOGETHER ON ONE PAGE' and see if that gives you the result you want.

[AJC]

Thanks,

New question regarding this program. I want to be able to keep all the data together from year to year. So I need to be able to keep track of wage increases.

I have the employee table which has number, first name, last name, wage
This would be the default wage value, or I can ignore this field all together

I have another table tblWage with the fields EmployeeNumber (linked with number from tblEmployees), Wage, DateEffective (start date of wage)

I'm having a hard time figuring out how to calculate the cost using the dynamic ability to pull the new wage.

Currently to calculate the cost i have it pull out of a query and it takes the hours * wage from tblEmployees.

Any ideas?

[rpeare]

In other words you want to be able to reproduce an invoice (for example) for a period that may be a week, a month or a year ago and have it pull the correct hourly wage from your history table?

Are you also putting a termination date on your wage. For instance if your employee 1 has had 2 wage increases does your table look something like:

tblWage
EmployeeNumber  Wage  DateEffective  DateTerminated
1               10.00 1/1/2009       9/30/2009
1               10.50 10/1/2009      4/5/2012
1               11.50 4/6/2012

where the most recent wage is the one with the open termination date?

[AJC]

I wasn't planning on entering a termination date or an end date for a wage, however i can. Employee numbers will always be static, even after an employee is terminated. But we typically do increases on a yearly basis so they start 1/1 and end 12/31 of said year. but flexibility within the program would be great!

[AJC]

I've got it working, but I have to have an end date or termination date or it doesn't show any of the data

Here's my sql script from my query. I know why it won't show the data if no EndDate, but not sure how to fix it.

PARAMETERS inEmpNo Short;
SELECT tblHours.EmployeeID, tblEmployees.LastName, tblEmployees.FirstName, tblHours.DateWorked, tblHours.TaskID, tblHours.Hrs, tblTasks.Code, tblTasks.Category, tblTasks.Task, tblWage.Wage, [tblWage]![Wage]*[tblHours]![Hrs] AS Cost, DatePart("m",[tblHours].[DateWorked]) AS [Month]
FROM (tblTasks INNER JOIN (tblEmployees INNER JOIN tblHours ON tblEmployees.ID = tblHours.EmployeeID) ON tblTasks.ID = tblHours.TaskID) INNER JOIN tblWage ON tblEmployees.ID = tblWage.[Employee ID]
WHERE (((tblHours.EmployeeID) Like IIf(IsNull([inEmpNo]),"*",[inEmpNo])) AND (([tblHours]![DateWorked])>=[tblWage]![BeginDate] And ([tblHours]![DateWorked])<=[tblWage]![EndDate]))
GROUP BY tblHours.EmployeeID, tblEmployees.LastName, tblEmployees.FirstName, tblHours.DateWorked, tblHours.TaskID, tblHours.Hrs, tblTasks.Code, tblTasks.Category, tblTasks.Task, tblWage.Wage, DatePart("m",[tblHours].[DateWorked]);

[rpeare]

the easy way is to put in a termination date of 12/30/9999

the other way is to build a query based on your employment wage table and fill in any missing termination dates with an artificial end date (again 12/30/9999 or something like that) then use that query as the basis for your lookup