Access Newbie- Help with querying and relationships?

[nickdawes28]

Hello!

I am creating an Access Database for work and through this forum, google and brief exposure to MS Access in college 7 years ago, I have managed to throw something together that I am reasonably happy with.

I have now reached a stage where I could use some advice from more experienced users- I understand what I want to achieve but I am not 100% what the best way to go about it may be. I will keep my post brief and give only information I think is relevant, however if more detail is required I will happily elaborate. Any help would be greatly appreciated!

My database is a straightforward equipment tracking system, consisting of 2 major table categories.

• tblJobs - Fields include details such as unique JobID, Client Name, Start Date etc.
• Equipment - A number of tables containing fields relating to equipment used on any given job.

Any given job can have multiple equipment on it, and any piece of equipment can be used on multiple jobs (this is by design, no need for a check-in/check-out setup).

I have set up the required relationships to make this work however I have recently tried to implement an additional feature and have not been successful.

Here is my problem:

I would like to introduce a 3rd major component to the database to enable me to assign equipment to staff members. I would then like to be able to assign staff members to a specific job, and have any equipment associated with that staff member assigned to the job also.

Again, initial post will be kept brief however I will include a screenshot of my current relationship setup and hopefully somebody can point out where I am going wrong.

(edit. Due to this being my first post I am unable to directly link to my screen shot. i59DOTtinypicDOTcom/2mwun1sDOTjpg

I would like to create a query using JobID as the criteria which returns all equipment associated with that job. This includes equipment 'assigned' to staff members.

In layman's (my) terms, I would like to create a record in tblStaffKit, assign equipment to that member of staff from the various equipment tables (e.g. tblRopes, tblHarnesses) and be able to query these records by JobID.

I look forward to hearing from you all!

Kind regards,
Nick

 

[CJ_London]

Without seeing your table relationships, it sounds from your description that your data is not normalised

Equipment - A number of tables containing fields relating to equipment used on any given job.
various equipment tables (e.g. tblRopes, tblHarnesses)

I would anticipate you would have only one table for equipment - perhaps with an additional column to indicate type -ropes, harnesses etc

Without normalisation, you will be making what you are trying to do that much more difficult

 

[nickdawes28]

Hi CJ, thanks for the reply. Please check the original post, a link to a screenshot of my tables and relationships was included, I replaced "."s with the word "DOT" to allow me to post a URL.

 

[CJ_London]

Understand, now had a look and like I said, you need to normalise your data.

You need to explain more - can jobs have kit issued to it that is not actually issued to staff? Or is all kit actually issued to staff who are then assigned to jobs

 

[nickdawes28]

Happy to elaborate, feel free to ask anything that will help you understand what I am trying to achieve.

Yes, equipment can be assigned to a job without being assigned to staff.

I had hoped to create a table (tblStaffKit) and basically "group" several items of equipment under one field (STAFF_NAME) and in assigning that field to a job, (tblStaffKit2Jobs) have all that equipment appear when querying JobIDs.

Eg,
John has Rope1, Harness2 and Helmet1 assigned to his name.
(Rope1, Harness2 and Helmet1 have "John" in their STAFF_NAME fields)

John is then assigned to Job1.
(tblStaffKit2Jobs has a record with Job1 under JOB_ID and John under STAFF_NAME)

I would then like to query the data to view ALL equipment that was used on Job1.

Hope this makes things a little clearer, but again, feel free to ask anything.

Thanks CJ

 

[CJ_London]

next question:

can kit be assigned to more than one job and/or person at the same time? I would assume not but just checking
Your relationships diagram does not show any dates in the link table - so is the assumption that kit issued to jobs is issued for the full period of the job?
Is there a similar situation for kit issued to staff?

 

[nickdawes28]

can kit be assigned to more than one job and/or person at the same time?

Equipment can be assigned to more than one job at a time. I'm confident this is how I want it to work, as the end goal of the system is to create reports detailing what equipment was used on particular jobs. To check equipment out to a job and require it to be checked back in before it can be assigned to another job is a feature that to me seems unnecessary for this system. tblJobs contains fields START_DATE and END_DATE, which are updated manually at the beginning and end of a job.

Your relationships diagram does not show any dates in the link table - so is the assumption that kit issued to jobs is issued for the full period of the job?
That's correct.

Is there a similar situation for kit issued to staff?
I think I have implemented this poorly. At present, equipment is 'assigned' to staff through an update query triggered on a form. A button on a form (based on tblStaffKit) prompts for the ID of the equipment you want to assign to a staff member, and then within that equipment's table updates the STAFF_NAME field.

Eg. to assign a harness to John...
I navigate to Johns record on a form based on tblStaffKit. I click "Assign Equipment" (multiple buttons for each type of equipment, spread over tab controls) and an update query runs on tblHarnesses with a prompt for HARNESS_ID as the criteria, 'Update To' set to "John" (through referencing a STAFF_NAME control on the form).

A similar button 'removes' the assignment by resetting the STAFF_NAME field to a default value.

Hope I was clear.

 

[CJ_London]

So to paraphrase what you want your query to do is

'display all kit issued to a job where the kit was issued directly to the job or the kit was issued to a member of staff who was assigned to the job'

I don't see how you can get away from using dates on the issuing side - if a member of staff works on three jobs over a period of time and has kit issued at different times, how do you decide which kit is applicable for any particular job?

 

[nickdawes28]

That's exactly right, thank you.

As for the dates, all I really need at this stage is a system capable of producing reports listing which equipment was used on a specific job- regardless of the reality that equipment may spend 2 days on a job that spans 2 weeks. I may look to include this capability in the future

 

[CJ_London]

OK, I'm still confused. So clarify this situation

Job1 in January is issued 3 bits of kit and has two staff members assigned, each with their own kit

In february Job2 is issued with 2 bits of kit and has one of the above staff members assigned

That staff member was issued with a further bit of kit at the beginning of february.

From what you are describing, you want that further bit of kit to be assigned to job1 as well as job2, even though Job1 was completed by the time the kit was issued. Is this correct? And if not, how do you plan to determine that it shouldn't be

 

[nickdawes28]

Annnnd the system fails! I hadn't considered that possibility, and just like that it's back to the drawing board.

In reality each staff member is assigned a harness, helmet and a few other bits when they start and that equipment stays with them until they leave the company or the equipment is retired. Ropes, slings and carabiners are assigned directly to the jobs as staff members should never 'own' their own ropes etc.

In addition to what you have pointed out, I realise if a staff member leaves the company and is removed from the system- all record of their equipment ever being on a job is removed with them. Same if equipment is handed from one staff member to another, as the STAFF_NAME field in equipment tables changes there is no lasting record of that equipment on the jobs it was previously assigned to under the original staff members name.

:banghead:

Do you have any suggestions as to how I should implement this properly? The staff kit feature was created to minimize administration when assigning equipment to jobs- the idea being you could assign a staff member to a job instead of individually assigning the equipment they use on every job.

Eg. John owns Harness1 and Helmet1, assign John to Job1 and that will in turn assign Harness1 and Helmet1 to Job1.

Thanks for your help so far!

 

[CJ_London]

OK - first - normalise your data and second use in/out dates

I suggest your structure is as follows:

tblJobs
JobPK - autonumber
JobDesc - text
jobfrom - date
jobto - date

tblStaff
StaffPK - autonumber
StaffName - text
StaffJoin - date
StaffLeave - date

tblKit
KitPK - autonumber
KitType - Text (or an FK to a KitType Table)
KitDesc - Text

tblIssue
IssuePK - autonumber
KitFK - long (is null if staff is issued to job)
JobFK -long (is null if kit issued to staff)
StaffFK - long (is null if kit issued to job)
IssueFrom - date (default today)
IssueTo - date (default Null)

FK's are the many side of a one to many relationship with the corresponding PK

Your relationship structure will then look like this - note the left joins - if you also make the join between tblJobs and tblIssue a left join as well, this query will also return jobs which don't have any kit or staff issued to it

So to determine what kit is issued to a job your query would be:

SELECT tblJobs.JobPK, tblIssue.IssuePK, tblJobs.JobDesc, tblIssue.StaffFK, Nz([tblKit].[KitName],[tblKit_1].[kitname]) AS Kit
FROM ((((tblJobs INNER JOIN tblIssue ON tblJobs.JobPK = tblIssue.JobFK) LEFT JOIN tblKit ON tblIssue.KitFK = tblKit.KitPK) LEFT JOIN tblStaff ON tblIssue.StaffFK = tblStaff.StaffPK) LEFT JOIN tblIssue AS tblIssue_1 ON tblStaff.StaffPK = tblIssue_1.StaffFK) LEFT JOIN tblKit AS tblKit_1 ON tblIssue_1.KitFK = tblKit_1.KitPK
WHERE (((Nz([tblKit].[KitName],[tblKit_1].[kitname])) Is Not Null) AND ((Nz([tblissue].[IssueFrom],[jobto]))<=[jobto]) AND ((Nz([tblissue].[IssueTo],[jobfrom]))>=[jobfrom]) AND ((Nz([tblissue_1].[IssueFrom],[jobto]))<=[jobto]) AND ((Nz([tblissue_1].[IssueTo],[jobfrom]))>=[jobfrom]) AND ((Nz([StaffJoin],[jobto]))<=[jobto]) AND ((Nz([StaffLeave],[jobfrom]))>=[jobfrom]))

Looks complicated but if you look at the query in design view it is actually quite simple

 

[nickdawes28]

Wow, that's more help than I'd ever anticipated- you have practically designed the entire database for me! I will look at this later tonight (6:45am here in Adelaide at the moment) and let you know how I get on.

Thanks very much for all your help so far CJ!

 

[nickdawes28]

Ok, managed to set up the screenshot you provided and I am able to query data as before, however I am still missing something- I'm sure you will be able to provide the answer!

At present I am able to assign equipment from Kit_1 to staff through tblssue_1 and assign staff to jobs through tblIssue. I am then able to query which staff were present on which jobs, and what equipment they have assigned to them.

However I cannot see how this setup allows for the equipment assigned to staff through tblIssue_1 to be re-assigned to a different member of staff, without losing the history.

E.g,

• I assign Harness1 from tblKit_1 to John, and John attends job SampleJob from tbl_Jobs on 01/01/2014.
• This creates an entry in tblIssue_1 to link Kit_1.KitPK to Issue_1.IssuePK.
• I can easily create SampleReport showing this data. However, 9 months down the line John leaves the company and passes Harness1 on to a new starter Chris. Harness1 is assigned to Chris in tblIssue_1.
• This creates another entry in tblIssue_1. I need to remove the previous entry otherwise Harness1 will appear to belong to both John and Nick.
• Now SampleReport does not show Harness1 as ever being present on SampleJob, as Chris was never assigned to SampleJob under tblIssue.

Am I correct in assuming the above is possible? And if so, what is the best solution?

I can think of a possible solution. If you look back at my original relationship setup, I will explain the same scenario above with how I propose to solve the problem.

• I assign Harness1 from tblHarnesses to John by changing the STAFF_NAME field. John attends job SampleJob from tbl_Jobs on 01/01/2014.
• This creates an entry in tblHarnesses2Jobs, which links Harness1 itself to SampleJob in tblJobs.
• 9 months down the line John leaves the company and passes Harness1 on to a new starter Chris. The STAFF_NAME field in Harness1 record changes to Chris.
• Harness1 is still linked to SampleJob in tblJobs.

Would like to reiterate the fact I am an inexperienced user and could well be missing something very obvious to an Access expert.

Hope to hear back from you on this CJ!

 

[CJ_London]

need to remove the previous entry otherwise Harness1 will appear to belong to both John and Nick

Not if you've completed the issued from/to dates and the staff join/leave dates

I'm also concerned you are referring to Kit_1, tblIssue_1 etc as if they are separate tables from Kit and tblIssue - they're not. You should just have the 4 tables as per my post. The _1 only occurs becuase you have a second instance of the table in the query/relationship

I'm now away for a few days so keep trying with it. Rather than theorising about whether or not it will/wont work, suggest you populate with some data and try out these scenarios

 

[nickdawes28]

Wow. This is brilliant, thanks again for your help CJ- the setup you suggested works perfectly. I have tried it out with a few scenarios (jobs overlapping, staff leaving, equipment changing hands) and I'm very happy with the results.

A few of the tools used are beyond my level of understanding within Access, so over the next few days I will spend some time looking into them. In particular Left Joins and the NZ function were completely new to me. I may have more questions in the future but you have given me more than enough to get started with this.

Thanks CJ!