Need help with my database

[Adam]

i think i may be making things over complicated and need to know if there is
a simpler way of doing things compared to the way i am doing them

It is a client database for a recruitment company, the database holds
details of clients and their work experience and work preferences..

the users will need to generate reports based on work preference i have done
this using queries and generated new table info according to the results
generated from the query.

please contact me and i can send you the DB - this site is restricted by
opendns so things like megaupload and file sharing are not allowed.

can someone have a look for me and advise me ??
ont worry i have put fake info in so your not looking at someones real
personal details.

please contact me and i can send you the DB - this site is restricted by
opendns so things like megaupload and file sharing are not allowed.

 

[Beetle]

It's unlikely that you are going to find someone in the group willing
to accept a file from an unknown source. If you post some information
about your sructure/problem/question someone may be able to offer
advice as to whether your current approach is correct, or what the
correct approach might be.

All we have right now is a vague overview of the general purpose
of your application.

 

[Adam]

yah i kinda figured that i can post scren shots etc but basically i have a
main table with details such as "surname" "first name" address etc all the
usual personal details with some extra info as true/false fields dictating
what their preference of work is EG. admin mechanics outdoor work etc etc

EG
First Name
Surname Text
Address Text
Post Code Text
Contact Tel Number
NINO Text
Work Ready True/False
Add_Info Memo
admin True/False
warehouse True/False
leisure True/False
etc etc

I was thinking about using OCR documents this is why i have several fields
with true/ false

currently i am using append quesries to extract information from this table
matching "work_Ready = true" and "[work_type] = true" (used for validation of
checkboxes) and output this information to a report so the users can print
reports according to the type of work the client is looking for - i know this
wont make a lot of sense but bear with me.


but this seems like a very long way round approach to the solution.

do i have to generate seperate tables and reports for each area of work or
is there a simple way of extracting this information - as when i get to OCR
input and the sheet of a4 has like 100 jobs on i dont really want to be
making 100 fields and 100 queries for those fields etc

i know my way around access quite well but the finer point of things stump
me just because i have had no experience doing what i am doing now.

 

[Beetle]

There is a very good explanation of why not to use your current
approach, along with examples of the correct approach, at the
following link;

allenbrowne.com/casu-23.html

I woul also suggest you bookmark Allen Browne's site. It has a wealth
of helpful Access information.

 

[CraigH]

Hi,
I may be confused about what you are doing with the OCR (hope that means
Optical Characer Reading), but my 2 cents worth of what I am getting from
what you said.

If you are getting a OCR file I assume it is going to be in some text
format where you are reading in the information i.e.:

either like a talbe
First name Phone WillWorkAdmin Outdoors
Craig 999 True False
Dave 888....

Or if you have other data below
Like
First name Phone WillWorkAdmin Factory
Craig 999 True False
Jobs Worked Type Where
Admin OH
Facory NY
You could code the import of the files

I won't go into exact coding until I am sure what you are doing but:

If it is a simple table like the first example and you know how it is
seperated, you populate the 'Persons' Table with the name and phone number
- get the ID for that person
then in the tables you are useing put the id of the person in that table
i.e.
tblTypeOfWork
TOWID 1 2 ...
TOWName Admin Factory ...

tblPersonsWork
PersonID 1(Craig)
TOWID 1

you code is similer to
If I am at Column 3 ' will work Admin
If value = True
rstPersonsWork!PersonID = lngPersonWorkingOn
rstPersonsWork!TOWID = 1

But lets step back becasue I've re-Reread some of what you are saying. You
have said you are "thinking about using OCR", hopefully that means you
haven't started.

So the first thing is what type of data are you trying to gather and in
conjunction from who and how?

Make your tables known - People, TypesOfWork (and the connecting table),
TypesOfConditions (what types of conditions will someone work under [and
connecting table]) etc...

Then from who and how - is it the exact person? is it something that they
will fill out (if you have 2007 they have a new way to email a person and get
the information.

I actually am willing to get a file, and I am interested in what you are
trying to do. Right now I do not have a throw away email address but if you
want to give yours (TempATtempDOTcom) in the response then contact you.

Craig

yah i kinda figured that i can post scren shots etc but basically i have a
main table with details such as "surname" "first name" address etc all the
usual personal details with some extra info as true/false fields dictating
what their preference of work is EG. admin mechanics outdoor work etc etc

EG
First Name
Surname Text
Address Text
Post Code Text
Contact Tel Number
NINO Text
Work Ready True/False
Add_Info Memo
admin True/False
warehouse True/False
leisure True/False
etc etc

I was thinking about using OCR documents this is why i have several fields
with true/ false

currently i am using append quesries to extract information from this table
matching "work_Ready = true" and "[work_type] = true" (used for validation of
checkboxes) and output this information to a report so the users can print
reports according to the type of work the client is looking for - i know this
wont make a lot of sense but bear with me.


but this seems like a very long way round approach to the solution.

do i have to generate seperate tables and reports for each area of work or
is there a simple way of extracting this information - as when i get to OCR
input and the sheet of a4 has like 100 jobs on i dont really want to be
making 100 fields and 100 queries for those fields etc

i know my way around access quite well but the finer point of things stump
me just because i have had no experience doing what i am doing now.

 

[Adam]

sure contact me on (e-mail address removed) - i dont mind that being
used as a public email i get enough crap thro it anyway lol

Hi,
I may be confused about what you are doing with the OCR (hope that means
Optical Characer Reading), but my 2 cents worth of what I am getting from
what you said.

If you are getting a OCR file I assume it is going to be in some text
format where you are reading in the information i.e.:

either like a talbe
First name Phone WillWorkAdmin Outdoors
Craig 999 True False
Dave 888....

Or if you have other data below
Like
First name Phone WillWorkAdmin Factory
Craig 999 True False
Jobs Worked Type Where
Admin OH
Facory NY
You could code the import of the files

I won't go into exact coding until I am sure what you are doing but:

If it is a simple table like the first example and you know how it is
seperated, you populate the 'Persons' Table with the name and phone number
- get the ID for that person
then in the tables you are useing put the id of the person in that table
i.e.
tblTypeOfWork
TOWID 1 2 ...
TOWName Admin Factory ...

tblPersonsWork
PersonID 1(Craig)
TOWID 1

you code is similer to
If I am at Column 3 ' will work Admin
If value = True
rstPersonsWork!PersonID = lngPersonWorkingOn
rstPersonsWork!TOWID = 1

But lets step back becasue I've re-Reread some of what you are saying. You
have said you are "thinking about using OCR", hopefully that means you
haven't started.

So the first thing is what type of data are you trying to gather and in
conjunction from who and how?

Make your tables known - People, TypesOfWork (and the connecting table),
TypesOfConditions (what types of conditions will someone work under [and
connecting table]) etc...

Then from who and how - is it the exact person? is it something that they
will fill out (if you have 2007 they have a new way to email a person and get
the information.

I actually am willing to get a file, and I am interested in what you are
trying to do. Right now I do not have a throw away email address but if you
want to give yours (TempATtempDOTcom) in the response then contact you.

Craig

yah i kinda figured that i can post scren shots etc but basically i have a
main table with details such as "surname" "first name" address etc all the
usual personal details with some extra info as true/false fields dictating
what their preference of work is EG. admin mechanics outdoor work etc etc

EG
First Name
Surname Text
Address Text
Post Code Text
Contact Tel Number
NINO Text
Work Ready True/False
Add_Info Memo
admin True/False
warehouse True/False
leisure True/False
etc etc

I was thinking about using OCR documents this is why i have several fields
with true/ false

currently i am using append quesries to extract information from this table
matching "work_Ready = true" and "[work_type] = true" (used for validation of
checkboxes) and output this information to a report so the users can print
reports according to the type of work the client is looking for - i know this
wont make a lot of sense but bear with me.


but this seems like a very long way round approach to the solution.

do i have to generate seperate tables and reports for each area of work or
is there a simple way of extracting this information - as when i get to OCR
input and the sheet of a4 has like 100 jobs on i dont really want to be
making 100 fields and 100 queries for those fields etc

i know my way around access quite well but the finer point of things stump
me just because i have had no experience doing what i am doing now.

 

[Adam]

Many many thanks. this seems to be the exact solution i was looking for


Regards.

There is a very good explanation of why not to use your current
approach, along with examples of the correct approach, at the
following link;

allenbrowne.com/casu-23.html

I woul also suggest you bookmark Allen Browne's site. It has a wealth
of helpful Access information.
--
_________

Sean Bailey

yah i kinda figured that i can post scren shots etc but basically i have a
main table with details such as "surname" "first name" address etc all the
usual personal details with some extra info as true/false fields dictating
what their preference of work is EG. admin mechanics outdoor work etc etc

EG
First Name
Surname Text
Address Text
Post Code Text
Contact Tel Number
NINO Text
Work Ready True/False
Add_Info Memo
admin True/False
warehouse True/False
leisure True/False
etc etc

I was thinking about using OCR documents this is why i have several fields
with true/ false

currently i am using append quesries to extract information from this table
matching "work_Ready = true" and "[work_type] = true" (used for validation of
checkboxes) and output this information to a report so the users can print
reports according to the type of work the client is looking for - i know this
wont make a lot of sense but bear with me.


but this seems like a very long way round approach to the solution.

do i have to generate seperate tables and reports for each area of work or
is there a simple way of extracting this information - as when i get to OCR
input and the sheet of a4 has like 100 jobs on i dont really want to be
making 100 fields and 100 queries for those fields etc

i know my way around access quite well but the finer point of things stump
me just because i have had no experience doing what i am doing now.