timesheet question - (Jeff)

[Guest]

Jeff,

I was able to make the subform and this isn't exactly what I am needing.
While it does away with the duplication of fields, it doesn't fit the
exercise.

The employee timesheet is a separate record, we are not planning to
including it in the main database at the present time. What I am attempting
to do is get a table entry that will verify the job number, name, client, and
pm. (pull from the job_list and put it in timesheet), the employee can then
enter the date, hours, task code, and work description.

the main form (job_list) and have a subform linked would be a great idea if
it populated a table that would have the information completely filled out.
ie, job no, job name, client, pm, date, hours, task code, work description.
the way I did the subform it was not creating a new table or populating the
existing time table, but creating secondary records (don't have a clue where
they were being sent to) for each individual job... and that's not what I am
looking for.

this has been fun digging, but at the same time frustrating because I didn't
think that Access would be that difficult for a seemingly simple task.

 

[Jeff Boyce]

I didn't see a question in there, but I did observe something. It sounds
like you are still trying to record the same fact in more than one table.
Don't. Access is a relational database, and you not only don't need to, but
should, for efficiency and synchronization.

Instead, create a query that connects the tables that, together, have the
data you want to see. Use the query, perhaps via a form or a report, to
display it.

Or have I misinterpreted?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

I think I am not being very clear on what I am attempting...

I am trying to grab information from one database, the project_info file and
moving information into a new database - the timesheet, two separate files
(databases).

I understand the relational part of this exercise but then the end result is
not what I am looking for. If I write the query/form/combo box, how do I keep
the information in a format that the end user will understand? Maybe by using
a tabular type subform?

Thanks for being patient with me. I am overthinking this thing and at the
same time trying to make it as simple as possible.

 

[Jeff Boyce]

I don't understand why you think it is necessary to have the data in two
places ("a new database").

I don't understand why you think a query & form combination won't let you
show data the way you want. What is it about the display requirements that
you feel will not be achievable?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

I began the project with each employee putting their time in individual
databases. The owner prefers to continue this process. All I am trying to do
is create a functional way for the employee to retrieve job information
through a linked file. The employee is currently inputting their time
directly into the table (not through forms). If they have already charged to
a job, they normally cut and paste a new entry. This is not a good way to do
business because if they put the wrong job number, name or client, then the
report does not compile the information correctly.

I think that I can reprogram a form to show the timesheet in a form that the
employee will understand, but it still runs into how to verify the job
information in the form? Needless to say I am a bit over my head, but it's
becoming clearer as you feed information to me.. thanks for all your help so
far.

 

[Jeff Boyce]

Bob

I don't have a very clear picture of the physical layout of this system.
Are you saying that each person is doing data entry into a different
Microsoft Access .mdb file? Moreover, directly in the table?

Data entry/edit directly in the table is a dangerous practice, as Access
tables have extremely limited data validation mechanisms, and no Event
model. Access forms allow for much stronger validation and event handling.

If you can persuade the users to do their data entry in forms instead of the
tables, you could move the data to a shared back-end (I assume all these
folks work on the same Local Area Network?). You would create a "front-end"
(forms, queries, reports, everything but the tables) that is linked to the
back-end tables. Then you'd distribute a copy of the front-end to each
user's PC. This is also known as "splitting" the database.

Good luck!

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

I began the project with each employee putting their time in individual
databases. The owner prefers to continue this process. All I am trying to do
is create a functional way for the employee to retrieve job information
through a linked file. The employee is currently inputting their time
directly into the table (not through forms). If they have already charged to
a job, they normally cut and paste a new entry. This is not a good way to do
business because if they put the wrong job number, name or client, then the
report does not compile the information correctly.

I think that I can reprogram a form to show the timesheet in a form that the
employee will understand, but it still runs into how to verify the job
information in the form? Needless to say I am a bit over my head, but it's
becoming clearer as you feed information to me.. thanks for all your help so
far.

 

[Guest]

Thanks for the reply Jeff. In answer to your statement regarding the physical
layout, I will have a main database that will have client information, job
information, employee information, etc... The database that is separated is
the timesheet. I will be querying the main database for project number, name,
client name, and project manager. The rest of the information in the
timesheet is input from the employee, it is why I chose to separate (split)
the databases. There is no reason for the employee to go into the main
database.

I did as you suggested and have the project number, name, client name, and
project manager as the main form and have attached the timesheet as a subform
and it looks great... now my problem is getting the information from the main
form to the subform. That is for later next month, but right now I am having
to refurbish timesheets so that I can start pulling information from the
individual timesheets.

Again, thanks for all your help... I'm sure that I will be back for more
info...

 

[onedaywhen]

Access
tables have extremely limited data validation mechanisms

BIG misstatement! Jet tables have table-level CHECK constraints, which
are a very effective and powerful for data validation. There of course
remain some stuff better caught in the front end e.g. complex pattern
matching using VBScript Regular Expressions but basic pattern matching
using LIKE in Jet SQL can catch most things.

Putting data validation in just the (one) front end application is a
dangerous practice.

Jamie.

--

 

[Jeff Boyce]

Thanks, Jamie. Chalk my miss up to lack of experience using SQL to build
(?and ALTER?) tables and their CHECK constraints.

Is there also a mechanism via the GUI that allows for construction of
multiple CHECK constraints?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[onedaywhen]

Is there also a mechanism via the GUI that allows for construction of
multiple CHECK constraints?

Not really and it's top of my issues list:

http://groups.google.com/group/micr...2fd3ce3967/c711008cd57d4a36?#c711008cd57d4a36

Jamie.

--