how to go about this projetc?

[splashout1]

Hi,

I have been directed here as the guys in the query group couldn't help.

I am thinking of using access to produce a system to allow production
members to look up any record of data from any job that has been
produced to allow them to check that the data is correct.

Each job we produce is data driven in that the client supplies the data
we then may or may not carry out any number of processes prior to this
data being used in the production of that job.

As such the data format that will be going into this database will have
no standard format. The only things that will be common in each jobs
data is the fact that we will add our own id fields and job number to
each record of each job prior to it needing to be entered into the db.
This in affect will mean that every record from every job has a
completely unique key.

The aim of the db is to allow someone in production to be able to
search on job number and unique id and to have the corresponding record
returned in order to confirm that all data used from that record has
been produced correctly.

The issue i am struggling with is that every data file (i.e. job) could
be different how could i go about coming up with a system that will
allow the users in production to simply look up on job number and id to
find the record.

I have tried to make it as clear as possible but i am struggling with
this one.

Any help or pointers greatly received.
Glyn

 

[Jeff Boyce]

Glyn

Are you saying that each record you would keep would have nothing in common
with the next/previous? For example, job1 is fixing the carburetor in my
truck, job2 is finding a dozen roses for Valentines Day, job3 is sharpening
a pencil, ...

What are the common data elements you DO have?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

 

[splashout1]

Hi Jeff thanks for reading.

To some degree yes, each job has nothing in common with the next job
however each record in each job will be comparable to others in that
job and it is these records that need to be viewed, searchable by jobno
and urn.

The only guaranteed thing that will be common between the jobs will be
a jobNo field and a urn field.

each job can have any number of data records(rows) in it and any number
of fields.

i could get round it in a messy way by creating a seperate query for
each job(as a seperate table) and call that querry from code but i cant
help thinking there must be a cleaner way of doing this.

Thanks again
Glyn

 

[Amy Blankenship]

Hi,

I have been directed here as the guys in the query group couldn't help.

I am thinking of using access to produce a system to allow production
members to look up any record of data from any job that has been
produced to allow them to check that the data is correct.

Each job we produce is data driven in that the client supplies the data
we then may or may not carry out any number of processes prior to this
data being used in the production of that job.

Right there it sounds like you DO have something in common across jobs. All
of the jobs will have a number of requested processes, and you may or may
not perform the processes. So, as a start, you might want to try something
like this:

Jobs
JobID
ClientID
JobDesc
IsComplete

Clients
ClientID
ClientName
ClientAddr1
ClientAddr2
City
State
Zip
Phone
etc.

If you commonly have processes that you do often

Processes
ProcessID
ProcessDesc
etc.

If you use the Processes Table

JobProcesses
JobID
ProcessID
IsDone (or DateDone)

If you don't use the Processes Table
JobProcesses
JobID
ProcessDesc
IsDone (Or DateDone)

HTH;

Amy

 

[splashout1]

Hi Amy thanks for taking more of your time.

I think that is a start, but it falls down slightly on the fact that
what the db will be holding is not data about a job but the data that
is used in that job.

For example job1 may be a reports based job and the data used to drive
that job may have many fields of reporting data lets say 10 fields.

while job 2 may be a simple job that just uses a number and therefore
this data would only be one field.

we add the job number and a urn to every job and therefore these fields
would be common.

So yes you could split the job no data with who the client is etc into
one table, but where do you go from there as every job(i.e. data from
that job) could have a different format.

Thanks again
Glyn

 

[Amy Blankenship]

Nice that you're finally starting to volunteer actual data instead of making
us drag it out of you.

It's not that difficult. Just do it like the process table. Depending on
how your workflow work (hint: some more volunteering of information would
have saved me some typing and lessened the chance I'd give up on you before
your question is fully answered), it would work one of two ways:

If the data is tied to the Process:

Add a JobProcessID field to the JobProcess table, then add a JobProcessData
table
JobProcessDataID
JobProcessID
DataDesc
DataValue

Again, if you have certain data items that are consistently repeated, you'd
want to pull that out to a separate table.

If the data is tied to the job and has nothing to do with the process:
JobData table
JobID
DataID
DataDesc
DataValue

As I said, it's almost never impossible to normalize the data, but you have
to step back and look at the data objectively and determine where
generalizations can be made.

HTH;

Amy

 

[splashout1]

Hi Amy,

Thanks again for your time i am moving in the right direction just one
issue left to over come appologies if you think i am holding back info
it is not intentional.

How when each jobs data can have a diffefrent number of fields that
could be different things eg number text etc will you ever get that in
one table or multiple tables that can be serched as one?

i can see what your saying about splitting out the job details etc but
surely i will still end up with a seperate table for each jobs actual
data due to the different formats of each job?

so how would you go about combining this through a querry or something
to allow all jobs to be searched?

i have it working in the messy way with an individual querry setup for
each table and then calling this through code using a combo box on the
form that looksup the job numbers that the user selects. I would just
like it cleaner if there is a way and being a novice this is testing me
a bit.

Thanks
Glyn

 

[Amy Blankenship]

Hi Amy,

Thanks again for your time i am moving in the right direction just one
issue left to over come appologies if you think i am holding back info
it is not intentional.

How when each jobs data can have a diffefrent number of fields that
could be different things eg number text etc will you ever get that in
one table or multiple tables that can be serched as one?

You don't. The data design I showed you makes each different data object a
record, not a column. That's what normalization is about. When you find
yourself wanting multiple varying columns, you can bet you need to have them
as separate records. You can have as few or as many records as you need,
which is not the case for columns.

Unless you need to do math or something with the data in the data table, you
shouldn't need to worry about the data type (i.e. text should work fine for
whatever you need unless you're expecting essays in which case you should
move to a memo data type). From what you've said about your data, it would
seem that the data for each job is different so you're not likely to want to
do math with it.

i can see what your saying about splitting out the job details etc but
surely i will still end up with a seperate table for each jobs actual
data due to the different formats of each job?

If
1) You are determined that is the only way that will work for you
2) I cannot convince you otherwise
3) You don't hire professional help to do it right

You will indeed wind up with separate tables for each job. I've given you a
very sound design that fits what you've said about your requirements, but if
you're not able to envision how it can be put into service, it's not likely
to benefit you.

so how would you go about combining this through a querry or something
to allow all jobs to be searched?

It depends on what you're doing with the records and which design (as I gave
you several variations) you went with.

i have it working in the messy way with an individual querry setup for
each table and then calling this through code using a combo box on the
form that looksup the job numbers that the user selects. I would just
like it cleaner if there is a way and being a novice this is testing me
a bit.

I thought you said all of this information would be entered by hand without
any forms. Regardless, if what you want to do is provide a combobox with
all the job numbers it is very clean since all the jobs are in one table.

Now if, for instance, your processes and data are related, where each
process can have 1-n amounts of data, then you could easily embed a subform
for processes in the Job form (if your relationships are set up properly all
the links between JobID in the Job table and JobID in the processes table
will automatically just work). That subform would allow you to add as many
or as few processes as you wanted. And then in that subform you would embed
a subform for data which you could put as many or as few data records for
each process as you needed.

Your questions lead me to believe that your earlier statements that you
understand normalization are not as true as maybe you believed they were
when you said it. I have always found this article to describe that process
very well
http://www.webmonkey.com//templates/print_template.htmlt?meta=/webmonkey/99/13/index1a_meta.html.

HTH;

Amy