Data Label Conundrum in XY Scatter Chart

[Paul H]

I have a chart that displays all the points of data I want it to. I have a form that filters a crosstab query. I have named and ordered the column headings. Each time the query runs it has the same number of columns. All my data series are in the right order and the legend symbols and colors stay consistent. When I don't use the wild card only one column is populated plus 5 other columns that are always populated. These 5 columns are the last columns in the query. This may all be too much information, but I'm trying to anticipate questions. My chart functions perfectly at this point, but here's where I am running into problems.

1. I want data labels on a single point of the last 5 data series.
2. I want a line on each of those data series

The data in these columns never changes. There are always two data points, one is zero and the other is a constant. Both numbers are stored in a table. Visually the lines will fan out from the center point (0,0) to each of the constant values. The constant values have the data labels. They want them all formatted the same, big red triangles and big red lines. Here are the problems.

1. I can't get the line to show up in the data series.
2. The labels appear then disappear depending on which value I filter by.
3. The lines do too.
4. The position of the labels change.
5. The labels keep reappearing on the data points that I have formatted not to display.

I've tried to step through each iteration and fix it till I have each one just right, but each time I fix one, it blows out something in a different one. At one point I had this close to working, but now I'm losing ground. I've tried 6 ways from Sunday to get this to work. I hate to walk away from it, but I'm facing a deadlinie.

Note: I've removed the screenshots and the sample database. I'll be uploading some better examples and a more generic version of my database shortly.

Here are some new snaps. Two of these are formatted correctly. Notice on the bottom one (snap 10) out that there are red triangles at grid point (0,0). Also some of the data labels are showing the X Axis value. If I were to go in and fix this iteration it would most likely blow the two correct ones out. I am including a new sample database in case you want to play with this.

Mod edit: attachments removed at poster's request.

[June7]

Be aware UNION query will not retain duplicate rows unless use ALL qualifier. I am not seeing any combinations of Group, Sample, P1, P2 that will result in duplicates but keep that in mind.

I think Lines aren't showing because data is not continuous in the query. I filter for OB data and get line to show for OB but it is broken because of gaps. When all data displays there are lots of gaps, so many that all lines virtually disappear.

Why do I get warning message when closing report and the form is open: The object is locked. Any changes you make will be discarded with the form closes.

[Paul H]

A duplicate row would be a real fluke, but is possible and worth anticipating.

Re:line I guess I have not understood why lines have been inconsistent. Since these dataseries are always mixed in with the others, they will almost never be continuous. I thought since they were the only two points in the dataseries they would connect, hence the including the zero values in the UNION query. The lines were requested by the customer who managed to incorporate them into her Excel prototype by manually keying in continuous values in her data sources window, something I don't think I can do if I stick with Access.

Re: the warning Ever since I've been using Pop up forms to manipulate my charts, I've been getting these warning messages. They don't seem to do any damage and don't show up when I open and close the reports using code. Answer? I don't know.

The real bugaboo are the data labels and the markers on the 5 data series in question.

[June7]

There may be only two records in the series with values but Access has to deal with all the other records in the series and if the field is null then that causes break.

I just learned yesterday that even the ALL qualifier won't assure that duplicate records are retained in the query. Very disappointing.

[Paul H]

There may be only two records in the series with values but Access has to deal with all the other records in the series and if the field is null then that causes break.

I get that and don't see a fix for it in Access. For now, I have moved the chart into Excel. Manually keying in the values in each of those 5 dataseries fixes all the formatting problems I have been having. I've done some programming on the Excel end using an Autofilter. This works pretty well. From here I have two choices. Create the whole application in Excel (not my first choice) or pulling the Excel Chart into Access, which I have never done, till now. I managed to get the chart onto my form using the tips I found here. It's late and I'm going to quit for the day, but my next step will be to figure out how to update my Excel chart from Access. I'll let you know tomorrow how I'm doing.

Paul

[Paul H]

I've come up with a form in Excel that works pretty much as I wanted to do in Access. I may do all my data management in Access and then shoot them over to this Excel file to view and manipulate the chart. I learned to program in Excel, but have been away from it for years. It is nifty to be able to record and edit your macros, but I found form development to be somewhat of a bear. This is going to get me where I need to go in the short run. Considering all the headaches of chart development in Access I might just give it up and use Excel in the future. I know you can view the Excel chart in a form or report. Once I learn how to manipulate and update it from Access I may have just what I need. I'm going to mark this as solved, but feel free to add to the discussion if you have any comments.

[Paul H]

June,

As stated, I would like to know how to update this chart from access and view the results on my form. Do you have any links I could follow up with. I've found precious little during my searches and the examples I have found haven't worked.

Thanks,

Paul

[June7]

Sorry, no experience with that and don't know any references. There isn't much about charting in Access to be found.

[Paul H]

Thanks,

Hope you are staying warm up there in The Great Land. Right now it's 1 degree colder here than in Anchorage, but that's bound to change real soon. I see it's -31 in Fairbanks... Ha!

Paul