ACCESS FEED: RE: [MS_AccessPros] filtering to show anyone who has multiple items

Jumat, 10 Mei 2013

RE: [MS_AccessPros] filtering to show anyone who has multiple items

I'm going to make an assumption the ID and Service combination is a unique index. Also, I can't wrap my head around a field named "Name".
This query seems to work for me:
SELECT tblDaleC.*FROM tblDaleCWHERE ID IN (SELECT ID FROM tblDaleC WHERE Service IN ("Service1","Service2") GROUP BY ID HAVING COUNT(*) = 2) AND Service IN ("Service1","Service2");
Duane Hookom, MVPMS Access
> To: MS_Access_Professionals@yahoogroups.com
> From: tenn_tazz26@yahoo.com
> Date: Fri, 10 May 2013 15:07:01 +0000
> Subject: [MS_AccessPros] filtering to show anyone who has multiple items
>
> I have Access 2002 and I have a basic query and can't seem to get the filtering to yield the correct results without creating two queries and comparing those. Here are the fields I have: ID, Name, Service
> The Service would contain the actual service name such as Service1, Service2, Service3, Service4, Service5, etc. If someone gets multiple services then the main table would have that person stored multiple times, one line for each service they receive. I am trying to make a query that shows me anyone who is getting Service1 and Service2. If I use the filter to be "Service1" AND "Service2" it will not display any results because technically there is no service name that is called Service1Service2 since each individual service is outputted to a separate line. If I use the OR statement it does show anyone who has Service1 or anyone who has Service2 but does not filter to just those that actually have the Service1 and a line with Service2. How could I accomplish this? For example, here would be an example of what the query outputs without a filter:
> 1235 - John - Service1
> 1235 - John - Service3
> 1235 - John - Service2
> 1236 - Jane - Service1
> 1345 - Stan - Service1
> 1345 - Stan - Service2
> 1654 - Jake - Service2
>
> If I wanted those who had service 1 and a service2 it should output this:
> 1235 - John - Service1
> 1235 - John - Service2
> 1345 - Stan - Service1
> 1345 - Stan - Service2
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

[Non-text portions of this message have been removed]