Stupid Question

[Tcmarsh43]

After working on this for a few days I am stuck and believe I may have been going about this all wrong.

I have 1 large database holding various fields etc. Then I was using a form where a user could select specific values from the master database. I was hoping to create various graph outputs based on the selection from the user.

Is this even possible to do in access? I cant seem to figure out how. I try to create a graph on the form but it says it can only be made via a table.

Any clues?

 

[GinaWhipp]

Yes, it can be done by basing your Chart off of a query. You need the query abbreviated sample below...

SELECT tblCoilReceiving.crCustomerID, etc...
WHERE (((Format([crRunDate],"yyyy"))=[B][COLOR=red][Forms]![frmPromisePerformance]![cboYear][/COLOR][/B]) AND ((Format([crRunDate],"m[B]"[/B]))=[B][COLOR=red][Forms]![frmPromisePerformance]![cboMonth][/COLOR][/B]))
ORDER BY tblCoilReceiving.crCustomerID;

Then your Form with Combo Boxes...

Then you need your Form with your fields as you can see by the above. Add your Chart and base it off the query. After selection of your Combo Box selection requery your Chart so it will refresh the results. Hmm, you might need to put the Criteria in after you create the Chart. If I remember correctly you may run into an issue trying to base the Chart off a query that has Criteria.

 

[Tcmarsh43]

Thank you so much for your reply. I will definately we trying your advice out tomorrow morning. Just about to head out from the office for the day. I will make sure I post a followup message once I have attempted to implement it.

Thanks again,

-Tyler

 

[GinaWhipp]

No problem... I'll be here!

 

[Tcmarsh43]

I am trying to go about doing what you suggested however my query I am trying to make which will have all the variables needed keeps outputting incorrectly.

The code I have for it looks like this:

SELECT QueryMarketNamesWorking.MARKET_DESC, [sah-marsty1-Thu_Jun_12_17_02_37].SITE, [sah-marsty1-Thu_Jun_12_17_02_37].EUTRANCELL, [sah-marsty1-Thu_Jun_12_17_02_37].Day, [sah-marsty1-Thu_Jun_12_17_02_37].L1_DL_Thpt

FROM [sah-marsty1-Thu_Jun_12_17_02_37], QueryMarketNamesWorking, QuerySiteNameWorking, QuerySectorWorking

WHERE (((QueryMarketNamesWorking.MARKET_DESC)=[Forms]![MainInterfaceForm]![MarketName]) AND (([sah-marsty1-Thu_Jun_12_17_02_37].SITE)=[Forms]![MainInterfaceForm]![SiteName]) AND (([sah-marsty1-Thu_Jun_12_17_02_37].EUTRANCELL)=[Forms]![MainInterfaceForm]![SectorNumber]) AND (([sah-marsty1-Thu_Jun_12_17_02_37].Day) Between [Forms]![MainInterfaceForm]![BeginDateText] And [Forms]![MainInterfaceForm]![EndDateText]));

I went ahead and attached some pictures to better explain whats going on for me. In capture 1, it shows the basic values I selected. Then in Capture2 It shows the new query being run and it is getting the correct values 18.28 and 15.22 for both the first and the second. The problem is it does this like 1-2 thousand times.
Capture4 and 5 are just of the table but I dont think they would be as useful.

 

[GinaWhipp]

I don't know what "...outputting incorrectly..." means. What should it show and what is it showing?

 

[Tcmarsh43]

I should only have 2 rows of information showing. Not several thousand.

 

[GinaWhipp]

Okay, so first, you've got 4 queries there. Are they all INNER joins? That query needs to work without the parameters (critieria) and if it's returning thousands of records then it's because one or more of those queries is returning that many records.

 

[GinaWhipp]

Wait a minute... you have 4 queries/tables in this query. How are they joined? You can't just drop them in there. My example was based off one table so there are no joins but yours, well, those four need to be linked.

 

[Tcmarsh43]

No I didn't inner join them. So if I am using multiple queries together I need to inner join them. Fair enough.

Is there a simple way to narrow down which query is causing this to happen.

The method I found that produced the fewest number of rows (1866) was using only 1 of those queries and the rest coming the main table.

I linked a picture of the design view since I am probably poorly explaining things.

 

[GinaWhipp]

Okay, before we worry about the results with criteria we need to get the data to display correctly without criteria. There is no narrowing down, throwing four queries into a single query will cause a cartesian join...

A CARTESIAN join (or product) is no join between tables. This produces a query the will give you every combination possible between the tables in the QBE window.

So, we need to make the query work first then add the criteria.

 

[Tcmarsh43]

Ah that make sense sort of. How am I suppose to create a query though to find lets say, Site names or EUTRANCELL names without using a criteria to specify where on the form that data is originating from?

I know it's going against what you just said but what if I made 4 individual small queries with the criteria in there? Then tried to create 1 larger querier which called the other 4 queries? But I suppose then I would end up with the same problem wouldn't I. A Cartesian Joint as you called it.

 

[Tcmarsh43]

Ok I think I have it working now Well at least the query portion! Which is uplifting seeing as ive been pulling my hair out all day over this ^.^ . Here is what it looks like now.

Now to try and work through your explanation about creating a chart Ill post an update on the progress after a meeting I have now. (Unless you are tired of my constant barrage of questions)

Either way, I do greatly appreciate all the help you have given me so far! It is nice to see people helping out others not because they are required to but because they can

 

[GinaWhipp]

Hmm, I see you are answering your own questions now Glad you got that part figured out.

Not getting tired, so move on to part two when you can and I'll keep my eyes peeled for more questions.

 

[Tcmarsh43]

Alrighty, I have my graph working correctly now with 1 "site name" being used. I wanted to expand this to allow for multiple "Site Names" though. I thought maybe under criteria for my query which the graph was tied to I could simply change my criteria from:

[Forms]![MainInterfaceForm]![SiteName]

to this:

[Forms]![MainInterfaceForm]![SiteName] And 
[Forms]![MainInterfaceForm]![AdditionalSiteName1] And 
[Forms]![MainInterfaceForm]![AdditionalSiteName2] And 
[Forms]![MainInterfaceForm]![AdditionalSiteName3] And 
[Forms]![MainInterfaceForm]![AdditionalSiteName4] And 
[Forms]![MainInterfaceForm]![AdditionalSiteName5] And 
[Forms]![MainInterfaceForm]![AdditionalSiteName6]

I have quickly found out that this will not work. Or at least not how I have it currently. My graph still outputs correctly for the

[Forms]![MainInterfaceForm]![SiteName]

part but doesnt output the others. Any ideas what could be happening?

 

[GinaWhipp]

You would need to set up the graph to show more sites before setting up the query to allow the input of more sites. Then you'd have to use something like a multi-select List Box. (And that I have never done before! )

You know with the limited capabilities of Access Graphs have you considered throwing the data out to Excel and displaying the graph there?

 

[Tcmarsh43]

Ya, from all my reading, everyone seems to have the same opinion that access graphs are pretty limited in comparison to excel (which I love using and am quite decent at if I do say so myself ). Ya I would do that but this access database setup isnt for my own use specifically. I am making it for the analysis team here and will be on the shared drive. I want it to be as easy and streamlined to use as possible.

Ill hit the drawing board again about getting the graph to display multiple site parameters and see what I come up with.

I am still grateful for your continued help I have managed to get my form working correctly (for 1 site at least ) and have it all setup so with the click of a button it spits out a chart (see attached image if you like ^.^) I know it doesnt look very aesthetically appeal right now but I'm just trying to get things working before that stage .

 

[GinaWhipp]

No problem, that's what I'm here for!

Side note: You can still make it visible to everyone. After you throw it out to Excel you can put a button on the Form to open it right there in Excel. And, you can throw the workbook into the same directory as the database.

 

[Tcmarsh43]

Hmm? I think I follow what you are saying, but I do not believe I have correctly explained this databases purpose. Since I am storing all this data here I want those who use it to be able to set a few parameters and then output a graph to see if changes made to our current infrastructure have been beneficial or relatively ineffective.

So I could run it into excel but I wouldn't know the parameters they will set ahead of time to create a corresponding graph in excel. Or am I misunderstanding you?

 

[GinaWhipp]

Okay, I didn't explain that correctly. You create a Graph in Excel on one worksheet and put the parameters for the Graph on another sheet. In Access have a Form where you select what parameters you want to send to Excel.

Users open the database, select the parameters, click a button sending to the specified worksheet and then opening the updated Graph. They could click that button all day long if the want.