Zellax Zellax - 2 years ago 128
SQL Question

How to get a Split-view form to display DISTINCT entries when one data field is multi-valued

What I want that I don't yet have:

Distinct employee entries in the datasheet view. Currently there is as many records for the same employee as that employee has authorized locations, recorded in a multi-valued field.


I have a split-view form that lists employees and what stations they are approved to perform operations at. The [ScanCodeLocations] field is multi-valued. It was not my choice, someone else decided to use it and for the form it actually works slick because when I link the [ScanCodeLocations] to a list box Access gives me a convenient check box next to each entry in the row source to easily and clearly manage that many-to-many relationship. I would have no idea how to replicate that check box aesthetic using a linking table. The Data Source for the form comes from the following query:

SELECT Employees.ID, Employees.FirstName, Employees.LastName,
Employees.IsActive, Employees.IsManager, Employees.HasScanCode,
Employees.ScanCodeID, Employees.ScanBadgeTitle, Employees.ScanCodeLocations,
FROM Employees
LEFT JOIN Locations
ON Employees.ScanCodeLocations.Value = Locations.ID
ORDER BY Employees.LastName;

Is there some way to adjust this query to get it to give back only one record per employee without having to remove the [ScanCodeLocations] field? I need that field in the form and I need it to update with the rest of the fields when the user selects a different employee record.
The List Box I'd rather not mess up
The output with multiple entries
The split-view datasheet is a list of all the employees in the production environment and the form is meant for managers to be able to modify the properties for the employees. I sure don't want John Doe listed many times.

Answer Source

SOLVED! I created another similar form that used a multi-value field And it didn't multiply entries so I compared the two and realized what was wrong.

I had [Employees.ScanCodeLocations.Value] LEFT JOINed to the [Locations] table. Employees.ScanCodeLocations.Value only stores a Location ID so the join was supposed to let me use the name of the location but that was unnecessary. The List Box object allows me to define a row source that links the Location ID to its name so I didn't need the location name in the form's Record Source.

Once I removed the LEFT JOIN between Employees.ScanCodeLocations.Value and the [Locations] table it only listed each employee once. Yay!


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download