Complicated Query?

  • Thread starter Thread starter hntsignif
  • Start date Start date
H

hntsignif

I have a dbs with the following fields:
ProjMgr
ProjLead
ProjDesg
ProjEng
ProjQA
TeamM1
TeamM2
TeamM3
TeamM4
TeamM5
TeamM6
TeamM7
TeamM8

An individual may appear in any of these fields once per job.

My manager would like me to run a report by individual listing any jobs that
they are on.

Joe may be ProjLead on one job and TeamM7 on another. I need to find all
instances where joe appears and run a report. I have a report running if
they choose the individual they want (ie joe).

Now I need a report that shows everyone and their jobs, not just one person.

I am running into a mental roadblock and could use some help.
 
If I'm understanding your table structure (I assume you mean your table has
those fields), you don't have a relational database, you have a spreadsheet!

The reasons you may be having difficulty figuring out how to get Access to
do what you want is because Access is optimized for relational data, not
'sheet data.

If you have a history with Excel, I'm sorry, but you'll have to unlearn some
things to make the best use of Access' tools.

Each of those fields appears to be a role/category. In a well-normalized
relational database, you'd use ONE field to hold the category, another to
hold the PersonID (pointing back to a Person table), and a third to hold a
ProjectID (pointing to a Project table). You'd probably also have other
fields related to one person performing in one role on one project.

Until your data better fits a relational database model, both you and Access
will be struggling to come up with work-arounds to deal with 'sheet data.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Your problem is that the table is set up completely wrong for your needs.

You should have a table of Jobs. You should also have a table of individuals
that we will call Employees. Both of these tables should have a primary key.

Between them you have a table of Positions which would look something like
below:

JobID EmpID Position
1 1 ProjMgr
1 2 ProjLead
1 3 TeamM2
2 3 TeamM2
2 5 TeamM5

With a setup like the above with all the tables joined, you could tell all
kinds of things like what Employees are on a particular Job. Who are all the
ProjMgrs and for what Jobs. What Jobs an Employee is on.

You may even need a 4th table with the Position titles and possible a 5th
table if knowing what Team a person is on is also important.
 
I have this in two tables at the moment and may be doing it wrong.

I have an employee table with all of the employees listed and the project
table that has project dates, names and information.

The fields I listed are in the project table but draw from the employee list.

Is this more like what you guys were thinking?
 
You know much better than we do what your situation is.

Are you saying that you have Employees, Projects, and
Employees-Assigned-to-Projects?

If so, you need THREE tables.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Right now I am only 40+ entries into the dbs so changes are easy at this
point.

On the advise of Jerry above, I already have a table called employee that
has the employee list that the project table pulls from. I have added a
position table with the positions and have created a relationship between
them using the ID primary key of each.

Currently the Project table stores the employee name into the columns that I
had listed. I am guessing that I need to change those columns to draw from
the new positions table.

Where do I go from there?
 
Where do I go from there?

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez. It will save you many, many
hours of grief.
 
Where do I go from there?
Click to expand...

Learn about normalization and relational design.

Here's some good places to start: the tutorials at the end of the list would
be the first things to check.

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

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

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