Kamis, 16 Mei 2013

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

 

I think this is getting closer. The only thing it did not like was the dbo_HAR_SERVICE_DELIVERIES.SERVICE_PERIOD_START date in the first line. Whenever I would run the query it would prompt me for a value. Is there a way I can display this date on the output or is the only way to remove the date from the first line in the query?


________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, May 16, 2013 11:54 AM
Subject: RE: [MS_AccessPros] filtering to show anyone who has multiple items

 

Dale-

You don't need a Totals query - unclick the Sigma symbol in Design. Try this:

SELECT dbo_HAR_Consumers.CLIENT_ID, dbo_HAR_Consumers.FULL_NAME, dbo_HAR_SERVICE_DELIVERIES.SERVICE_PERIOD_START
FROM dbo_HAR_Consumers
WHERE dbo_HAR_Consumers.CONSUMER_UUID In
(SELECT CONSUMER_UUID FROM dbo_HAR_SERVICE_DELIVERIES WHERE service like "*Homemaker*"
AND dbo_HAR_SERVICE_DELIVERIES.SERVICE_PERIOD_START>=#4/1/2013#)
And dbo_HAR_Consumers.CONSUMER_UUID In
(SELECT CONSUMER_UUID FROM dbo_HAR_SERVICE_DELIVERIES WHERE service like "*personal*"
AND dbo_HAR_SERVICE_DELIVERIES.SERVICE_PERIOD_START>=#4/1/2013#)

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 10:36 AM
To: mailto:MS_Access_Professionals%40yahoogroups.com
Subject: Re: [MS_AccessPros] filtering to show anyone who has multiple items

I used the code you said and it seems like it is working but the results are way to big for me to know for sure. So I tried to make a slight adjustment on the filtering by going to the Design View. Also I used the like statement instead of service= because these services are all named weird but the name Homemaker or Personal Care is in the ones I wanted to target. I hope that did not create the problem. When I went to Design View I added the Service Delivery table to the view and brought the service_date_range field down. Under the service date range I tried to put a date range filter of >=4/1/13 but it was returning some strange results. It returned 4 people but none of them had both Homemaker and Personal care in that time period. They received it in years prior.

Here is my code without the date range:
SELECT dbo_Consumers.CLIENT_ID, dbo_Consumers.FULL_NAME FROM dbo_Consumers WHERE (((dbo_Consumers.CONSUMER_UUID) In (SELECT CONSUMER_UUID FROM dbo_SERVICE_DELIVERIES WHERE service like "*Homemaker*") And (dbo_HAR_Consumers.CONSUMER_UUID) In (SELECT CONSUMER_UUID FROM dbo_HAR_SERVICE_DELIVERIES WHERE service like "*personal*"))) GROUP BY dbo_HAR_Consumers.CLIENT_ID, dbo_HAR_Consumers.FULL_NAME;

When I add the date range field from Design View here is the code SELECT dbo_HAR_Consumers.CLIENT_ID, dbo_HAR_Consumers.FULL_NAME, dbo_HAR_SERVICE_DELIVERIES.SERVICE_PERIOD_START
FROM dbo_HAR_Consumers INNER JOIN dbo_HAR_SERVICE_DELIVERIES ON dbo_HAR_Consumers.CONSUMER_UUID = dbo_HAR_SERVICE_DELIVERIES.CONSUMER_UUID
WHERE (((dbo_HAR_Consumers.CONSUMER_UUID) In (SELECT CONSUMER_UUID FROM dbo_HAR_SERVICE_DELIVERIES WHERE service like "*Homemaker*") And (dbo_HAR_Consumers.CONSUMER_UUID) In (SELECT CONSUMER_UUID FROM dbo_HAR_SERVICE_DELIVERIES WHERE service like "*personal*"))) GROUP BY dbo_HAR_Consumers.CLIENT_ID, dbo_HAR_Consumers.FULL_NAME, dbo_HAR_SERVICE_DELIVERIES.SERVICE_PERIOD_START
HAVING (((dbo_HAR_SERVICE_DELIVERIES.SERVICE_PERIOD_START)>=#4/1/2013#));

I think adding the service deliveries table to the view and the filter makes it so that if they received any service in the 4/1/13 period or after and meet the Personal Care Homemaker criteria to display them but I am not sure. I am guessing I need to move that last code into the code above it somehow.

________________________________
From: John Viescas <mailto:JohnV%40msn.com>
To: mailto:MS_Access_Professionals%40yahoogroups.com
Sent: Thursday, May 16, 2013 10:19 AM
Subject: RE: [MS_AccessPros] filtering to show anyone who has multiple items

Dale-

If you want Consumers who have at least one "HomeMaker" and one "PersonalCare" service, do this:

SELECT * FROM dbo_Consumers
WHERE ConsumerID IN
(SELECT ConsumerID FROM dbo_Servicedeliveries WHERE registeredtypeservice = "HomeMaker") AND ConsumerID IN (SELECT ConsumerID FROM dbo_Servicedeliveries WHERE registeredtypeservice = "PersonalCare")

You'll have to modify the tests on registeredservicetype to match the codes or values in your database for those two service types.

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 8:58 AM
To: mailto:MS_Access_Professionals%40yahoogroups.com
Subject: 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 <mailto:duanehookom%40hotmail.com>
To: Access Professionals Yahoo Group <mailto:ms_access_professionals%40yahoogroups.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]

------------------------------------

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 (13)
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar