Horizontal bar or line based on field value

[sharkchaser]

GROUP STATUS COUNT

WILMINGTON ENTRYDATE 23
WILMINGTON POSITIONNO 60
WILMINGTON LOCKGDATE 123

What I wish to do is have a solid or gradient horizontal bar to the right of each group entry representing the COUNT number.

It really needn't be a bar but any chatachter, ie., >

How do I accomplish this with ACCESS 2003?

Thanks.

Rick

 

[Gammachaser]

I am interpreting your request as 'How do I get a string of length x consisting of a particular character."

For example, A string of 23 "|" characters for the first group, a string of 60 for the next, etc. so it looks like this:

WILMINGTON ENTRYDATE 23 |||||||||||||||||||||||
WILMINGTON POSITIONNO 60 ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
WILMINGTON LOCKGDATE 123 |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
If that is what you are looking for, use this:

String(x,"|")

You can substitute any character you want.

 

[sharkchaser]

Thanks Gammachaser

The numbers represented above are in a count field of a query placed in the report detail record, grouped by city names, ie. WILMINGTON example.

I have this =String([Count],"|") but it shows all the same number of "|" marks for ALL three of the counts even though they are different.

I think somehow I must reference ENTRYDATE count, POSITIONNO count and LOCKGDATE count for each city.

What do you think?

Rick

 

[Gammachaser]

To give an exact solution, I would need more information about where and how you are displaying this information: Is it a form, report, spreadsheet, Word document or ...

But... there should be a field that you are using to display the 'count' number. Just reference the field (or cell) for the count. for example, if the field you are using to display the count data is called txtCount, just use:

=String([txtCount],"|")

That works in a report and a form (I just tried it.) To display in an Excel Spreadsheet, reference the cell that contains the count data.

If that doesn't work, give more detail about your form or report and maybe we can (as we say in my field) 'Nuke it out.'

 

[PWise]

count is a reserved word try changing the count field to something else like countofcity

 

[Gammachaser]

PWise:

I had to re-read his last post to understand what sharkchaser was doing with [Count]. I had assumed (silly me) that he was just using [Count] as an example. Good catch.

sharkchaser:

PWise is right -- It is never a good idea to use reserved words for field names, control names or aliases (i.e. "SELECT Count(MyField) as Count FROM...") for many reasons, the least of which is it is confusing to read.

 

[sharkchaser]

I didn't think of that either - reserved word.

Here is the actual query which is an r937 design.

SELECT tblCARETSData.City, 'LISTINGENTRYDATE' AS count_type
,1 AS sort_order
,Count(*) AS [NewListingCount]
FROM tblCARETSData
WHERE (((tblCARETSData.ListingENTRYDate) Between #4/1/2011# And #4/30/2011#))
GROUP BY tblCARETSData.City, tblCARETSData.County
HAVING (((tblCARETSData.County)="los angeles"))

UNION ALL SELECT tblCARETSData.City, 'PENDINGDATE' AS count_type
,2 AS sort_order
,Count(*) AS [NewPendingCount]
FROM tblCARETSData
WHERE (((tblCARETSData.PENDINGDATE) Between #4/1/2011# And #4/30/2011#))
GROUP BY tblCARETSData.City, tblCARETSData.County
HAVING (((tblCARETSData.County)="los angeles"))

UNION ALL SELECT tblCARETSData.City, 'CLOSINGDATE' AS count_type
,3 AS sort_order
,Count(*) AS [NewClosedCount]
FROM tblCARETSData
WHERE (((tblCARETSData.CLOSINGDATE) Between #4/1/2011# And #4/30/2011#))
GROUP BY tblCARETSData.City, tblCARETSData.County
HAVING (((tblCARETSData.County)="los angeles"))
ORDER BY city, sort_order;

These three counts are in a detail section of a simple report.

Having a one line per count grouped on City field based on the count number would really enhance the report.

What say yee?

Rick

 

[Gammachaser]

OK, so now your 'Count' fields are NewListingCount, NewClosedCount and NewPendingCount

Use:

"=String(NewListingCount,'|')"
"=String(NewPendingCount,'|')"
"=String(NewClosedCount,'|')"

As the ControlSources for the TextBoxes where you want the "|" characters to appear.

 

[sharkchaser]

Gammachaser - I must apologize as I changed those "newlistingcount," etc. names myself.

Rudy had them ALL as "count" and it works that way.

This is really frustrating.

Thanks . . . Rick

ps. I can't seem to upload a 13k .pdf file of the report page either.

 

[PWise]

use

=String(count(*),"|")

 

[PWise]

that is if you want to use it as tje control source of a report if you want to use it in the query no need for the "="

just

String(count(*),"|")

 

[sharkchaser]

PWise

That produces a different result.

The three bars "|" goo all the way to the right, word wrap again all the way to the right and word rap again foe about an inch then ends.

Most of these count number are well below 100 but about three or four (City of Los Angeles are over that).

Somehow I need to change the results to reflect 100% or something like that.

Any ideas . . . ?

Thanks Rick

 

[MajP]

Not sure what you are doing, but I think if you do this
String(count(whatever),"G"), and format as wingdings/webdings you get a solid square. The affect is a solid bar, that looks like a progress bar with a little formatting.

 

[sharkchaser]

I tried both windings & webdings.

When I select the solid box and copy it into its field placement it just copies a "G" or "g" into the string.

No bar shows even though it does in the selection list of webdings & wingdings.

Why does this need to be so difficult?

Thanks Rick

 

[sharkchaser]

How about if I put something like this in the control. . .

IF ([count_type])="listingentrydate" then String(count)= ?????

I don't understand how to complete this but could it be the answer?

Thanks.

Rick

 

[Gammachaser]

Somehow I need to change the results to reflect 100% or something like that.

Any ideas . . . ?

The formula for getting the 100% value would be:

String((Value/LargestValue) * 100,"|")

That would give 100 bars for the largest value and the appropriate percentage of bars for the others. Naturally, you could use any other number for your maximum, and any character.

 

[MajP]

Works fine for me.
Assume I have an order details (Northwind), and I want a horizontal bar showing the percent of order

SELECT 
 [Order Details].OrderID, 
 [Order Details].ProductID, 
 [Order Details].Quantity, 
 (SELECT SUM(Quantity) from [Order Details] As A 
   where A.OrderID = [Order Details].OrderID) AS TotalOrderItems,  [Quantity]/[TotalOrderItems] AS PercentOrder, 
 String([PercentOrder]*20,"g") AS HorizotalBar
FROM 
 [Order Details];

Results:

Order ID Product                    Qty TotalOrder PercentOrder	HorizotalBar
10248    Queso Cabrales             12  27         44.44%       ggggggggg
10248    Singaporean Hokkien Fried  10  27         37.04%       ggggggg
10248    Mozzarella di Giovanni	    5   27         18.52%       gggg

 

[sharkchaser]

This is what I've have . . . .

=String(([Count])*100,"g")

1> It displays the "g" across the page all the way to the right edge for all three counts per grouping.

There are three count_types per group,

count_type

LISTINGENTRYDATE     count
PENDINGDATE          count
CLOSINGDATE          count

Each one has their results as "count"

I believe there has to be a way to distinguish which count belongs to which count_type or the formula will never work.

It can't tell which count belongs to which count_type.

Whew . . . this is giving me a headache!

Any ideas?

Thanks much. Rick

 

[Gammachaser]

Well, you are multiplying the number of occurences (the 'count') by 100!

No wonder it wraps around the page several times.

But you still don't get that you don't use 'Count.' Count is a reserved word... you need to be using your variable name that the count is associated with. Based on your code in the May 26 @ 1331 entry it looks like the variables you should be using are NewListingCount, NewEntryCount and NewClosedCount, and you should be using String(NewListingCount,"|"), String(NewEntryCount,"|"), and String(NewClosedCount,"|"). In any case, use whatever variable you are using to print the number of occurrences.

To show each bar as a percentage of the total count you can use:

String(NewListingCount/(NewListingCount + NewEntryCount+NewClosedCount),"|")

and substitute the other variables to get the other percentages.
Does that make more sense to you?

 

[sharkchaser]

OK I'm totally dazed and confused!

Here is the ORIGINAL UNION query (courtsey of r937)which produces the correct results for the detail section of the report.

SELECT tblCARETSData.City, 'LISTINGENTRYDATE' AS count_type
,1 AS sort_order
,Count(*) AS [count]
FROM tblCARETSData
WHERE (((tblCARETSData.ListingENTRYDate) Between #4/1/2011# And #4/30/2011#))
GROUP BY tblCARETSData.City, tblCARETSData.County
HAVING (((tblCARETSData.County)="los angeles"))

UNION ALL SELECT tblCARETSData.City, 'PENDINGDATE' AS count_type
,2 AS sort_order
,Count(*) AS [count]
FROM tblCARETSData
WHERE (((tblCARETSData.PENDINGDATE) Between #4/1/2011# And #4/30/2011#))
GROUP BY tblCARETSData.City, tblCARETSData.County
HAVING (((tblCARETSData.County)="los angeles"))

UNION ALL SELECT tblCARETSData.City, 'CLOSINGDATE' AS count_type
,3 AS sort_order
,Count(*) AS [count]
FROM tblCARETSData
WHERE (((tblCARETSData.CLOSINGDATE) Between #4/1/2011# And #4/30/2011#))
GROUP BY tblCARETSData.City, tblCARETSData.County
HAVING (((tblCARETSData.County)="los angeles"))
ORDER BY city, sort_order;

As you can see there are three count_type(s) and a Count(*) AS [count] for each one of them.

Frankly I don't understand it but it works.

The results query columns are in this order:
City | Count_type | Sort Order | count

The detail records are Grouped and sorted by City then sort_order.

So, using the control source for an unbound txtBox, how do I produce the expanding percent bar in the txtBox to the right of count_type (3 different count_types) and Count(*) AS [count]?

Are you confused? I am.

Rick