Form layout for one table

[Imstac73]

Hi, I am creating a timecard entry system using Access. Users will be keying in a record for each employee for each day of the week and the records will update only one table. So the users do not have to re-key duplicate information for each record such as employee id and to make it quicker for them to key in the records, I would like the form split into two sections. In the top section the user will key in employee id and any other duplicate information. Then below I would like to have a datasheet view where they can key in individual record information such as job#, cost code, record type, hours, etc.

When they hit the Submit button the form should pull the employee id from the top of the form and insert it into the table for each individual record they have keyed in the dataset below.

Is it possible to do this? If so, how would I go about setting this up? I looked at subforms and split forms but they don't seem to give me the format I need.

Any help will be greatly appreciated. Thanks.

 

[MajP]

It sounds to me that you can use a traditional subform. Is that not the case?

 

[postermmxvicom]

Yes, it is possible to do this. Without knowing your table structure, I can't say for sure, but, "duplicate information" has me a little concerned.

Typically, you would want to set up multiple tables to avoid duplicate information.

For instance, you would have a table which had the employees name and id and other such information that has a 1 to 1 relationship with the employee.

Then you would have a second table with the job#, cost code, record type, hours and other information that has a 1 to 1 relationship with the job. This table would also have a foreign key which links this information to a record in the employee table, because this information has a many to one relationship with the employee.

Then, once your tables are set up correct (which is the most important thing), you then set your form up this way: You have a combobox which selects the foreign key from the employee record (and thus all the 'duplicate information') and the your user enters in the remaining info for that record (job#, cost code, record type, hours).

Notice that all the 'duplicate information' was stored in another table and referred to by one field in the job table.


Having said all of that let me say this: I am super green and misread these posts all the time :/ I hope I wasn't too off base. If I was and you knew all this already, I apologize

However, if this post is on target, there is some link the guys around here are always giving out which explains this stuff about relational databases pretty good and I could dig that up for you.

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[PHV]

Have a look here:

http://r937.com/relational.html

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Imstac73]

I think I need to give a little more explanation on the purpose of the Access program. This program is basically a front end for our timecard managers to key their information into a table that will uploaded into our Accounting system. This table is a detail table so all the 'duplicate' fields(ie employee id, week ending date,etc) will be repeated in every record for that employee.

I looked at using a subform but it looks like this is for parent/child table relationship only and I only have this one table.

 

[postermmxvicom]

Could you just make an unbound form for the parent form?

Then have controls for the 'repeated' data there, and use those values to set the default values for the controls on the child form. Perhaps, using the on change event of the controls on the parent and a little VBA.

But, I have a hard time imagining your table structure based on your posts. You might want to check those links PHV gave, those are the ones I mentioned in my previous post.

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[fneily]

Since from your last post you don't want to normalize, why not just set up an Excel spreadsheet? I did that for a company. With a little VBA, an employee filled out an Excel "form" that produced a copy for the employee and sent the data to a history workbook. It kept track of employee's hours, time off, etc.
"the purpose of the Access program." The purpose of the Access program is to create a Relational Database system, not spreadsheets.

 

[MajP]

I looked at using a subform but it looks like this is for parent/child table relationship only and I only have this one table.

Not necessarily. They are a little more powerful than that

The fields or controls you use to set these properties don't need to have the same names, but they must contain the same kind of data and have the same or a compatible data type and field size. For example, an AutoNumber field is compatible with a Number field if the FieldSize property for the Number field is set to Long Integer.

You can use the name of a control (including the name of a calculated control) to set the LinkMasterFields property, but you can't use the name of a control to set the LinkChildFields property. If you want to use a calculated value as the link for a subform, subreport, or embedded object, define a calculated field in the child object's underlying query and set the LinkChildFields property to the field.

When you specify more than one field or control name for these property settings, you must enter the same number of fields or controls for each property setting and separate the names with a semicolon (.

So theoretically you could use a select distinct employee query as the master form and an unbound date textbox. This would then fill in each field in the subform. All using the same table.