By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,813 Members | 2,269 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,813 IT Pros & Developers. It's quick & easy.

Table Setup

P: n/a
I am setting up a DB for our company and am at a loss on the best way
to handle some of the records.

I have a table (tblJobs) that houses the basic info on the job (when,
where, what, who). These jobs are assigned a job number based on the
year and autonumber (05-3589). However, sometimes these jobs spawn new
jobs that have to keep the original job number and add an alphabet
(05-3589A). I have another table (tblTime) that houses the surveyor
time/expense on each job. Do I have to have a separate time table for
main and sub jobs? I don't really want to have to do unions because it
limits the data entry. Is there another way to go about this?

I am using Access 2003 on a NT2000 server.

Help!

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Seems to me the easiest way would be to have two fields in tblJobs.
The Job # field is the primary (i.e. 05-3589) and secondary job field
would be each job attached to the primary job.

For example:
Job Subjob
05-3589 05-3589
05-3589 05-3589A
05-3589 05-3589B

I know this goes against relational table structure, but you don't
always have to create related tables if it complicates matters.

That way you could link the time to the Job field and it would apply to
all subjobs. That is if the time applies to all jobs related to the
main job #. All you have to do is design your queries or filters based
on the main job # and it will apply to all subjobs.

Does that make sense?

Nov 13 '05 #2

P: n/a
or

tblJobs tblTimes
jobId jobId
parentJobId ....
....

so that you can now get the time spent on a job, or the time spent on
all jobs within a parent job

think of it as 'employees' and 'supervisors'... supervisors are
employees too

Nov 13 '05 #3

P: n/a
"Ozzone" <oz**********@yahoo.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
Seems to me the easiest way would be to have two fields in tblJobs.
The Job # field is the primary (i.e. 05-3589) and secondary job field
would be each job attached to the primary job.

For example:
Job Subjob
05-3589 05-3589
05-3589 05-3589A
05-3589 05-3589B

I know this goes against relational table structure, but you don't
always have to create related tables if it complicates matters.

That way you could link the time to the Job field and it would apply to
all subjobs. That is if the time applies to all jobs related to the
main job #. All you have to do is design your queries or filters based
on the main job # and it will apply to all subjobs.

Does that make sense?

I would do similar, but perhaps not take on the responsibility of making
sure the job and subjob matched - there's always the danger that something
could get out of sync. You could have Subjob simply as a single-character
text field A-Z but allow nulls. Or, without allowing nulls, use an integer
with a default value of zero and validation so it must be between 0 and 26.
On the form show 0 as "", 1 as "A", 2 as "B", etc.
Nov 13 '05 #4

P: n/a
True Justin. I was using the whole ID simply as an example. But
shouldn't matter what the child id is as long as the parent id is the
same.

Nov 13 '05 #5

P: n/a
but if someday you want a job to become a subjob in a bigger job (or
vice versa), your letter / number approach will fail

Nov 13 '05 #6

P: n/a
<le*********@natpro.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
but if someday you want a job to become a subjob in a bigger job (or
vice versa), your letter / number approach will fail


Judging by the OP's question, I doubt the need to have the ParentID
approach. Although it can provide the ability to describe 'a job of a job
of job of a job, etc' that is just the table design and at some point you
come to the practicalities of forms, reports, searching and updating the
data.
I once took on a database where someone had gone about describing products
in a hierarchical table structure and after nesting tables 5 levels deep, he
was asked to produce some fairly basic reports. He was off sick with
stress - initially for a week but he never returned.
After looking at how they needed to analyse their products and sales, I took
the table structure (which looked like a mad woman's breakfast) and returned
to a single products table which contained a product mask field. By
agreeing a masking policy they could express all sorts of structures with
this one field.
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.