Kamis, 16 Mei 2013

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

 

Duane,
 
I did but I was not quite sure how to apply your code to my exact table structure. I will have 2 tables I am working with. One is called dbo_consumers and the other is dbo_servicedeliveries. The Consumers table will have a ConsumerID and Full Name. (It has several other fields but for the purpose of this I think we are only using these two fields). The ServiceDeliveries table has the fields ConsumerID and ServiceName (It also has several other fields). The related item is ConsumerID because a consumer ID will be the same in both tables.   So when I create my basic query I pull ConsumerID and FullName from consumers table and ServiceName from ServiceDeliveries table to generate the fields I want to see. The ConsumerID has the one to on relationship to the services and consumer tables. I am not sure on the Count command you mentioned if that would work because they may not be listed just twice. A person may have a service called Homemaker and there
are several different kinds and they may have Personal Care and there are several different kinds of those. So they may have Homemaker1, Homemaker2, Homemaker3, PersonalCare1, and Personal Care3. If they have at least one type of Homemaker AND at least one type of PersonalCare I want them displayed. They may have 3 homemakers and 1 personal care so they would meet the criteria but in the count this would show 4 so I am not sure how the count command would work here.


________________________________
From: Duane Hookom <duanehookom@hotmail.com>
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Sent: Thursday, May 16, 2013 9:41 AM
Subject: RE: [MS_AccessPros] filtering to show anyone who has multiple items

 

Did you try my suggestion? The formatting got clobbered so here it is again:

==================================================================

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 tblDaleC
WHERE ID IN 
(SELECT ID
 FROM tblDaleC
 WHERE Service IN ("Service1","Service2")
 GROUP BY ID HAVING COUNT(*) = 2)
 AND Service IN ("Service1","Service2");

==================================================================
Hopefully the formatting, spaces,and carriage returns stick this time.

Duane

----------------------------------------
> To: mailto:MS_Access_Professionals%40yahoogroups.com
> From: mailto:tenn_tazz26%40yahoo.com
> Date: Thu, 16 May 2013 06:33:09 -0700
> Subject: Re: [MS_AccessPros] filtering to show anyone who has multiple items
>
> There is a service category called registeredtypeservices. It would include any homemaker, personalcare, or several other types. For the purpose of this we need to target just those getting the personalcare type services AND the homemaker type services.
>
>
> ________________________________
> From: John Viescas <mailto:JohnV%40msn.com>
> To: mailto:MS_Access_Professionals%40yahoogroups.com
> Sent: Thursday, May 16, 2013 9:19 AM
> Subject: RE: [MS_AccessPros] filtering to show anyone who has multiple items
>
>
>
> Dale-
>
> Is there a Service Type in ServiceDeliveries that tells you the category of service? For example, all Homemaker services (1, 2, 3, etc.) would all have the same "HomeMaker" service type.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> -----Original Message-----
> From: mailto:MS_Access_Professionals%40yahoogroups.com [mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of Dale Condon
> Sent: Thursday, May 16, 2013 7:39 AM
> To: mailto:MS_Access_Professionals%40yahoogroups.com
> Subject: Re: [MS_AccessPros] filtering to show anyone who has multiple items
>
> I am not sure I know how to convert this code to my table structures. I will have two tables I am working with. One is called dbo_consumers and the other is dbo_servicedeliveries. The Consumers table will have a ConsumerID and Full Name. (It has several other fields but for the purpose of this I think we are only using these two fields). The ServiceDeliveries table has the fields ConsumerID and ServiceName (It also has several other fields). The related item is ConsumerID because a consumer ID will be the same in both tables. I am not sure on the Count command you mentioned if that would work because they may not be listed just twice. A person may have a service called Homemaker and there are several different kinds and they may have Personal Care and there are several different kinds of those. So they may have Homemaker1, Homemaker2, Homemaker3, PersonalCare1, and Personal Care3. If they have at least one type of Homemaker AND at least one type of
> PersonalCare I want them displayed. They may have 3 homemakers and 1 personal care so they would meet the criteria but in the count this would show 4 so I am not sure how the count command would work here.
>
>
> ________________________________
> From: Duane Hookom <mailto:duanehookom%40hotmail.com>
> To: Access Professionals Yahoo Group <mailto:ms_access_professionals%40yahoogroups.com>
> Sent: Friday, May 10, 2013 4:36 PM
> Subject: 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: mailto:MS_Access_Professionals%40yahoogroups.com
>> From: mailto:tenn_tazz26%40yahoo.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]
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>



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

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (9)
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar