Data entry form using two tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to create a form for data entry that will display records from one
table, based on the date, and enter data into a new table, which will link to
the original. Is this possible or am I trying to accomplish too much?

Thanks for any help you can provide.
 
Hi, "CNM",

Sure this is possible, but how you implement it depends on the type of data
in the first table, and its relationship to the second--i.e., 1-to-1,
1-to-many, many-to-many. Please additional detail on your table structures
and their relationship.

Sprinks
 
I want to create a form for data entry that will display records from one
table, based on the date, and enter data into a new table, which will link to
the original. Is this possible or am I trying to accomplish too much?

Thanks for any help you can provide.
Click to expand...

A Form with a Subform is the most common way to do this. As Sprinks
suggests, it would help if you would post some more details.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
The first table will have employees, dates, and their work schedule. This
information will be consistent. The second table will be data entry of their
actual start and end times. I want to be able to pull the records from the
first table by date, and then do data entry to the second table, and carry
the date into the second table in order to do queries. I'm not too familiar
with subforms, so any suggestions are more than welcome.

Thanks so much for the assistance.
 
A subform is itself a form, created in the normal way--either in design view,
or using the wizard--that is inserted onto another form (referred to as the
Main Form).

A typical use of a subform is to display records on the many side of a
one-to-many relationship, such as all the orders placed by a given customer,
or all the products ordered on a given order. In both of these cases, the
one and the many side share a common, or, linking, field--in the first case,
customer number, in the second, the order number. Once a subform is inserted
on the main form, it is a control itself, and has properties specific to its
control type. The two most important properties are the Link Master Field
and Link Child Field properties, which specify the linking field from the
main form and child form, respectively.

I think it would be helpful if you would post the table structures of your
two tables, specifying the fieldnames and datatypes, such as:

Orders
--------
OrderNumber AutoNumber
OrderDate Date
Customer Number
ShipVia Number
etc.

Once we understand your table structures, it should be straightforward to
suggest a solution.

Sprinks
 
Your help is greatly appreciated.

The first table will have the following fields

EENO Number
Name Text
Date Date/Time
ShdStrt Short Time
ShdEnd Short Time
Shd Lunch Short Time

The second table will have the first three fields again (linked in a
relationship), as well as

In Short Time
LOut Short Time
LIn Short Time
Out Short Time

I want to use a form to do the data entry of these last four fields into a
new table, containing the first 3 fields of table 1.

I'm new to creating forms and subforms, so all the help I can get is
appreciated.

Thanks again!!
 
OK.

I suspect this has been a struggle to implement because the tables as
designed are not normalized. Normalization is a set of rules for relational
databases that improve performance and minimize "workarounds". Here are some
of the rules:
- Fields should be atomic, i.e., broken down as far as possible. E.G., use
FName and LName rather than a single Name field. By the way, "Name" is a
reserved word in Access, being a property of a control. Naming fields with
reserved words can lead to perplexing bugs in your application's performance.
You can get a full list by a Google search on "Access Reserved Words".
- Each record should have a unique identifier, or primary key (PK).
- The PK is a field or fields that uniquely identify the record.
- The PK should be short, stable, and simple--as little memory as possible,
that doesn't change, and easily understood. For this reason, I almost always
use an AutoNumber primary key rather than a compound key made up of more than
one field.
- Each field should supply additional information (or properties) of the
record that the PK identifies, e.g., each field of a record in an Orders
table should describe aspects of *that* order -- its order number, the
customernumber, the date.
- Data should, in general, be stored in a single table, e.g., the
CustomerName and his address, telephone, etc., should be defined in a single
record in the Customer table. To specify this customer in say, an Orders
table, include a numeric CustomerNumber field in the Orders table's
structure. DO NOT include his name, address, or any other fields from the
Customer table. Should a customer change their phone, or address, you make
one simple change in the Customer table.

I like to think about normalization with tables analogous to "things", and
fields analogous to "properties", or "traits". Each field is a unique
property of this particular thing.

As far as relationships between different tables, there are 3 types:
1-to-1, 1-to-many, many-to-many. The latter cannot be described in Access
directly, it must be simulated by two 1-to-many relationships. 1-to-many is
the most typical--a Customer can place many orders, etc.

If you find that your structure is in a 1-to-1 relationship, 99% of the
time, it would be far simpler to simply include the fields from the second
table in the first. This is what I would suggest here--move the In, LOut,
LIn, and Out fields to the first table. Further, following the normalization
rules above, you should remove the Name field from the table's structure, and
include it only in an Employees table, preferably splitting it into the first
and last name.

My personal preference, and that of many developers, would be to add an
Autonumber field to your table, and make that the primary key, rather than
the compound one of EENO and Date. If you do this, and yet wish to prevent a
user from adding a second record for an employee for a given date, you can
add a unique compound index in table design view (see help under Index for a
step-by-step guide) that will prevent this.

If you wish, even though the data is being stored in the same table, create
separate forms for scheduling and for posting the actual work times.

HTH
Sprinks
 
I want to use a form to do the data entry of these last four fields into a
new table, containing the first 3 fields of table 1.
Click to expand...

Umm... No. You *really* don't want to store these fields redundantly
in a second table!

Store them ONCE, and once only, in Table1. Store only the times in
Table2, using a Subform. If you need to see the name in conjunction
with the time, look at the name on the mainform and the times on the
subform; if you need to print a report, create a Query joining the two
tables. This is how relational databases work: each table has its own
data, and you create Queries linking the tables to bring data
together, rather than storing the same data in multiple places.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
The name is actually split into fname and lname, I just wanted to shorten the
post as much as possible. As far as having In, Lout, Lin, and Out in the same
table, I am not averse to that, as it will make my queries easier for me to
build. I just want to be able to select a date, and then be able to go
through the employee records by number order, and input the actual times
worked. I probably could have made this easier on everyone, but was trying to
be as simple as possible.

Again, my thanks for all the help provided.
 
Back
Top