Data Count & Display

[Chris]

I have a field called Tbl_PIP.[Requires Specs] and a field called
Tbl_PIP_Phases.Date. I want to count the items in Tbl_PIP.[Requires Specs]
but only if those items happen to fall within the after 1/1/07 or prior to
todays date.

Example: Hotel ABC has 5 items due on 7/1/07 (These would go to
Tbl_PIP.[Requires Specs]. The hotel also has 5 items due on 12/1/07 (These
would also go to Tbl_PIP.[Requires Specs]. We have approved the 5 items due
on 7/1/07 (Tbl_PIP.[Specs Approved]) but not the 5 from December.

I want the report to look like this.
Items Due Specs Approved
%
Hotel ABC 5 5
100%

What I'm getting is...

Items Due Specs Approved
%
Hotel ABC 10 5
50%

I know this is kind of confusing but any help would be appreciated!

 

[John W. Vinson]

I have a field called Tbl_PIP.[Requires Specs] and a field called
Tbl_PIP_Phases.Date. I want to count the items in Tbl_PIP.[Requires Specs]
but only if those items happen to fall within the after 1/1/07 or prior to
todays date.

Example: Hotel ABC has 5 items due on 7/1/07 (These would go to
Tbl_PIP.[Requires Specs]. The hotel also has 5 items due on 12/1/07 (These
would also go to Tbl_PIP.[Requires Specs]. We have approved the 5 items due
on 7/1/07 (Tbl_PIP.[Specs Approved]) but not the 5 from December.

I want the report to look like this.
Items Due Specs Approved
%
Hotel ABC 5 5
100%

What I'm getting is...

Items Due Specs Approved
%
Hotel ABC 10 5
50%

I know this is kind of confusing but any help would be appreciated!

Please post the SQL view of the Query that you're using as the recordsource,
and the Control Source of the textbox containing ItemsDue and the percentage
field. One or other of these is incorrect, but without seeing what you did to
get the result, we can't tell you how to fix it!

John W. Vinson [MVP]

 

[John W. Vinson]

I have a field called Tbl_PIP.[Requires Specs] and a field called
Tbl_PIP_Phases.Date. I want to count the items in Tbl_PIP.[Requires Specs]
but only if those items happen to fall within the after 1/1/07 or prior to
todays date.

Example: Hotel ABC has 5 items due on 7/1/07 (These would go to
Tbl_PIP.[Requires Specs]. The hotel also has 5 items due on 12/1/07 (These
would also go to Tbl_PIP.[Requires Specs]. We have approved the 5 items due
on 7/1/07 (Tbl_PIP.[Specs Approved]) but not the 5 from December.

The problem is probably with the OR clause retrieving records you aren't
expecting - parenthesis nesting can be tricky! Let's untangle the WHERE clause
here:

WHERE
(
((Tbl_PIP.[Requires Specs])=True)
AND
((Tbl_Inspect.Status)="Reno")
AND
((Tbl_PIP_Phases.Date)>=#1/1/2007#)
AND
((Tbl_Hotels.Country)="US")
)
OR
(
((Tbl_PIP.[Requires Specs])=True)
AND
((Tbl_Inspect.Status)="NC-Reno")
AND
((Tbl_PIP_Phases.Date)<=Date())
AND
((Tbl_PIP_Phases.[Prior to Opening])=True))

The OR in the middle there will retrieve the record if *EITHER* the first
block of code is true - i.e. the date is greater than January 1; *OR* if the
second block is true - the date is anytime prior to today's date. These need
to be AND'd conditions. I don't know enough about your database to be sure but
I suspect you meant the OR to apply to "Reno" and "NC-Reno". Try

WHERE
(
((Tbl_PIP.[Requires Specs])=True)
AND
((Tbl_Inspect.Status) IN ("Reno", "NC-Reno"))
AND
((Tbl_PIP_Phases.[Date]) BETWEEN DateSerial(Year(Date()), 1, 1) AND Date())
AND
((Tbl_Hotels.Country)="US")
AND
((Tbl_PIP_Phases.[Prior to Opening])=True)
)

Note that Date is a BAD choice of fieldnames - it's a reserved word for the
builtin Date() function, and - if you use it at all - it should be enclosed in
square brackets.

John W. Vinson [MVP]

 

[Chris]

John,

That worked really well, the only problem is that I have a hotel that has
data and the Tbl_PIP_Phases.[Prior to Opening]) is true, yet nothing
displays. Any suggestions?

I have a field called Tbl_PIP.[Requires Specs] and a field called
Tbl_PIP_Phases.Date. I want to count the items in Tbl_PIP.[Requires Specs]
but only if those items happen to fall within the after 1/1/07 or prior to
todays date.

Example: Hotel ABC has 5 items due on 7/1/07 (These would go to
Tbl_PIP.[Requires Specs]. The hotel also has 5 items due on 12/1/07 (These
would also go to Tbl_PIP.[Requires Specs]. We have approved the 5 items due
on 7/1/07 (Tbl_PIP.[Specs Approved]) but not the 5 from December.

The problem is probably with the OR clause retrieving records you aren't
expecting - parenthesis nesting can be tricky! Let's untangle the WHERE clause
here:

WHERE
(
((Tbl_PIP.[Requires Specs])=True)
AND
((Tbl_Inspect.Status)="Reno")
AND
((Tbl_PIP_Phases.Date)>=#1/1/2007#)
AND
((Tbl_Hotels.Country)="US")
)
OR
(
((Tbl_PIP.[Requires Specs])=True)
AND
((Tbl_Inspect.Status)="NC-Reno")
AND
((Tbl_PIP_Phases.Date)<=Date())
AND
((Tbl_PIP_Phases.[Prior to Opening])=True))

The OR in the middle there will retrieve the record if *EITHER* the first
block of code is true - i.e. the date is greater than January 1; *OR* if the
second block is true - the date is anytime prior to today's date. These need
to be AND'd conditions. I don't know enough about your database to be sure but
I suspect you meant the OR to apply to "Reno" and "NC-Reno". Try

WHERE
(
((Tbl_PIP.[Requires Specs])=True)
AND
((Tbl_Inspect.Status) IN ("Reno", "NC-Reno"))
AND
((Tbl_PIP_Phases.[Date]) BETWEEN DateSerial(Year(Date()), 1, 1) AND Date())
AND
((Tbl_Hotels.Country)="US")
AND
((Tbl_PIP_Phases.[Prior to Opening])=True)
)

Note that Date is a BAD choice of fieldnames - it's a reserved word for the
builtin Date() function, and - if you use it at all - it should be enclosed in
square brackets.

John W. Vinson [MVP]

 

[John W. Vinson]

John,

That worked really well, the only problem is that I have a hotel that has
data and the Tbl_PIP_Phases.[Prior to Opening]) is true, yet nothing
displays. Any suggestions?

(
((Tbl_PIP.[Requires Specs])=True)
AND
((Tbl_Inspect.Status) IN ("Reno", "NC-Reno"))
AND
((Tbl_PIP_Phases.[Date]) BETWEEN DateSerial(Year(Date()), 1, 1) AND Date())
AND
((Tbl_Hotels.Country)="US")
AND
((Tbl_PIP_Phases.[Prior to Opening])=True)
)

This will require that all five conditions be true for the record to be
retrieved. For the hotel that's not showing up, what are the contents of
[Requires Specs], [Status], [Date], [Country] and [Prior To Opening]? If any
one of these fails to match you won't see the record.

John W. Vinson [MVP]

 

[Chris]

The only problem with that is I want the record to display if the
Tbl_PIP_Phases.[Date] is between our criteria OR if Tbl_PIP_Phases.[Prior to
Opening] is True.

John,

That worked really well, the only problem is that I have a hotel that has
data and the Tbl_PIP_Phases.[Prior to Opening]) is true, yet nothing
displays. Any suggestions?

(
((Tbl_PIP.[Requires Specs])=True)
AND
((Tbl_Inspect.Status) IN ("Reno", "NC-Reno"))
AND
((Tbl_PIP_Phases.[Date]) BETWEEN DateSerial(Year(Date()), 1, 1) AND Date())
AND
((Tbl_Hotels.Country)="US")
AND
((Tbl_PIP_Phases.[Prior to Opening])=True)
)

This will require that all five conditions be true for the record to be
retrieved. For the hotel that's not showing up, what are the contents of
[Requires Specs], [Status], [Date], [Country] and [Prior To Opening]? If any
one of these fails to match you won't see the record.

John W. Vinson [MVP]

 

[John W. Vinson]

The only problem with that is I want the record to display if the
Tbl_PIP_Phases.[Date] is between our criteria OR if Tbl_PIP_Phases.[Prior to
Opening] is True.

Sorry... as I say, I don't know the details of your database and you did not
post the logic so I guessed, and guessed wrong. Do the other criteria apply in
either case?

John W. Vinson [MVP]

 

[Chris]

John,

All the other requirements are true for all cases.

The only problem with that is I want the record to display if the
Tbl_PIP_Phases.[Date] is between our criteria OR if Tbl_PIP_Phases.[Prior to
Opening] is True.

Sorry... as I say, I don't know the details of your database and you did not
post the logic so I guessed, and guessed wrong. Do the other criteria apply in
either case?

John W. Vinson [MVP]

 

[John W. Vinson]

John,

All the other requirements are true for all cases.

The only problem with that is I want the record to display if the
Tbl_PIP_Phases.[Date] is between our criteria OR if Tbl_PIP_Phases.[Prior to
Opening] is True.

Sorry... as I say, I don't know the details of your database and you did not
post the logic so I guessed, and guessed wrong. Do the other criteria apply in
either case?

John W. Vinson [MVP]

Ok... two options.

Either use two rows on the query grid, with the date criterion on one row, the
Prior to Opening criterion on the other, and all the other criteria on BOTH
rows; or do it in SQL by putting the two OR criteria in parentheses:

WHERE
(((Tbl_PIP.[Requires Specs])=True)
AND
((Tbl_Inspect.Status) IN ("Reno", "NC-Reno"))
AND
((Tbl_PIP_Phases.[Date]) BETWEEN DateSerial(Year(Date()), 1, 1) AND Date()
OR
((Tbl_PIP_Phases.[Prior to Opening])=True))
AND
((Tbl_Hotels.Country)="US"))

or, getting rid of a lot of the extra unneeded parentheses that Access likes
to throw in for no good reason I've ever seen,

WHERE
tbl_PIP.[Requires Specs]=True
AND
Tbl_Inspect.Status IN ("Reno", "NC-Reno")
AND
Tbl_Hotels.Country="US"
AND
(Tbl_PIP_Phases.[Date] BETWEEN DateSerial(Year(Date()), 1, 1) AND Date()
OR Tbl_PIP_Phases.[Prior to Opening]=True)

Note that the last AND clause will be true if either the date criterion is
true, or Prior to Opening is True.

Boolean Algebra - the care and feeding of AND, OR, NOT and the other more
obscure logical operations - is something I learned in 10th grade (thanks,
Coach Burnett, your legacy lives on!) and have found applicable in many
contexts. SQL Where clauses are a prime example of such a context. It takes
some mental gearshifting, because the English language conjunctions "and" and
"or" don't work in quite the same way as the logical operators AND and OR. The
operators should be thought of in sort of the same way as you would think of +
and - operators in an algebraic expression; they obey a truth table like

<false expression> AND <false expression> = False
<true expression> AND <false expression> = False
<false expression> AND <true expression> = False
<true expression> AND <true expression> = True

<false expression> OR <false expression> = False
<true expression> OR <false expression> = True
<false expression> OR <true expression> = True
<true expression> OR <true expression> = True

This table is just like the addition tables 2+2=4 that you learned as a child
(but shorter) - just as mindless, just as rigorous. With attention to the AND
and OR operators and parenthesis nesting you can build extremely complex but
rigorously and provably accurate WHERE clauses in the SQL window - just don't
go back to the query grid or Access will turn them into corned-beef hash!


John W. Vinson [MVP]

 

[Chris]

John,

Well that worked! You really are an MVP!!! Now I need help with one more
thing. I only want the Tbl_PIP.[Requires Specs] to Count when the
Tbl_PIP.[Requires Specs] is true and falls in between the date we selected
Tbl_PIP_Phases.[Date]).

The idea is that a property has to submit product specifications for each
item(Tbl_PIP.[Requires Specs]). There are 100 items spread out over 3 years.
Instead of the report counting all 100 items, I only want it to count the
first 30 which are due 7/1/07 and display that count.

Any Ideas?

John,

All the other requirements are true for all cases.

The only problem with that is I want the record to display if the
Tbl_PIP_Phases.[Date] is between our criteria OR if Tbl_PIP_Phases.[Prior to
Opening] is True.

Sorry... as I say, I don't know the details of your database and you did not
post the logic so I guessed, and guessed wrong. Do the other criteria apply in
either case?

John W. Vinson [MVP]

Ok... two options.

Either use two rows on the query grid, with the date criterion on one row, the
Prior to Opening criterion on the other, and all the other criteria on BOTH
rows; or do it in SQL by putting the two OR criteria in parentheses:

WHERE
(((Tbl_PIP.[Requires Specs])=True)
AND
((Tbl_Inspect.Status) IN ("Reno", "NC-Reno"))
AND
((Tbl_PIP_Phases.[Date]) BETWEEN DateSerial(Year(Date()), 1, 1) AND Date()
OR
((Tbl_PIP_Phases.[Prior to Opening])=True))
AND
((Tbl_Hotels.Country)="US"))

or, getting rid of a lot of the extra unneeded parentheses that Access likes
to throw in for no good reason I've ever seen,

WHERE
tbl_PIP.[Requires Specs]=True
AND
Tbl_Inspect.Status IN ("Reno", "NC-Reno")
AND
Tbl_Hotels.Country="US"
AND
(Tbl_PIP_Phases.[Date] BETWEEN DateSerial(Year(Date()), 1, 1) AND Date()
OR Tbl_PIP_Phases.[Prior to Opening]=True)

Note that the last AND clause will be true if either the date criterion is
true, or Prior to Opening is True.

Boolean Algebra - the care and feeding of AND, OR, NOT and the other more
obscure logical operations - is something I learned in 10th grade (thanks,
Coach Burnett, your legacy lives on!) and have found applicable in many
contexts. SQL Where clauses are a prime example of such a context. It takes
some mental gearshifting, because the English language conjunctions "and" and
"or" don't work in quite the same way as the logical operators AND and OR. The
operators should be thought of in sort of the same way as you would think of +
and - operators in an algebraic expression; they obey a truth table like

<false expression> AND <false expression> = False
<true expression> AND <false expression> = False
<false expression> AND <true expression> = False
<true expression> AND <true expression> = True

<false expression> OR <false expression> = False
<true expression> OR <false expression> = True
<false expression> OR <true expression> = True
<true expression> OR <true expression> = True

This table is just like the addition tables 2+2=4 that you learned as a child
(but shorter) - just as mindless, just as rigorous. With attention to the AND
and OR operators and parenthesis nesting you can build extremely complex but
rigorously and provably accurate WHERE clauses in the SQL window - just don't
go back to the query grid or Access will turn them into corned-beef hash!


John W. Vinson [MVP]

 

[John W. Vinson]

Well that worked! You really are an MVP!!! Now I need help with one more
thing. I only want the Tbl_PIP.[Requires Specs] to Count when the
Tbl_PIP.[Requires Specs] is true and falls in between the date we selected
Tbl_PIP_Phases.[Date]).

The idea is that a property has to submit product specifications for each
item(Tbl_PIP.[Requires Specs]). There are 100 items spread out over 3 years.
Instead of the report counting all 100 items, I only want it to count the
first 30 which are due 7/1/07 and display that count.

Now that's a bit confusing. If you select only the top 30 won't the count be
30? or do you want the count to be up to 30? What exactly do you mean by "due
7/1/07" in terms of the fields in your table?


John W. Vinson [MVP]

 

[Chris]

Well that worked! You really are an MVP!!! Now I need help with one more

thing. I only want the Tbl_PIP.[Requires Specs] to Count when the
Tbl_PIP.[Requires Specs] is true and falls in between the date we selected
Tbl_PIP_Phases.[Date]).

The idea is that a property has to submit product specifications for each
item(Tbl_PIP.[Requires Specs]). There are 100 items spread out over 3 years.
Instead of the report counting all 100 items, I only want it to count the
first 30 which are due 7/1/07 and display that count.

Now that's a bit confusing. If you select only the top 30 won't the count be
30? or do you want the count to be up to 30? What exactly do you mean by "due
7/1/07" in terms of the fields in your table?


John W. Vinson [MVP]

I suppose that is a little confusing. Let me try and explain it a different
way. I work with hotels and I put together a plan that tells them what items
that have to renovate/replace. Lets say I tell a hotel that they have to
change 100 different items within the hotel (beds, artwork, carpet, etc).
These items all require specifications to be sent in (height, weight,
material, etc). This is determined by the whether the Tbl_PIP.[Requires
Specs] is True or False.

Lets say we give the hotel 3 years to complete the renovations. 30 of the
items that are Tbl_PIP.[Requires Specs]=True must be submitted to us (are
due) by 7/1/2007. The next 30 items that are Tbl_PIP.[Requires Specs]=True
must be submitted to us (are due) by 7/1/2008 and the final 40 are due
12/31/2008. Each of the dates are called a phase date
(Tbl_PIP_Phases.[Date]). Since only the first phase (due 7/1/07) is past, I
only want those 30 items to count and display. What is currently happening
is that since phase 1 is in our date criteria, all 100 items are counting and
displaying.

On the flip side, if phase 1 was due 7/1/07 and phase 2 was due 8/1/07, I
would want 60 items to print, not just 30.

Hopefully this makes a little more sense.

 

[John W. Vinson]

I suppose that is a little confusing. Let me try and explain it a different
way. I work with hotels and I put together a plan that tells them what items
that have to renovate/replace. Lets say I tell a hotel that they have to
change 100 different items within the hotel (beds, artwork, carpet, etc).
These items all require specifications to be sent in (height, weight,
material, etc). This is determined by the whether the Tbl_PIP.[Requires
Specs] is True or False.

Lets say we give the hotel 3 years to complete the renovations. 30 of the
items that are Tbl_PIP.[Requires Specs]=True must be submitted to us (are
due) by 7/1/2007. The next 30 items that are Tbl_PIP.[Requires Specs]=True
must be submitted to us (are due) by 7/1/2008 and the final 40 are due
12/31/2008. Each of the dates are called a phase date
(Tbl_PIP_Phases.[Date]). Since only the first phase (due 7/1/07) is past, I
only want those 30 items to count and display. What is currently happening
is that since phase 1 is in our date criteria, all 100 items are counting and
displaying.

Chris, did you ever get this resolved? Sorry, I marked this thread and never
got back to it!

John W. Vinson [MVP]

 

[Chris]

I suppose that is a little confusing. Let me try and explain it a different
way. I work with hotels and I put together a plan that tells them what items
that have to renovate/replace. Lets say I tell a hotel that they have to
change 100 different items within the hotel (beds, artwork, carpet, etc).
These items all require specifications to be sent in (height, weight,
material, etc). This is determined by the whether the Tbl_PIP.[Requires
Specs] is True or False.

Lets say we give the hotel 3 years to complete the renovations. 30 of the
items that are Tbl_PIP.[Requires Specs]=True must be submitted to us (are
due) by 7/1/2007. The next 30 items that are Tbl_PIP.[Requires Specs]=True
must be submitted to us (are due) by 7/1/2008 and the final 40 are due
12/31/2008. Each of the dates are called a phase date
(Tbl_PIP_Phases.[Date]). Since only the first phase (due 7/1/07) is past, I
only want those 30 items to count and display. What is currently happening
is that since phase 1 is in our date criteria, all 100 items are counting and
displaying.

Chris, did you ever get this resolved? Sorry, I marked this thread and never
got back to it!

John W. Vinson [MVP]

John,

I never did get it figured out. Any ideas?