very new to access and need major help

[thisgirlneedshelp]

I really need some help in terms that I can actually understand.

I have never set up a database before and have been given the task of
creating one using access to make report details easily accessable to my
supervisers.

They have a bunch of reports that need to be organized in a way that anyone
can pull specific information from these in a quick and easy fashion. ie. If
they want to see all the reports that have been completed in 2003 and 2007,
including the project name and cost associated. I am to use access but I am
having some major issues with trying to figure out how it works.

So far I have set up several tables that have specific details of each
category within each table ie. Table = Project Partners & Details in table =
Name of Individual or group, partner type, contact person, contact number.
Table = Cost & Details in table = Type of cost, funding source, year of cost,
cost amount. This is as far as I could get and dont even know if this is the
right way to go about setting up the databases that I need. I am constantly
told by the access program that I do not have the right kinds of
"relationships" created but I can not figure out how to create these
relationships properly

Is there any way to set up a database with all the project information and
details available for every project completed by having all the fields in
every table related together so that anyone can access any of the desired
details they are interested in from the tables I have created? hope this
makes sense

I would really appreciate any help or information anyone can offer to help
me sort this out! Please and thankyou!

 

[John W. Vinson]

Is there any way to set up a database with all the project information and
details available for every project completed by having all the fields in
every table related together so that anyone can access any of the desired
details they are interested in from the tables I have created? hope this
makes sense

Yes... if your tables are set up correctly and if you have climbed the rather
steep and rocky learning curve for Access. :-{(

What you're describing is a pretty substantial project. If you don't
understand relationships, normalization, forms and subforms, and queries,
you're going to be hacking through some rough underbrush for a while!

Check out some of the tutorials and introductions here:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

You may want to see if there is budget to hire someone with Access experience
to help. Be sure you get someone who will "teach you to fish" rather than
"give you a fish" though.

John W. Vinson [MVP]

 

[akphidelt]

Haha, you are asking for quite a bit there.

But some key things you might want to research

Joins- These allow you to create queries with multiple tables

Total Queries- These allow you to group similar criteria and sum values to
get totals

Using Forms to create criteria for your queries- So if you don't want canned
reports this allows people to pick and choose information they want on the
report

Grouping in Reports- Theres tons of options here that can make reports very
easy to read, understand, and determine values of groups and the reports as a
whole.

Pretty much you are touching on a lot of different areas in access that
takes a little while to get used to.

 

[thisgirlneedshelp]

Thank you for the post!

Unfortunately there is no possibility of hiring someone to do this for me
but I think I have figured out that I did not set up my tables properly
because I had no common fields in which I could link tables together by.

My next question is...would it be better to link all my table to an
indicator table that has a specific project identification number or to link
the project id number into every different table (or do I have to do both)?.
For several of the projects there is more than 1 contractor hired or type of
equiptment used so I need to know which way would be easiest to link multiple
records within a field of a table to one specific project. (I am using a
basic Access training manual and it shows me an example that has two tables,
one with client information and the other with trainer information which are
linked together using a trainer number and client id. It demonstrates how to
link the two tables together but does not show how to make this link if a
client has more than one trainer.) I think in essence this is what I need to
do with my data.

If you have any suggestions it would be appreciated!
Many thanks

 

[John W. Vinson]

Thank you for the post!

Unfortunately there is no possibility of hiring someone to do this for me
but I think I have figured out that I did not set up my tables properly
because I had no common fields in which I could link tables together by.

My next question is...would it be better to link all my table to an
indicator table that has a specific project identification number or to link
the project id number into every different table (or do I have to do both)?.

Either, neither, or both.

You first need to identify what kinds of "Entities" - real-life people,
things, or events - you are dealing with. A Project is an entity; a Contractor
is another kind of entity; a piece of equipment is yet another. Each kind of
Entity will have its own table; the table will have fields for the different
"Attributes" of the entity. For example, a Project might have attributes such
as the Projecttitle, Startdate, etc.; a Contractor will have a name (more
likely a FirstName and LastName), contact information, etc.

For several of the projects there is more than 1 contractor hired or type of
equiptment used so I need to know which way would be easiest to link multiple
records within a field of a table to one specific project. (I am using a
basic Access training manual and it shows me an example that has two tables,
one with client information and the other with trainer information which are
linked together using a trainer number and client id. It demonstrates how to
link the two tables together but does not show how to make this link if a
client has more than one trainer.) I think in essence this is what I need to
do with my data.

If you have a many to many relationship - e.g. each Project may use more than
one Contractor and any one contractor can work on one or more Projects - you
need *ANOTHER TABLE*, because the relationship between a project and a
contractor is now an entity in its own right! The ProjectContractors table
would have a ProjectID as a link to the Projects table (and no other project
information!), a ContractorID as a link to Contractors (ditto), and any
information about *this* contractor with regard to *this* project (start date,
end date, role, comments, etc.

Therefore... you would NOT put the ProjectID into the Contractors table. A
Contractor is not part of "a project" - an individual contractor might work on
*several different* projects, so it would be inappropriate to consider a
project as being a proper attribute of a Contractor (or vice versa, for that
matter). Each table should have its own Primary Key, which uniquely identifies
one particular entity of that class of entities (e.g. ContractorID 318
uniquely identifies the contractor Ames Construction Co.); any table related
directly to the contractors table (ProjectContractors say) would have a
"foreign key" of the same datatype, distinct from that table's primary key, as
a link to that table.

You'ld use the same logic with equipment. If you have a bulldozer, or a
laptop, or a #2 pencil which is used on several projects, and a given project
uses a dozen #2 pencils, you have another many to many relationship... and
another Entity and another Table, EquipmentUsed.

Hope this isn't just adding to the confusion!

 

[thisgirlneedshelp]

I think I am finally starting to understand, although I have more questions
and am bound to have many more as I go through this process.

I have set up many tables as you described in your last posting, setting up
an additional table for each entity so I can relate my tables together
properly; however, you mention a many to many type relationship and my
relationships appear to all be one to many and I do not know how to change
this. Also I am confused as to which join type I am suppose to have.
Ususally there is one project with more than one contractor (or staff member,
or equitpment type), however each contractor (ect...) may be hired for more
than one project. Is it ok to just have three tables to relate this
information or do I need 4. (with the 4 being: 1 for the project details, 1
for the contractor details, 1 relating the different contractors hired for a
single project and 1 relating the number of projects that each contractor has
been hired for) Which type of relationship/join type would be needed for each
table to properly relate them together?

I am incredibly greatful for your help!

 

[John W. Vinson]

I have set up many tables as you described in your last posting, setting up
an additional table for each entity so I can relate my tables together
properly; however, you mention a many to many type relationship and my
relationships appear to all be one to many and I do not know how to change
this.

Sorry... that was confusing!

A Many to Many relationship (in the real world) needs to be converted into two
one to many relationships within the database. That's what the third table
does: you relate Projects to Contractors by relating Projects one-to-many to
ProjectContractors, and Contractors also one-to-many to ProjectContractors.

Also I am confused as to which join type I am suppose to have.
Ususally there is one project with more than one contractor (or staff member,
or equitpment type), however each contractor (ect...) may be hired for more
than one project. Is it ok to just have three tables to relate this
information or do I need 4. (with the 4 being: 1 for the project details, 1
for the contractor details, 1 relating the different contractors hired for a
single project and 1 relating the number of projects that each contractor has
been hired for) Which type of relationship/join type would be needed for each
table to properly relate them together?

Well... if you're thinking that you need to create One Great Master Query
joining everything together in order to edit your data, you don't. Typically
you would use Forms and Subforms instead - a Form for the "one" with a subform
for the "many". You will end up with more than four tables I'm sure.

But to relate Projects to Contractors you just need the ProjectContractor
table as described above. Each record of the ProjectContractor table defines
the relationship between a project and a contractor; so your two phrases "1
relating the different contractors hired for a single project and 1 relating
the number of projects that each contractor has been hired for" are actually
just two different ways of looking at the same table! For instance, you could
have a Form based on the Projects table, with a Subform based on
ProjectContractors, showing (via combo boxes storing the contractorID but
displaying the contractor name) all the contractors for that project, and a
different Form, based on Contractors, with a subform - also based on
ProjectContractors - showing all the projects that contractor has worked on.

If there's a one to many relationship from Projects to Projectdetails, that's
a different relationship. What's in ProjectDetails? Is a contractor related to
an entire project, or to a specific ProjectDetail? That's a decision you need
to make in constructing your logical model of the application.

 

[thisgirlneedshelp]

Again thanks for your posting, I am slowly, with your assistace, beginning
to understand the complexity of this process.

Just to clear things up, with the join-types that can be created, I am given
three options and I am not sure which is the best one to use.

 

[John W. Vinson]

Just to clear things up, with the join-types that can be created, I am given
three options and I am not sure which is the best one to use.

Don't worry about it. Those settings really don't have any major effect; just
leave them as the default inner join. The setting doesn't do anything to the
data or how it's stored; the only thing that happens is that when you create a
new Query joining the two tables it will use whatever you have chosen as the
join type (and this can be changed in the query design anyway).

 

[thisgirlneedshelp]

Another question....

Now that I have set up the three tables to relate the contractors to the
project how would I go about creating the appropriate relationship that would
be needed if I need to link multiple records into a field within the
contractors table (ie Switching from our contractor/project relationship...I
have a different relationship where a project partner table was created and
related to the project title table (the same way I did it with the
project-contractor tables as we have been discussing); however, within the
project partner table there needs to be a "funding type" field (as well as
others) with multiple different records available to choose from (ie.
landowner, first nations, funding, ect). Do I need to set up a new table to
relate these different records back to the funding type field or can I do
this within the project partner field somehow without affecting the first
relationship that I built between the project title table and the partner
table?

I hope this makes sense....it is so complicated and confusing to me but with
your help I am somewhat making sense of it all...thank you so much for your
time

 

[thisgirlneedshelp]

Another question....

Now that I have set up the three tables to relate the contractors to the
project how would I go about creating the appropriate relationship that would
be needed if I need to link multiple records into a field within the
contractors table (ie Switching from our contractor/project relationship...I
have a different relationship where a project partner table was created and
related to the project title table (the same way I did it with the
project-contractor tables as we have been discussing); however, within the
project partner table there needs to be a "funding type" field (as well as
others) with multiple different records available to choose from (ie.
landowner, first nations, funding, ect). Do I need to set up a new table to
relate these different records back to the funding type field or can I do
this within the project partner field somehow without affecting the first
relationship that I built between the project title table and the partner
table?

I hope this makes sense....it is so complicated and confusing to me but with
your help I am somewhat making sense of it all...thank you so much for your
time