Using IF statement associated with Count

[jereece]

I have a report that totals the number of items for a given site [site_cd]. I have a Site_Cd header and I am using the [=Count(*)] to give me the total. However I want the total to be based on the number of units at the site and was wondering if I could use an IF statement to accomplish this. For example, if the site_cd is "O" the total should be devided by 3, if "M" or "C" divided by 2, otherwise just the total count.

Any help on this is appreciated. I am not good with IF statements. I am open to another approach if there is a better and easier one.

Thanks,
Jim

 

[plog]

Post some sample data--->Post some sample records, then post what you want the query to return based on those sample records.

 

[jereece]

site_cd pip_yr pip_no entering_dte
c 11 1563 3/1/2011 5:05:00 AM
m 11 1564 3/1/2011 6:09:00 AM
o 11 1565 3/1/2011 7:15:00 AM
o 11 1566 3/1/2011 7:28:00 AM
o 11 1567 3/1/2011 7:56:00 AM
o 11 1568 3/1/2011 8:11:00 AM
o 11 1569 3/1/2011 8:13:00 AM
g 11 1570 3/1/2011 8:31:00 AM
c 11 1571 3/1/2011 8:44:00 AM
c 11 1572 3/1/2011 8:53:00 AM
g 11 1573 3/1/2011 9:01:00 AM
c 11 1574 3/1/2011 9:40:00 AM
c 11 1575 3/1/2011 10:04:00 AM
m 11 1576 3/1/2011 10:07:00 AM
m 11 1577 3/1/2011 10:10:00 AM


Results [=Count(*)]
c = 5
m = 3
g= 2
o = 5

Units At The Site
c = 2
m = 2
g = 1
o = 3

Results Per Site [If c divide by 2, if m divide by 2, if o divide by 3, if g divide by 1]

c = 1
m = 1
g = 0.5
o = 1.5

 

[plog]

Based on your sample rows I see how to determine this:

Results [=Count(*)]
c = 5
m = 3
g= 2
o = 5

But I don't see how you got this:

Units At The Site
c = 2
m = 2
g = 1
o = 3

Where does that data come from?

 

[jereece]

The units at the sites is a know standard. Think of units as plants. So site C and M have 2 units or plants on their site. Site o has 3 units or plants and site G has only 1 unit or plant.

 

[plog]

If that data is essentially standard then that means the data you want to calculate is essentially standard:

Results Per Site [If c divide by 2, if m divide by 2, if o divide by 3, if g divide by 1]

c = 1
m = 1
g = 0.5
o = 1.5

This data was derived from this data:

Units At The Site
c = 2
m = 2
g = 1
o = 3

There's not much of a calculation to perform. Exactly where are you stuck?

 

[jereece]

I am stuck on how to write and IF statement that will count the values for each site and then divide each site's count by the number of units. On my current report, I am simply using "=Count(*)" in the site_cd header. So my results is simply a total count for the site (c,m,g and o). However some sites have a different number of units and I need to report the count at the site on a per unit basis. So what I am looking for is an IF statement or something that will divide the results of the count by the number of units at that particular site. So my report for the example above would look like this;

Site - Counts Per Unit
c = 1
m = 1
g = 0.5
o = 1.5

 

[plog]

But 2 posts back you said Units at site was a known standard and not counted based on rows of a table.

You've lost me.

 

[jereece]

I don't know how to be any clearer but let me try again. In our company we have 4 physical sites or plants across 3 states. The sites in our database are simply Site C, Site G, Site M and Site O (the first letter of the name of the site). However, each site has a different number of units. Think of units as machines. So each site has a different number of machines we track data on. Site O has 3 units or machines, Site C and M has 2 machines and site G has only 1 machine. So the number of units or machines each site has is fixed.

So when my query runs, I use the report header to count the number returned by the query. However, I am more interested in the count divided by the units (or machines) the site has. This kind of normalizes the data based on how many machines the site has. Obviously it takes more to run 3 machines than 1 so I am trying to normalize the data based on that.

Does that clarify the situation? If so, I am looking to see if there is an IF statement I can use with the Count function to do this automatically.