Re: Need assistance with query

There are hundreds of queries on that site, I would ask your firewall team to remove the block.


"Dan Lewis" <waynelewis@xxxxxxxxx> wrote in message news:c3869995-825c-4f57-9f05-784d6913d3e1@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Nov 8, 9:16 pm, "Garth" <S...@xxxxxxx> wrote:
I don't think that you have your query right based on what you are looking
for and the query that you are looking for is a complicated query to write
and test. I can't test it because you are using views that i don't have
such as "v_2NDVALUE0"

Take a look at this

http://smsug.ca/blogs/garth_jones/archive/2007/05/11/342.aspx

and I think this is more what you are looking for

http://smsug.ca/blogs/garth_jones/archive/2007/06/24/431.aspx

"Dan Lewis" <waynele...@xxxxxxxxx> wrote in message

news:0c99f942-3d40-4987-bbd8-a7be58d71b1a@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Nov 7, 3:08 pm, "Garth" <S...@xxxxxxx> wrote:





> I'm curious why you think the numbers are all wrong?

> "Dan Lewis" <waynele...@xxxxxxxxx> wrote in message

>news:96c24d56-e5e1-417d-b192-fd086d9e92ea@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> On Nov 1, 6:38 pm, "Garth" <S...@xxxxxxx> wrote:

> > Try
> > this.http://smsug.ca/blogs/garth_jones/archive/2008/11/01/ou-count-of-pcs-...

> > This query will give you a count of all PC that meet a requirement for
> > each
> > OU.

> > "Dan Lewis" <waynele...@xxxxxxxxx> wrote in message

> >news:bb6840b9-a587-4a4f-8580-0dea2f3ffb1e@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

> > > I'm trying to build a report that will show each OU in AD and a > > > count
> > > of all computers in that OU and a count of computers that meet some
> > > requirement in that OU:

> > > SELECT OU.System_OU_Name0, count(*) as 'Count'
> > > FROM v_R_System as SYS
> > > JOIN v_RA_System_SystemOUName as OU on SYS.ResourceID = > > > OU.ResourceID
> > > JOIN AnotherView on SYS.ResourceID = AnotherView.ResourceID
> > > WHERE AnotherView.SomeCriteria0 like "%99%"
> > > group by OU.System_OU_Name0 order by OU.System_OU_Name0

> > > I can't figure out how to get it to show a count of ALL systems in > > > the
> > > OU. Any help is appreciated.- Hide quoted text -

> > - Show quoted text -

> That helped. This is what I came up with, and it works:

> select distinct a.System_OU_Name0, count(a.ResourceID) as 'All
> Workstations', count(b.ResourceID) as 'XXXX',
> cast(100*(cast(count(b.ResourceID)as float)/cast(count(a.ResourceID)
> as float)) as numeric(5,2)) as 'Percent'

> from v_RA_System_SystemOUName a

> left join v_2NDVALUE0 b on b.ResourceID = a.ResourceID
> and b.ImageRevision0 like "%value%"

> where a.System_OU_Name0 = "ouname"
> group by a.System_OU_Name0 order by a.System_OU_Name0

> Now, I want to add another column that shows the number of systems
> that have office 2007 installed, so I tried this:

> select distinct a.System_OU_Name0, count(a.ResourceID) as 'All
> Workstations', count(b.ResourceID) as 'XXXX',
> cast(100*(cast(count(b.ResourceID)as float)/cast(count(a.ResourceID)
> as float)) as numeric(5,2)) as 'Percent', count(c.ResourceID) as
> 'Office2K7'

> from v_RA_System_SystemOUName a

> left join v_2NDVALUE0 b on b.ResourceID = a.ResourceID
> and b.ImageRevision0 like "%value%"

> left join v_GS_INSTALLED_SOFTWARE c on c.ResourceID = a.ResourceID
> and c.ProductName0 like "%office%2007%"

> where a.System_OU_Name0 = "ouname"
> group by a.System_OU_Name0 order by a.System_OU_Name0

> But, this is not working. The numbers come out completely wrong.

Because they are :^)

I'm not sure what is happening, but the numbers are very exaggerated.
If I build an individual query to show the number of systems in a
specific OU that have Office 2007 and the other criteria, the numbers
are correct, but this report is not. If I understood more about how
sql queries worked, I might be able to follow the flow of the query I
posted. It logically makes sense to me and seems like it should work,
but after I added the 2nd join (v_GS_INSTALLED_SOFTWARE), it goes
crazy on me. It throws off the counts for a and b. What I want is:

count(a.ResourceID) = ALL systems in the OU
count(b.ResourceID) = the systems in the OU that meet the criteria for
the first join
count(c.ResourceID) = the systems in the OU that have Office 2007
installed

a and b are working, but not when I add c.- Hide quoted text -

- Show quoted text -

Can you post the information from those links (they are blocked for
me :^( )

.

Relevant Pages

  • RE: link pull down/drop down menu to query access
    ... tab on the form in the query criteria. ... one alternative is to use 5 different queries. ... Dim varFiscalWeek As Variant ...
    (microsoft.public.access.forms)
  • RE: link pull down/drop down menu to query access
    ... tab on the form in the query criteria. ... one alternative is to use 5 different queries. ... Dim varFiscalWeek As Variant ...
    (microsoft.public.access.forms)
  • Re: multiple variables
    ... stuff in a query's criteria is just making the problem more ... could reduce the number of queries that you need for all ... regular query). ... DoCmd.OpenReport "report name", acViewPreview, _ ...
    (microsoft.public.access.formscoding)
  • RE: link pull down/drop down menu to query access
    ... one alternative is to use 5 different queries. ... Now, in your query, put this in the Criteria row of the field that stores ... Dim varFiscalWeek As Variant ...
    (microsoft.public.access.forms)
  • Re: Access: Add "Row Source Lookup" to "Query Parameters"
    ... Hello, Tom. ... I understand how form values can be retreived for queries. ... parameters with by possible without having to create controls criteria forms. ... The query can automatically generate the criteria input form based on the ...
    (microsoft.public.access.queries)