Placing field within a form to select the table to input data

  • Thread starter Thread starter apdc711
  • Start date Start date
A

apdc711

I am building a database that has several tables, I have built a form that I
want the user to choose which table will be updated. Is it possible in Access
to have this option? Example:

Master table for a summary of time sheets for a year, then I have 26 pay
period tables so that each pay period can be inputed. Each pay period the
sups will go into the form and choose what pay period table they are entering
and the employee then fill out the information I have place in the form. I
just can not figure out how to allow them to choose what table ( Pay period)
they are entering into from a field, combo box, etc. in the form.

Or, should I create just one table and 26 pay periods for each employee? If
I do that, how do I create 26 pay periods within each employee with on column?

PLEASE HELP!!

Thanks,
Acpc
 
Based on your description ("26 pay period tables"), you have ... a
spreadsheet!

You might need to use separate worksheets for each pay period (or each
employee/pay period) if you were using a spreadsheet, but Access is a
relational database. You won't get the best use of Access'
features/functions if you insist on feeding it 'sheet data.

Consider turning off your PC and using paper/pencil to sketch out the
"entities" (the categories about which you want to keep information) and the
"relationships" (how the entities related to one another).

If "relational" and "normalization" are not yet in your vocabulary, expect
to face a bit of a learning curve before you can put a relational database
(Access) to good use.

Is there a reason you are NOT using something like Excel?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Follow-up...

Note that my previous response did not respond to the question you posed.
That was deliberate.

In Access, it all starts with the data. If you try to treat Access like a
spreadsheet on steroids, you'll pain yourself (not paint, pain) into a
corner that it will be painful to get out of...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
We currently have all our data stored in Excel. I wanted to go to Access
because it is less error.

Okay, let me start all over. I have a TimeSheet Form where a Supervisor
keys in the Employees time for that particular pay period. My Table (1)
contains these fields, Name, Supervisor, Regular Hours, OT Hours, etc... I
want to include a Pay Period combo box of some sort so when the Supervisor
goes into the form, they can select the 26 pay periods for that year and then
when the data is entered, it goes into the table for that specific pay
period. Does that make sense? I am an amatuer in access and was wondering
if that is possible.
 
Again, it all starts with the data...

Even though the data is currently available in Excel, there is no reason
your Access database has to store the data with the same design/structure.

Access is not, inherently, less error prone than Excel.

Access will require TWO rather steep learning curves: 1) understanding
relational, well-normalized database structure, and 2) learning how to use
Access.

One more time ... are you quite certain that you have the time and energy to
come up to speed using Access, rather than relying on what you already use,
Excel? I'm not trying to discourage you from using Access, but I am asking
you to consider the actual cost of doing so...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Even though the data is currently available in Excel, there is no reason
your Access database has to store the data with the same design/structure.

Access is not, inherently, less error prone than Excel.

Access will require TWO rather steep learning curves: 1) understanding
relational, well-normalized database structure, and 2) learning how to use
Access.
Click to expand...

Are you thinking in terms of porting the data to Jet but you said
'Access' so as not to confuse the OP?

If you are not thinking of a Jet port and instead working with the
Excel data from within Access then I broadly agree your "Access is
not, inherently, less error prone than Excel" assertion.

If you *are* thinking of a Jet port then I don't agree.

Let's put the (not so small) issue of normalization aside for the
moment. If the data with the same design/structure was ported to Jet,
would there be any immediate benefits which we could attribute to
things being "less error prone"? I say, yes.

Data typing and data validation are very poor in Excel (last time I
tried you could circumvent these mechanisms by simply pasting from the
clipboard), whereas in Jet exhibits strong data typing and its CHECK
constraints facilitate very rich data validation.

Jet has data integrity functionality (UNIQUE CONSTRAINTS, NOT NULL,
DEFAULT, FOREIGN KEY, etc) out of the box, but the same must be done
'by hand' in Excel (cell formulas, protecting cells, VBA, etc).

I'm sure I could think of more examples but do you see what I mean?

Jamie.

--
 
Back
Top