EasyCFM.COM ColdFusion Forums / MS Access Related Issues / 5 table query conundrum

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: 5 table query conundrum

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

tgruen
03-24-2008 @ 4:40 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 59
Joined: Aug 2006

=== This is a long and challenging request for help. No hard feelings if no one has the time to step up and actually envision this conundrum. ===

After 3 days of banging my head on this one (did anyone else work Easter?) I have stalled in frustration. I feel I am ALMOST there but, alas, it escapes me.

DB is MSAccess. Client has an existing application for tracking assets. I wish it were SQL Server but.... I won't go there.

Here is the function:
I need to select information on an asset (a computer in this case) and see where it has been used. In this particular application computer assignments are recorded so the client can see who is using a computer. Computers can be assigned to employees or to groups. An example of a group is a computer lab, or public use in a library. This enables the tech guys to pull up a computer asset tag when challenged by all-knowing management and see what employee has it, or see that the computer in question is being used in the 5th floor computer lab. The page I am working on queries the database and returns a list of all the assignments for a particular computer. It should display a list of assignments whether the assignment was an employee assignment, or a group assignment. This will enable the tech support staff to pull up a computer by asset tag and see the computer's usage history. Only the asset id in the database (assets.a_id) is passed to the CFM page I am working on. This can be modified if necessary.

Selecting the required information involves 5 tables. They are tbl_assignments, tbl_assets, tbl_locations, tbl_employees, tbl_groups.

Here is a simplified outline of the DB structure and pertinent fields;

Tables:

tbl_assignments: this records assignments
assn_id = primary key
assn_a_id = asset foreign key from tbl_assets
assn_loc = asset location foreign key from tbl_locations (physical locations)
assn_empid = foreign key from tbl_employees
assn_groupid = foreign key from groups table

tbl_assets: this is the list of assets
a_id = primary key
a_loc_id = foreign key from the locations table (locations are physical offices)

tbl_locations
loc_id = primary key
loc_city = city
loc_state = state

tbl_employees
empl_id = primary key
empl_loc = employee location
empl_fname = first name

tbl_groups
groupid = primary key
group_name = name of broup (lab1, lab2, library, etc)
group_locid = foreign key for the location of the group

The exact issue is that assignments are either employee assignments OR group assignments. An asset may have a record in both tables, only one table, or neither table (if the asset is new). I have been able to get the query to work fine when I am only looking for assignments that are only employee, or assignments that are only group. But when my query (below) hits both tables it returns nothing. I think this is because my query is looking for full matches (where the record is in both the employee table and the group table). I haven't been able to find the best way to pull all matches regardless of employee or group related rcords.

My SQL:
SELECT assignments.assn_id, assignments.assn_a_id, assignments.assn_loc, assignments.assn_empid, assignments.assn_groupid, assets.a_id, assets.a_loc_id, assets.a_groupid, groups.groupid, groups.group_name, groups.group_locid, locations.loc_id, locations.loc_name, locations.loc_city, locations.loc_state, employees.empl_id, employees.empl_loc, employees.empl_fname
FROM groups INNER JOIN (employees INNER JOIN ((locations INNER JOIN assets ON locations.loc_id = assets.a_loc_id) INNER JOIN assignments ON assets.a_id = assignments.assn_a_id) ON employees.empl_id = assignments.assn_empid) ON groups.groupid = assignments.assn_groupid
WHERE (assignments.assn_a_id = 489)
AND (assignments.assn_groupid = groups.groupid)
OR (assignments.assn_empid = employees.empl_id)

If an actual copy of the database would be helpful I am able to share it. Contact me for a link to the file. I hope I have explained this sufficiently. If anyone has any suggestions I would greatly appreciate the input. Thanks in advance for taking the time to read this.

Tony

This message was edited by tgruen on 3-24-08 @ 4:43 PM

cfSearchin
03-24-2008 @ 6:11 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 608
Joined: Feb 2008

Thanks for taking the time to write such a good explanation of the issue.

This definitely calls for some type of outer join(s). What remains to be seen is how easy it is to construct the proper joins with Access. IIRC it is does not offer as much flexibility with joins as SQL Server.

A link to a sample database would be a great help.

tgruen
03-24-2008 @ 7:14 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 59
Joined: Aug 2006

Thanks for the reply cfsearcher. Here is a link to a sample of the database file I am working with. In this database you will see asset ID 368 is assigned to a group (the Art Studio) and asset ID 371 is assigned to an employee (Monica Allen). The third assignment is a past assignment for Monica Allen for asset ID 368. This represents the scenario that the computer in the Art Studio WAS in Monica's hands before going to the Art Studio.

My goal is to have the query return both records when asset ID 368 is queried and for only one record when queried for 371. I hope this makes sense.

[download offline]

Tony

This message was edited by tgruen on 3-24-08 @ 10:47 PM

cfSearchin
03-24-2008 @ 8:35 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 608
Joined: Feb 2008

I am not an expert with Access syntax, but it does not seem to allow the type of direct join needed here.  Maybe someone better versed in Access can find an alternative.  For now it can be done using a separate "view" or stored query.

Step 1:
Create a stored query named qry_all_assigments. It will return all records from the assigments table (ie both employee and group assignments). This is done by using left joins. Note, I do not normally recommend using prefixes like "qry_". Update Duh! If your real table names do not include "tbl_" then use a different name than qry_all_assigments ;-).


qry_all_assigments

SELECT
assignments.assn_id,
assignments.assn_a_id,
assignments.assn_loc,
assignments.assn_empid,
assignments.assn_groupid,
groups.groupid,
groups.group_name,
groups.group_locid,
employees.empl_id,
employees.empl_loc,
employees.empl_fname
FROM (assignments LEFT JOIN employees ON assignments.assn_empid = employees.empl_id)
LEFT JOIN groups ON groups.groupid = assignments.assn_groupid;

Step 2:
Then do an inner join between assets and location, and a left join with the stored query. The query will now return the selected asset and any assigments. Due to the left join, it will also return "new" assets (ie assets without any assignments).


main query
SELECT
qry_all_assigments.assn_id,
qry_all_assigments.assn_a_id,
qry_all_assigments.assn_loc,
qry_all_assigments.assn_empid,
qry_all_assigments.assn_groupid,
assets.a_id,
assets.a_loc_id,
assets.a_groupid,
qry_all_assigments.groupid,
qry_all_assigments.group_name,
qry_all_assigments.group_locid,
locations.loc_id,
locations.loc_name,
locations.loc_city,
locations.loc_state,
qry_all_assigments.empl_id,
qry_all_assigments.empl_loc,
qry_all_assigments.empl_fname

FROM     (locations INNER JOIN assets ON locations.loc_id = assets.a_loc_id)
     LEFT JOIN qry_all_assigments ON assets.a_id = qry_all_assigments.assn_a_id
WHERE assets.a_id = 371

This message was edited by cfSearchin on 3-24-08 @ 8:37 PM

cfSearchin
03-24-2008 @ 8:51 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 608
Joined: Feb 2008

I tweaked the syntax a bit and I think this single query will work too.

Since it uses a right join,  the results will include "new" assets (ie no assignments).  If you want to exclude new assets, change the right join to an inner join.


SELECT
assignments.assn_id,
assignments.assn_a_id,
assignments.assn_loc,
assignments.assn_empid,
assignments.assn_groupid,
assets.a_id,
assets.a_loc_id,
assets.a_groupid,
groups.groupid,
groups.group_name,
groups.group_locid,
locations.loc_id,
locations.loc_name,
locations.loc_city,
locations.loc_state,
employees.empl_id,
employees.empl_loc,
employees.empl_fname

FROM  
(((assignments LEFT JOIN employees ON assignments.assn_empid = employees.empl_id)
LEFT JOIN groups ON groups.groupid = assignments.assn_groupid)
RIGHT JOIN assets ON assets.a_id = assignments.assn_a_id)
  INNER JOIN locations ON locations.loc_id = assets.a_loc_id
WHERE assets.a_id = 368

tgruen
03-24-2008 @ 10:42 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 59
Joined: Aug 2006

cfsearchin, this seems to be working perfectly! I cannot thank you enough. So many times I jump in to help others and when someone else is willing to do the same for me it is a great feeling. Your solution also shows me some crafty LEFT/RIGHT join usage that I will be researching more in-depth.

Hopefully those who read this topic will get some tips from your code as well. Cheers!

Tony

cfSearchin
03-25-2008 @ 12:05 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 608
Joined: Feb 2008

You are very welcome.  It is always nice when what goes around comes around, right? (Though you made it easy by providing all the information needed ;-)



This message was edited by cfSearchin on 3-25-08 @ 3:35 PM


Website Designed and Developed by Pablo Varando.