Graphs using records...... 2

[psdcc]

I apologize for what is likely a blazingly obvious question, however it continues to plague me....

I only have MS Office package installed, so other applications like Crystal Reports aren't available to me.

I have a set of tables with highly summarized datafields. Each record represents one client. I would like to create a profile of each client which will include tables and a graph. Each client would have their purchase history (one of the fields) listed in a table and a graph depicting their purchase amount in $ across 4 years. The data for each year's amount is a separate field in the table.

In the past, I have simply created a profile in Word, inserted the Access fields and done a mail merge. However, when I include graphs, I'm left to creating them in Excel and copy and pasting them into Word.

My question: using Reports in Access, is it possible to create a graph for each record using a defined set of fields? When I have tried using the chart feature, it creates one graph with every record included.

I'm open to any suggestions using Word/Excel/Access etc.

Cheers!

psdcc

 

[dhookom]

An Access graph on a form or report is somewhat similar to a subform/report. You can set the Link Master/Child properties to limit the graphed records to a specific subset.

Depending on your version of Access, you may have some issues with graphing the wrong data for the client so you might need to add code to requery the graph object.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GingerR]

I use Access solely for charts for years now. No more Excel.

So...piggybacking on Duane's post...you'd make a report, and make it's recordsource be something that results in a list of unique Clients. Put the ClientID on the report. Put the chart on the report. You can set the master/child links to ClientID. Sometimes that hasn't worked for me, so in the chart's rowsource I put the critieria for i.e. ClientID:

Reports!ReportName!ClientID

and it doesn't the same thing.

Let us know how it goes.

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[psdcc]

Wow!!! Excellent, I actually got a graph to work for each client!! My problem has apparently been setting the master/child links.

I have an additional question and I think I just have to rearrange my thinking..

In excel, I can create a graph using data in 4 fields as my data source. When I insert a chart in Access, I seem to only be able to include data from a single field. Is this just a limitation of the wizard or do I just drag and drop the additional fields in the field box?

Thanks so much for both the responses!

 

[psdcc]

Ah ha! I gave it another shot and was able to add additional fields. Absolutely Brilliant. I knew it was something blatantly obvious.....I'm glad I found this forum!!!!

Thanks again for all the help!

psdcc

 

[psdcc]

Wonderfully, I am now able to do bar charts and scatter graphs quite easily but I am having a tougher go at pie charts. At the moment, I have three fields that are a percentage of 100. For example, Field A = 30%, Field B = 60%, Field C = 10%. In the Pie chart, I am only given the option of including one field. Should I reformat my table or might this be another issue of related fields?

psdcc

 

[dhookom]

I think you could reformat your data using a union query to normalize from three fields to three records. You might also try change the chart from row-wise to column-wise (caution WAG).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[psdcc]

Thanks dhookom....I played around with the data in Excel, transposed it and then reloaded into Access and it all worked. But, I'd like to try rearranging the data using the union query as you mentioned. What is involved in doing that? At the moment all the data is in three fields in one table. I'm not sure where the union would apply.

psdcc

 

[dhookom]

I don't know your table or field names or what type of values you are storing. However, you would need to create a union query like:
SELECT ID, Field1 as TheValue, "Fld1" as Yr
FROM tblA
UNION ALL
SELECT ID, Field2, "Fld2"
FROM tblA
UNION ALL
SELECT ID, Field3, "Fld3"
FROM tblA;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]