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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Problems With Charts, Access 2007    
 
   
LeicsChris
post Jul 7 2019, 02:00 PM
Post#1



Posts: 125
Joined: 23-May 17



Hi All

We have just bred a litter of puppies and I have been building a small database to keep data about them. One of the needs is to monitor their weight on a daily basis ensuring each pup is gaining weil

Amongst others, I have the following tables;

PuppieTB
PuppieID - Primary Key
PuppySex - Male/female
PuppyName - text
PuppyLitter - Foreign Key to litter table

This has 8 records, one for each of the eight puppies, Puppy1 to Puppy8

WeightTB
WeightID - Primary Key
PuppyWeighed - Foreign Key to Puppy Table
DateWeighed - Short Date

I currently have 24 records, all eight pups weighed for last 3 days.

I would like a multi line gragh which shows the daily weight of each pup, one pup per line. The Xis being the daily date and the Y axis being the weight scale.

Using a crosstab query I can produce the following;


PUPPY 06/07/2019 05/07/2019 04/07/2019
Puppy1 318 347 420
Puppy2 330 373 431
to
Puppy8 301 336 384


I have tried to use the gragh wizard in both a blank form and blank report but have been unable to get the report right.I have copied and pasted the query results into excell and got the desired gragh but for some reason not in access, and access wizard only allows 6 fields and each puppie shows up as a separate field?

How would this best be achieved as I am scratching my head. I have designed more complicated databases but never had to use graghs before, and I understand Access is harder than excell to get graghs to work as required.

Thanks in advance

Chris
Go to the top of the page
 
June7
post Jul 7 2019, 02:39 PM
Post#2



Posts: 725
Joined: 25-January 16



The wizard is somewhat limiting but once chart is created, can be modified, including SQL for RowSource property.

I think your CROSSTAB is backwards. Puppy ID/names should be column headers and dates should be row header.

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

This post has been edited by June7: Jul 7 2019, 02:50 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
LeicsChris
post Jul 7 2019, 03:21 PM
Post#3



Posts: 125
Joined: 23-May 17



Attached File  Puppy_Zipped.zip ( 44.03K )Number of downloads: 5


Hi

Database is attached, its early days and very basic

Thanks

Chris
Go to the top of the page
 
isladogs
post Jul 7 2019, 03:33 PM
Post#4


UtterAccess VIP
Posts: 1,541
Joined: 4-June 18
From: Somerset, UK


Hi Chris
You might find it useful to look at the chart in my Currency Exchange Rate Tracker app as this shows a similar output to what you want.
See http://www.mendipdatasystems.co.UK/cert/4594365453
Hope that helps

--------------------
Go to the top of the page
 
June7
post Jul 7 2019, 03:34 PM
Post#5



Posts: 725
Joined: 25-January 16



There is no report nor form with a graph. I cannot analyze an attempt that isn't there.

Did you try graphing the query I suggested? You have the query. Now use it as RowSource for line or bar graph. After graph is created, change the RowSource to that query name.

This works as long as there is only 1 litter. If you want to track multiple litters, this could get more complicated.

This post has been edited by June7: Jul 7 2019, 03:49 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
ADezii
post Jul 7 2019, 03:41 PM
Post#6



Posts: 2,538
Joined: 4-February 07
From: USA, Florida, Delray Beach


To me it seems as though a Clustered Column Chart would be more meaningful. Using some very simple Data over the course of three days, the Chart (on a Report) displays the weight variances over these three days for the eight dogs. All eight dogs were weighed on each of the three days. Weight Table related to the Puppy Table as such:
CODE
PuppieTB.[PuppieID]{PK-1} ==> WeightTB.[PuppyWeighed]{FK-MANY}

This post has been edited by ADezii: Jul 7 2019, 03:44 PM
Attached File(s)
Attached File  Puppy.JPG ( 70.3K )Number of downloads: 6
 
Go to the top of the page
 
LeicsChris
post Jul 7 2019, 04:11 PM
Post#7



Posts: 125
Joined: 23-May 17



Hi All

I have added a new crosstab query the other way round and now the puppy names are column headings and date weighed are row headings.

I have also added a form with my attempted gragh which still doesnt work. I couldnt see how to change the record source in the gragh.

I also added a menu screen with button to open the gragh form.


Attached File  Puppy_Latest.zip ( 50.55K )Number of downloads: 3


I would like the gragh to look similar to the picture below

Attached File  test_gragh.jpg ( 93.05K )Number of downloads: 5


Thanks

Chris
Go to the top of the page
 
isladogs
post Jul 7 2019, 04:11 PM
Post#8


UtterAccess VIP
Posts: 1,541
Joined: 4-June 18
From: Somerset, UK


Personally I find the approach suggested by adezii really difficult to visualise changes over time for each puppy.
I think the attached screenshot gives an easier way to visualising the changes.
Substitute puppies for currency exchange rates in the example shown

Attached File  Capture.PNG ( 111K )Number of downloads: 4

--------------------
Go to the top of the page
 
LeicsChris
post Jul 7 2019, 04:21 PM
Post#9



Posts: 125
Joined: 23-May 17



Hi Colin

I agree with you, the line gragh is much clearer and would instantly show a puppy losing weight or not growing the same proportion as others, and your currency one is very similar.

I have downloaded it and will look at how you laid it out.

Thanks
Go to the top of the page
 
June7
post Jul 7 2019, 09:41 PM
Post#10



Posts: 725
Joined: 25-January 16



Changing graph RowSource is simple. Form or report in design view, select the chart, change the RowSource property. All I had to do was select the query name from dropdown list. You already had the query built - PuppyWeightTB_line.

If you want the lines to separate more, change Y axis scale Minimum, Maximum, Major, Minor unit values. Multiply data by 10 if you want thousands. Sample chart does not show same data as the database.


This post has been edited by June7: Jul 7 2019, 10:05 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
WildBird
post Jul 7 2019, 11:06 PM
Post#11


UtterAccess VIP
Posts: 3,594
Joined: 19-August 03
From: Auckland, Little Australia


I would export the data to Excel. I find Access very limiting in what it can do with graphs etc. Excel far superior for analysis purposes. Can be automated as well easily.


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
isladogs
post Jul 8 2019, 02:06 AM
Post#12


UtterAccess VIP
Posts: 1,541
Joined: 4-June 18
From: Somerset, UK


Attached is a modified version of your database with a chart form based on a new crosstab query as suggested earlier.
Attached File  Capture.PNG ( 17.91K )Number of downloads: 2

The chart was done in A2010 on a tablet and is very basic. You should be able to improve its appearance in Access.
Or if preferred do in Excel
Attached File(s)
Attached File  Puppy_Master___v2_CR.zip ( 89.81K )Number of downloads: 2
 

--------------------
Go to the top of the page
 
LeicsChris
post Jul 8 2019, 08:28 AM
Post#13



Posts: 125
Joined: 23-May 17



Hi

Thanks Isladogs, that is perfect and just what I was looking for.

I will now play with the presentation and delve in and see how you achieved it.

Thanks again

Chris
Go to the top of the page
 
ADezii
post Jul 8 2019, 01:00 PM
Post#14



Posts: 2,538
Joined: 4-February 07
From: USA, Florida, Delray Beach


Why not consider the best of both worlds? You can create a Chart in Excel from within Access using Automation Code then fine tune it in Excel. I used isladog's Data and Queries in the Attachment that he posted in Post #12. Just a rogue idea.

P.S. - Uses Early Binding and ADO.
Attached File(s)
Attached File  Create_Puppy_Chart.zip ( 31.47K )Number of downloads: 2
 
Go to the top of the page
 
June7
post Jul 8 2019, 01:17 PM
Post#15



Posts: 725
Joined: 25-January 16



Excel is an option for complex charting. However, I think this one is simple enough for Access. I have several dbs with over a dozen charts altogether and all are constructed totally within Access. I do use VBA code to manipulate axis scales and titles.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
isladogs
post Jul 8 2019, 03:54 PM
Post#16


UtterAccess VIP
Posts: 1,541
Joined: 4-June 18
From: Somerset, UK


I also think this is a fairly simple chart that doesn't really need the additional charting facilities available in Excel.
Normally I do all my charts in Access as in most cases it is more than adequate for the job.


--------------------
Go to the top of the page
 
ADezii
post Jul 9 2019, 05:41 AM
Post#17



Posts: 2,538
Joined: 4-February 07
From: USA, Florida, Delray Beach


I agree with you guys also, simply appeared that the OP was inclined to use Excel as far as Charting goes and if this is the case, just another option to consider.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th August 2019 - 01:18 PM