UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Bar Chart Data - Displaying 2 Data Groups Side By Side, Access 2000    
 
   
TinyGiant2010
post Jun 4 2020, 04:35 PM
Post#1



Posts: 229
Joined: 1-June 10



I run this query: QChart1_2020 which is this:
SELECT FREIS.[Now List], FREIS.Status, FREIS.PType, FREIS.Listed, FREIS.Twp
FROM FREIS
WHERE (((FREIS.Status) Like "A*" Or (FREIS.Status) Like "P*") AND ((FREIS.PType)="res"))
ORDER BY FREIS.[Now List];

and I use this Query as a "master data source" for other queries.
I am creating a new chart and I'm trying to isolate the results. My first effort will be on "JAC" township; and next will be "not jac*" and third will be "all"

For the first query to isolate JAC:
SELECT DISTINCTROW Partition([Now List],forms!PADC![P$MIN],forms!PADC![P$MAX],forms!PADC![P$INCR]) AS Range, QChart1_2020.Status, Count(QChart1_2020.[Now List]) AS [Count], QChart1_2020.Twp
FROM QChart1_2020
GROUP BY Partition([Now List],forms!PADC![P$MIN],forms!PADC![P$MAX],forms!PADC![P$INCR]), QChart1_2020.Status, QChart1_2020.Twp
HAVING (((QChart1_2020.Twp) Like "JAC*"));


And it returns the set of data I am interested in. Specifically, the query is looking to "Jac" Jackson Township, then looking at all records with a status of either A or P (which is what QChart1_2020 query is finding for me); then it's showing me the results of the search in tidy price brackets that I set =50000 on the form as the P$INCR value.

The first few returns show this: (sorry for poor alignment, but you get idea)
Range Status Count Twp
0: 49999 P 1 JACKSON
50000: 99999 A 2 JACKSON
50000: 99999 P 3 JACKSON
100000: 149999 A 3 JACKSON
100000: 149999 P 12 JACKSON
150000: 199999 A 7 JACKSON
150000: 199999 P 20 JACKSON

I'd been using an old chart (report) that only looked at properties in "A" status, but now I want to indicate the "P" status as well.
I pulled up the old chart, which had a record source of this:
SELECT [Range],Sum([Count]) AS [SumOfCount] FROM [QChartJackson] GROUP BY [Range];

and it showed only the A properties.

But now I want to show P alongside of A in the price bracketing on a new chart.
So I copied old chart, saved as new chart, and I am trying to tweak from there.

The new chart has this as a record source: (note the 2020 addition - I am a simpleton)
SELECT [Range],Sum([Count]) AS [SumOfCount] FROM [QChartJackson2020] GROUP BY [RANGE];

But instead of generating separate A and P bars alongside each other in each price band (which is what i want to show); what I am getting to display is a summation in the price band regardless of A or P designation. The last two rows of my past above yield an indicated value of 27 total in the 150-199 range; rather than a bar of "A" (with one color ideally) with a value of 7, and another bar alongside of it of "P" (with a different color/contrast) showing a value of 20.

I recognize I could probably export the query to Excel and do some pivot tables to create what I want, but I'd like to keep it all in Access if I can. I just don't know how to get there from here.
I also thought I'd try designing a new chart from scratch, so I retrieved an old report with 3 charts on it and deleted the first one.

In Reports, Design View, I can see the method to drag a chart down from the Controls.
Using the Chart Wizard; I set the source of the chart to Query QChartJackson2020
For fields I select Range, Status, Count
For chart, I select a common column chart, top left corner
I set vertical axis to be SumOfCount
I set horizontal axis to be Range (for price bracketing)
I set the series to Status
I name the chart and finish, grow it out a little to see some details, then check the "View - Print Preview" and I get an error message where Access doesnt' want to cooperate with the P$MIN argument.
I checked the RowSource for the chart and it has changed itself to this:

TRANSFORM Sum([Count]) AS [SumOfCount] SELECT [Range] FROM [QChartJackson2020] GROUP BY [Range] PIVOT [Status];

And bear in mind, when I look at the results of the query QChartJackson2020; I can see the results I want to chart plainly; and the query itself contains this
portion in "design view"
Range: Partition([Now List],forms!PADC![P$MIN],forms!PADC![P$MAX],forms!PADC![P$INCR])

and the entire query is visible in SQL as this:

SELECT DISTINCTROW Partition([Now List],forms!PADC![P$MIN],forms!PADC![P$MAX],forms!PADC![P$INCR]) AS Range, QChart1_2020.Status, Count(QChart1_2020.[Now List]) AS [Count], QChart1_2020.Twp
FROM QChart1_2020
GROUP BY Partition([Now List],forms!PADC![P$MIN],forms!PADC![P$MAX],forms!PADC![P$INCR]), QChart1_2020.Status, QChart1_2020.Twp
HAVING (((QChart1_2020.Twp) Like "JAC*"));

which doesn't seem monsterously complex.
Nor does the query for QChart1_2020 either, (which is the parent query, if you will) which it's SQL is this:

SELECT FREIS.[Now List], FREIS.Status, FREIS.PType, FREIS.Listed, FREIS.Twp
FROM FREIS
WHERE (((FREIS.Status) Like "A*" Or (FREIS.Status) Like "P*") AND ((FREIS.PType)="res"))
ORDER BY FREIS.[Now List];

Quite a bit of effort to show A alongside P by price bands, based on townships under examination.
Bear in mind also that my "old charts" that just showed those with the A had no issues whatsoever with the P$MIN consideration; but adding the second variable P to the mix somehow made Access cough and not process the request.

I've attached a copy of my "old report charts" which work fine to display only A / Available properties. I'm just trying to add properties in P / Pending Sale Status alongside the A's in the right price band.
I had no idea it could possibly be this complex.
I must be missing something terribly fundamental.

Another set of eyes would be highly appreciated. Sorry if I rambled a little.










Attached File(s)
Attached File  Just_A_Values_Only.pdf ( 78.15K )Number of downloads: 1
 

--------------------
Self taught newbie just trying to remember that fields are in tables and controls are on forms.
Go to the top of the page
 
June7
post Jun 4 2020, 05:11 PM
Post#2



Posts: 1,513
Joined: 25-January 16
From: The Great Land


I deal with chart issues best when I can work with data. If you want to provide db, follow instructions at bottom of my post.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
TinyGiant2010
post Jun 5 2020, 10:10 AM
Post#3



Posts: 229
Joined: 1-June 10



I have 2 forms for your consideration.
#1 - Switchboard, which I don't know why doesn't open as a Switchboard (something I've never applied to this particular DB); and
#2 - PADC form. Please open this form. It specifies the min/max/increments to use to drive the report.

The PADC button, "Graphs Avails" show what works fine for the report it will bring up in preview.

All I am trying to do is replicate this report, but instead of showing only those properties in "A" status, I'd like to also show those in "P" status side by side, in the price groupings along the horizontal axis; complete with column totals at top of each column presented.

Rather than go through all the _2020 modifications I'd alluded to previously, if the queries below can be modified to include the P data in addition to the A data, and the existing report show what I am seeking; using the "old stuff" would be just fine.

I could stop here, but as part of my process of getting the data where it needed to be, I worked up this little sheet which may be helpful here as well:
Name Type Notes
PADC Form if this form is not open, the Queries will ask for min/max/increments (made lots of stuff "visible=no" just to provide focus)
ResGroup081718b Report Currently shows only A
Qchart1 Query Looks to FREIS table for data
QChartJackson Query Looks to QChart1 for data
QChartNOTJackson Query Looks to QChart1 for data
QChartALLJackson Query Looks to QChart1 for data

I can't say strongly enough how much I appreciate the advice I receive in this forum. Sorry about not zipping, but I am ignorant on this procedure. I know what it is and why, but I can count on one hand the number of times I've done it. I'll save that learning curve for another day. Please forgive me.
This post has been edited by TinyGiant2010: Jun 5 2020, 10:13 AM

--------------------
Self taught newbie just trying to remember that fields are in tables and controls are on forms.
Go to the top of the page
 
TinyGiant2010
post Jun 5 2020, 10:25 AM
Post#4



Posts: 229
Joined: 1-June 10



For my next trick, getting the db to upload.
sheesh
Compressed and repaired file size 1,444 kb; or 1.4 MB, less than 2MB limit.
I can choose the file, see it pop up between "Choose File" and "Upload" button.
Upload doesn't want to process it?

Guess I get to learn how to zip files again. brb

What's that old saying, anything is easy as long as you know how to do it. What did we ever do without Youtube? uggg... anyway............
This post has been edited by TinyGiant2010: Jun 5 2020, 10:32 AM
Attached File(s)
Attached File  UA_chart_help.zip ( 250.08K )Number of downloads: 2
 

--------------------
Self taught newbie just trying to remember that fields are in tables and controls are on forms.
Go to the top of the page
 
June7
post Jun 5 2020, 02:20 PM
Post#5



Posts: 1,513
Joined: 25-January 16
From: The Great Land


Consider this as chart RowSource.

SELECT DISTINCTROW Partition([Now List],forms!PADC![P$MIN],forms!PADC![P$MAX],forms!PADC![P$INCR]) AS Range, Count(IIf([TWP] Like "Jac*",[Now List],Null)) AS CntJac, Count(IIf([TWP] Not Like "Jac*",[Now List],Null)) AS CntNotJac
FROM QChart1
GROUP BY Partition([Now List],forms!PADC![P$MIN],forms!PADC![P$MAX],forms!PADC![P$INCR]);

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
TinyGiant2010
post Jun 6 2020, 09:22 AM
Post#6



Posts: 229
Joined: 1-June 10



Well, it's a start! I injected your code as the row source for the top chart.
I can now see 2 bars but I'm not sure what they represent; but I'm pretty sure it is not showing A and P properties in the same price bracket.
I will refresh the data and send again.

For report ResGroup081718b; it shows 3 charts.
Chart 1 shows those records only in Jackson Township
Chart 2 shows those records NOT in Jackson Township
Chart 3 shows those records for ALL Jackson Township

Unmodified, the charts showed Available properties, those with a status designation of "A"

The modification I seek it to show a secondary vertical data series of those properties in Pending status; those with a status designation of "P".

As I skim your suggestion, it seems to tackle the township issue; but I'm not seeing any A vs P differentiation.

I've attached the MDB file with revisions below.

Please go to the Form PADC and begin there.

Thanks again for the efforts!




Attached File(s)
Attached File  UA_chart_help.zip ( 246.12K )Number of downloads: 1
 

--------------------
Self taught newbie just trying to remember that fields are in tables and controls are on forms.
Go to the top of the page
 
June7
post Jun 6 2020, 01:28 PM
Post#7



Posts: 1,513
Joined: 25-January 16
From: The Great Land


Sorry, I lost track of requirement for showing both A and P in chart. I focused on getting the in Jac and not in Jac data side by side. But if that is not actually required, need A and P side by side, consider this CROSSTAB:

TRANSFORM Count(*) AS [Cnt]
SELECT Partition([Now List],[forms]![PADC]![P$MIN],[forms]![PADC]![P$MAX],[forms]![PADC]![P$INCR]) AS Range
FROM FREIS
WHERE (((FREIS.Twp) Like "JAC*") AND ((FREIS.Status) In ("A","P")))
GROUP BY Partition([Now List],[forms]![PADC]![P$MIN],[forms]![PADC]![P$MAX],[forms]![PADC]![P$INCR])
PIVOT FREIS.Status In ("A","P");

Now if you want A and P for both in Jac and not in Jac all in one graph, that does get a bit complicated. Build two crosstab queries then join them, review http://allenbrowne.com/ser-67.html#MultipleValues
Or use conditional expressions as demonstrated in my first attempt. Don't base on a query that filters to only "A".

This post has been edited by June7: Jun 6 2020, 01:33 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
TinyGiant2010
post Jun 6 2020, 02:09 PM
Post#8



Posts: 229
Joined: 1-June 10



Okay, I've created the 3 queries to run the 3 charts. It seems easy enough to pull for Twp Like Jac*, Not Like Jac*, and blank; and these three seem to give correct results.

I created the query for the first graph showing just Jackson Township, and it seems to work almost perfectly. It does show the correct quantities of A properties alongside the P properties in the correct price banding.

I tweaked the chart design a little bit to include a legend, and the legend is not what I seek. Specifically, it shows values for one color to be "Cnt Jack"; and the other to be "Cnt Not Jac";
when they should be "A" and "P".
(Frankly, I am mystified as to how a "not Jac" was even generated, but some things I'll save for another day to learn)

The query I pasted to RowSource was this:
TRANSFORM Count(*) AS Cnt
SELECT Partition([Now List],[forms]![PADC]![P$MIN],[forms]![PADC]![P$MAX],[forms]![PADC]![P$INCR]) AS Range
FROM FREIS
WHERE (((FREIS.Twp) Like "JAC*") AND ((FREIS.Status) In ("A","P")))
GROUP BY Partition([Now List],[forms]![PADC]![P$MIN],[forms]![PADC]![P$MAX],[forms]![PADC]![P$INCR])
PIVOT FREIS.Status In ("A","P");


wait....
Just ran the report again, and the chart looks much different; and more incorrect. Let me look this over and get back in a few.

A few minutes later.........
I thought I had the right chart as I mentioned at start of this post. I'd created the crosstab query, then set the RowSource of the chart to the Query.
This was my error.
I ran the crosstab query to make sure it was "finding" results, and it was. Next I changed to SQL view, then copied/pasted the SQL into the RowSource for the chart.
Values populate correctly
Legend shows A and P correctly.
Looks like a winner!

Now, for my next 2 charts, showing Not Jack, and All Jack; I will copy the first chart and paste twice. Then open Chart #2 and insert the SQL associated with that data; then see what happens.
I am very optimistic! smile.gif

Hooray - it all works!
Thanks so much! There is no way in the wide world my little brain would have figured that out; I am very grateful!
This post has been edited by TinyGiant2010: Jun 6 2020, 02:37 PM

--------------------
Self taught newbie just trying to remember that fields are in tables and controls are on forms.
Go to the top of the page
 
TinyGiant2010
post Jun 26 2020, 09:07 AM
Post#9



Posts: 229
Joined: 1-June 10



For my next trick..... lol.

There is also a population of homes that have sold (Status S) that I'd like to toss into the mix, grouped in the same price brackets as well as by the Townships. For records that have a status of S and a sale date of >Now() - 365 (looking back no more than one year) toss those into the mix.

For Jackson Township Only, here is my existing Crosstab Query which handles A and P only:

TRANSFORM Count(*) AS Cnt
SELECT Partition([Now List],[forms]![PADC]![P$MIN],[forms]![PADC]![P$MAX],[forms]![PADC]![P$INCR]) AS Range
FROM FREIS
WHERE (((FREIS.Twp) Like "JAC*") AND ((FREIS.Status) In ("A","P")) AND ((FREIS.PType)="res"))
GROUP BY Partition([Now List],[forms]![PADC]![P$MIN],[forms]![PADC]![P$MAX],[forms]![PADC]![P$INCR]), FREIS.PType
PIVOT FREIS.Status In ("A","P");


How can I modify this to include properties with Status of S; where the SaleDate is less than today minus 365?
I'd like to see a third bar, bracketed by price groups, reflecting the qty sold in that group within the prior 365 days.

It needs to analyze if the property is in S Status, then look to a SaleDate to compare against.
It is very possible, even likely, that many properties will be in A and P status that may also have a date figure in SaleDate; so I really must evaluate on the lines of
If Status=S, then find records where SaleDate>Now()-365
But if Status=A or P, just show those per the original Transform statement above.

I opened the CrossTab and changed to design view, then added a row for S properties. I must be missing something, because when I pasted the block below into the chart rowsource, I got gibberish. I'm thinking part is the GroupBy addition of FREIS.SaleDate; but I could be wrong.

TRANSFORM Count(*) AS Cnt
SELECT Partition([Now List],[forms]![PADC]![P$MIN],[forms]![PADC]![P$MAX],[forms]![PADC]![P$INCR]) AS Range
FROM FREIS
WHERE (((FREIS.Twp) Like "JAC*") AND ((FREIS.Status) In ("A","P")) AND ((FREIS.PType)="res")) OR (((FREIS.Twp) Like "jac*") AND ((FREIS.Status) In ("S")) AND ((FREIS.PType)="res") AND ((FREIS.SaleDate)>Now()-365))
GROUP BY Partition([Now List],[forms]![PADC]![P$MIN],[forms]![PADC]![P$MAX],[forms]![PADC]![P$INCR]), FREIS.PType, FREIS.SaleDate
PIVOT FREIS.Status In ("A","P","S");

Creative suggestions welcomed.



--------------------
Self taught newbie just trying to remember that fields are in tables and controls are on forms.
Go to the top of the page
 
June7
post Jun 26 2020, 10:27 AM
Post#10



Posts: 1,513
Joined: 25-January 16
From: The Great Land


Could you provide latest version of file?

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
TinyGiant2010
post Jun 26 2020, 11:40 AM
Post#11



Posts: 229
Joined: 1-June 10



Here is latest version of file. Thanks!
Attached File  UA_chart_help02.zip ( 769.89K )Number of downloads: 3



Be sure to open form PADC,
then view Report 2020 Active Pending Charts


This post has been edited by TinyGiant2010: Jun 26 2020, 11:44 AM

--------------------
Self taught newbie just trying to remember that fields are in tables and controls are on forms.
Go to the top of the page
 
June7
post Jun 26 2020, 01:31 PM
Post#12



Posts: 1,513
Joined: 25-January 16
From: The Great Land


Don't include PType and SaleDate in GROUP BY clause.

TRANSFORM Count(*) AS Cnt
SELECT Partition([Now List],[forms]![PADC]![P$MIN],[forms]![PADC]![P$MAX],[forms]![PADC]![P$INCR]) AS Range
FROM FREIS
WHERE (((FREIS.Twp) Like "JAC*") AND ((FREIS.Status) In ("A","P")) AND ((FREIS.PType)="res")) OR (((FREIS.Twp) Like "jac*") AND ((FREIS.Status) In ("S")) AND ((FREIS.PType)="res") AND ((FREIS.SaleDate)>Now()-365))
GROUP BY Partition([Now List],[forms]![PADC]![P$MIN],[forms]![PADC]![P$MAX],[forms]![PADC]![P$INCR])
PIVOT FREIS.Status In ("A","P","S");

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
TinyGiant2010
post Jun 26 2020, 02:00 PM
Post#13



Posts: 229
Joined: 1-June 10



That worked - too well I'm afraid.

To illustrate what I mean, the A and P in Jac with prices of 100-150K might show 10=A and 15=P; but solds show almost 100 in that price bracket. Overwhelms the graph.
However, it does give me a nice start on my next chart project that shows only sold properties.

Thanks!

--------------------
Self taught newbie just trying to remember that fields are in tables and controls are on forms.
Go to the top of the page
 
June7
post Jun 26 2020, 03:09 PM
Post#14



Posts: 1,513
Joined: 25-January 16
From: The Great Land


A line-column chart will allow second Y axis. Which I am trying but having difficulty getting series to format properly. Very annoying.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
TinyGiant2010
post Jul 1 2020, 08:14 AM
Post#15



Posts: 229
Joined: 1-June 10



For my next effort - analyzing sales by price groupings.
I successfully generated a graph showing sales for the prior 365 days, grouped in price brackets. You can see the output here: http://www.freisco.com/2020_SoldQty.pdf

For the top chart, I use this as ROW SOURCE:
TRANSFORM Count(*) AS Cnt
SELECT Partition([Now List],[forms]![PADC]![P$MIN],[forms]![PADC]![P$MAX],[forms]![PADC]![P$INCR]) AS Range
FROM FREIS
WHERE (((FREIS.Twp) Like "jac*") AND ((FREIS.Status) In ("S")) AND ((FREIS.PType)="res") AND ((FREIS.SaleDate)>Now()-365) AND ((FREIS.[Now List])>1000))
GROUP BY Partition([Now List],[forms]![PADC]![P$MIN],[forms]![PADC]![P$MAX],[forms]![PADC]![P$INCR])
PIVOT FREIS.Status In ("S");

So far so good. This code works. (thanks again for all the help eveyone!)

Suppose however, I want to look back at the prior year (Now()-365) as one dataset on the chart; but I'd also like to see alongside each data set in each price group; sales data for ((Now()-365) minus (Now()-730) -sorry about bad brackets and code, I'm not a coder. (pretty evident I guess).

Anyway, to show, for instance, all homes priced 100-150K (whatever value range I set in the PADC form) for prior 365 days could show 100 units sold; but for the year before that, the qty sold might be 85 units. And for the year prior to that (Now()-???) show a 3rd data set of qty=125 for that price bracket. I'd like to see 3 columns generated for each price bracket; well actually "N" brackets depending on how many years back I might want to examine.

In a perfect world, I'd set up another field in the form PADC that asks "how many years do you want to see?" and incorporate that into the solution. We could call this field "YrsBack"

So for example, the 100-150k bracket could show 100 for the past 365 days = data for yr 1
and also show in the same bracket 85 for the period of from 365 days ago to 730 days ago; = data for yr 2
and a third data set in the same bracket for 125 units sold for the period of 730 days ago thru 1095 days =data for yr 3.

I would presume the chart is smart enough to color code these years appropriately.

Any insights would be welcomed. I think I might be pushing my luck with this one; but I thought I'd ask anyway.



--------------------
Self taught newbie just trying to remember that fields are in tables and controls are on forms.
Go to the top of the page
 
June7
post Jul 1 2020, 03:37 PM
Post#16



Posts: 1,513
Joined: 25-January 16
From: The Great Land


Getting really complicated.

For "S" criteria:

Query1:
PARAMETERS [forms]![PADC]![P$MIN] Long, [forms]![PADC]![P$INCR] Long, [forms]![PADC]![P$MAX] Long;
SELECT Partition([Now List],[forms]![PADC]![P$MIN],[forms]![PADC]![P$MAX],[forms]![PADC]![P$INCR]) AS Range, Switch([SaleDate]>Now()-365,"Current",[SaleDate]>Now()-730,"Previous") AS SDPer, Count(*) AS Cnt
FROM FREIS
WHERE (((FREIS.Twp) Like "jac*") AND ((FREIS.Status) In ("S")) AND ((FREIS.PType)="res") AND ((FREIS.SaleDate)>Now()-730) AND ((FREIS.[Now List])>1000))
GROUP BY Partition([Now List],[forms]![PADC]![P$MIN],[forms]![PADC]![P$MAX],[forms]![PADC]![P$INCR]), Switch([SaleDate]>Now()-365,1,[SaleDate]>Now()-730,2);

Chart RowSource:
TRANSFORM First(Query1.Cnt) AS FirstOfCnt
SELECT Query1.Range
FROM Query1
GROUP BY Query1.Range
PIVOT Query1.SDPer;

For "A" and "P"
Query1:
PARAMETERS [forms]![PADC]![P$MIN] Long, [forms]![PADC]![P$INCR] Long, [forms]![PADC]![P$MAX] Long;
SELECT Partition([Now List],[forms]![PADC]![P$MIN],[forms]![PADC]![P$MAX],[forms]![PADC]![P$INCR]) AS Range, Switch([SaleDate]>Now()-365 And [Status]="A","CurrentA",[SaleDate]>Now()-365 And [Status]="P","CurrentP",[SaleDate]>Now()-730 And [Status]="A","PreviousA",[SaleDate]>Now()-730 And [Status]="P","PreviousP") AS SDPerSta, Count(*) AS Cnt
FROM FREIS
WHERE (((FREIS.Twp) Like "jac*") AND ((FREIS.Status) In ("A","P")) AND ((FREIS.PType)="res") AND ((FREIS.SaleDate)>Now()-730) AND ((FREIS.[Now List])>1000))
GROUP BY Partition([Now List],[forms]![PADC]![P$MIN],[forms]![PADC]![P$MAX],[forms]![PADC]![P$INCR]), Switch([SaleDate]>Now()-365 And [Status]="A","CurrentA",[SaleDate]>Now()-365 And [Status]="P","CurrentP",[SaleDate]>Now()-730 And [Status]="A","PreviousA",[SaleDate]>Now()-730 And [Status]="P","PreviousP");

ChartRowSource:

TRANSFORM First(Query1.Cnt) AS FirstOfCnt
SELECT Query1.Range
FROM Query1
GROUP BY Query1.Range
PIVOT Query1.SDPerSta;

This post has been edited by June7: Jul 1 2020, 04:18 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
strive4peace
post Jul 1 2020, 05:15 PM
Post#17


strive4peace
Posts: 20,532
Joined: 10-January 04



Hi Steve,

you can show colum markers stacked instead of side-by side


in design view, double-click to edit chart -- figure 1

2. right-click on chart choose Chart Type -- figure 2

3. choose Stacked Column for Chart Type -- figure 3


and here is what that looks like in design view: -- figure 4



On a chart, when you need to see detail in small, as well as big, numbers, you could use an exponential scale instead of a linear one. This shows relative changes in multiple ranges without going off the graph as often
1. right-click and Format Axis -- figure 5


2. choose Logarithmic Scale -- figure 6



Here is what that looks like: -- figure 7



~~~
other notes:

if you press Ctrl-F in the Navigation Pane, that turns on the Search bar (Find)

imo, its also nice to show overlapping windows instead of tabs in the Options

--- tip: on names
don't start any kind of names that you create (field, table, control, form, report, etc) with a number, ever ... something like this will bite you, "2020_AP_Jackson" -- to avoid the problems that can happen when name starts with a number or special character, always start names with a letter, so maybe "y2020_AP_Jackson"

~~~

I see that you slice and dice data a number of ways. Now that I see your dashboard, I want to mention another way that's nice to show data graphically, but it isn't using charts -- it is using a query. How? You might ask. Because you can use a unicode character to represent a bar on the graph and repeat it a specified number of times.

CODE
   TheGraph: String( CInt( expressionHowMany ) , ChrW(9600) )


When using characters in a query to show bar charts, its good to have an idea of the scale of the number (ones, tens, thousands, tenths, hundredths) so you divide/multiply appropriately for the amount of space you have to display. In a control on a form, you can also set font size and use conditional formatting.

And still yet another option is using a report object and drawing with the Report .Line (also draws rectangles), .Circle (/oval), and .Print (write text) methods. I've always been into charts and now a whole new world opens when you start to see what you can really do with the drawing tools for Access reports using VBA.

~~~

@June -- you're good!

------------------
sorry I'm not better at putting figures where they belong
This post has been edited by strive4peace: Jul 1 2020, 05:25 PM
Attached File(s)
Attached File  fig01_double_click_to_edit_chart.jpg ( 111.51K )Number of downloads: 0
Attached File  fig02_right_click_on_chart_ChartType.jpg ( 31.86K )Number of downloads: 0
Attached File  fig03_StackedColumn_ChartType.jpg ( 77.7K )Number of downloads: 0
Attached File  fig04_stackedChart.jpg ( 62.63K )Number of downloads: 0
Attached File  fig05_FormatAxis.jpg ( 7.12K )Number of downloads: 0
Attached File  fig06_Logarithmic_Scale.jpg ( 55.75K )Number of downloads: 0
Attached File  fig07_Results_Log_Scale.jpg ( 51.36K )Number of downloads: 0
Attached File  fig07_Results_Log_Scale.jpg ( 51.36K )Number of downloads: 0
 

--------------------
have an awesome day,
crystal

Microsoft MVP
Remote Training and Programming -- let's connect and build your application together! MsAccessGurus.com
~
video: Bar Graphs in Access Query using Unicode
Go to the top of the page
 
TinyGiant2010
post Jul 1 2020, 06:37 PM
Post#18



Posts: 229
Joined: 1-June 10



It is late for me to begin to digest your suggestions, but I will tomorrow if I can grab the time.

Just to clear things up. I'd originally asked how to show A and P data in the chart and was successful in those efforts. The advice from this forum was very effective and I am very pleased, as it shows precisely what I wanted to see.

What I'd asked about in my most recent post is for properties with the Status=S only, with no intention of showing A+P+S all in one chart. My latest post is just focusing on sold data only. I'd considered posting my idea as a separate thread, but I didn't want to undo or overlook the good advice regarding the methods for A and P; I wanted to help avoid recreating a wheel so to speak.

Thanks again for the pointers! I'll jump into them very soon.


--------------------
Self taught newbie just trying to remember that fields are in tables and controls are on forms.
Go to the top of the page
 
June7
post Jul 1 2020, 07:10 PM
Post#19



Posts: 1,513
Joined: 25-January 16
From: The Great Land


Did you see post #16?

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
TinyGiant2010
post Jul 2 2020, 08:18 AM
Post#20



Posts: 229
Joined: 1-June 10



From Post 16, just the sales section: (My issues with A and P have already been solved 100%)
For "S" criteria:

Query1:
PARAMETERS [forms]![PADC]![P$MIN] Long, [forms]![PADC]![P$INCR] Long, [forms]![PADC]![P$MAX] Long;
SELECT Partition([Now List],[forms]![PADC]![P$MIN],[forms]![PADC]![P$MAX],[forms]![PADC]![P$INCR]) AS Range, Switch([SaleDate]>Now()-365,"Current",[SaleDate]>Now()-730,"Previous") AS SDPer, Count(*) AS Cnt
FROM FREIS
WHERE (((FREIS.Twp) Like "jac*") AND ((FREIS.Status) In ("S")) AND ((FREIS.PType)="res") AND ((FREIS.SaleDate)>Now()-730) AND ((FREIS.[Now List])>1000))
GROUP BY Partition([Now List],[forms]![PADC]![P$MIN],[forms]![PADC]![P$MAX],[forms]![PADC]![P$INCR]), Switch([SaleDate]>Now()-365,1,[SaleDate]>Now()-730,2);

Chart RowSource:
TRANSFORM First(Query1.Cnt) AS FirstOfCnt
SELECT Query1.Range
FROM Query1
GROUP BY Query1.Range
PIVOT Query1.SDPer;


xxxxxxxxxxxxxxx
It seems to me (an uneducated programmer) that the instances of "Now()" above would not be what I desire.
Given sales qty (imaginary) that over the past 365 days, 75 units sold.
For the period between day 366 and day 730; 88 units sold;
and from day 731 thru 1096; 38 units sold.
All sales qty are for just one "price bucket" which gets defined on form PADC.

When I peek at the code above, it seems that the past 365 would show 75 units,
but the next period would show 75+88 units, because the first 75 weren't blocked out.
and for the 3rd year back data would show 75+88+38 units in that bucket because the 75 units + 88 units weren't blocked out.
What I'm trying to glean is the bar chart showing that particular price bucket with 3 columns, with 75 units alongside 88 units, alongside 38 units. (If I am lucky, I will be able to show "Color 1=prior year" and "Color 2=days 366-730" and "Color 3=days 731-1096")
I think (again I am not a smart man) that the code would be providing cumulative totals, rather than discrete totals for each time period.

Perhaps there needs to be some sort of nesting/loop. (brackets probably wrong)
Yr 1= prior 365 days = Now()-365 ;
Yr 2 = ((Now()-730) - (Now()-365));
Yr 3 = (((Now()-1096) - ( ((Now()-730) - (Now()-365)) ) )

As I may be running a low grade fever (contemplating a Covid test) my mind isn't the clearest right now.
This post has been edited by TinyGiant2010: Jul 2 2020, 08:21 AM

--------------------
Self taught newbie just trying to remember that fields are in tables and controls are on forms.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    4th July 2020 - 01:43 PM