scottfarcus
Registered User.
- Local time
- Today, 13:08
- Joined
- Oct 15, 2001
- Messages
- 182
I am attempting to develop a dynamic query capability in one of my databases.
tblStaff: a list of staff names in one table (StaffID key)
tblCars: a unique list of Cars (CarID key)
tblStaffCars: a list of Cars owned by staff (StaffID, CarID combo key)
tblComputer: a unique list of computers (laptop personal, laptop company, desktop personal, desktop company, other) (ComputerID key)
tblStaffComputer: a list of computers assigned to or owned by each staff (StaffID, ComputerID)
I've built a report based on a query that compiles all this stuff into one recordset. What I'm trying to do now is allow the user to select a car type (BMW) and filter down the staff list to show only those people who own a BMW. BUT, the report shows duplicate records for some people because they own more than one computer in tblStaffComputer and the report needs to be able to be filtered on Car AND/OR Computer at the same time (If I want to find out everyone who owns a Porsche and a company laptop for example), but only show individual names once if I only use one of the available search criteria.
Can anybody help?
tblStaff: a list of staff names in one table (StaffID key)
tblCars: a unique list of Cars (CarID key)
tblStaffCars: a list of Cars owned by staff (StaffID, CarID combo key)
tblComputer: a unique list of computers (laptop personal, laptop company, desktop personal, desktop company, other) (ComputerID key)
tblStaffComputer: a list of computers assigned to or owned by each staff (StaffID, ComputerID)
I've built a report based on a query that compiles all this stuff into one recordset. What I'm trying to do now is allow the user to select a car type (BMW) and filter down the staff list to show only those people who own a BMW. BUT, the report shows duplicate records for some people because they own more than one computer in tblStaffComputer and the report needs to be able to be filtered on Car AND/OR Computer at the same time (If I want to find out everyone who owns a Porsche and a company laptop for example), but only show individual names once if I only use one of the available search criteria.
Can anybody help?