Stupid Question

So if I understand correctly, essentially you have a query in access with the columns of data etc. Then in Excel you have it setup so it copies those columns and populates a graph then access pulls that graph back and displays it? (Sounds kinda confusing to me. I've only interconnected workbooks and spreadsheets not other applications ^.^)

Well I have managed to make some progress with my graph situation. But I think I've hit another wall >.< Several problems actually. I created a query for each possible additional site name to be added. Then I was planning on setting up a union to link them together.... I had a whole large paragraph explaining why my union query wouldn't work if a location was left empty... However, while typing this I reattempted it and now it works :o

The other problem I have is, when I switch the UnionQuery from SQL view to pivot chart I see pretty much exactly what I want in the end, but I do not know how to automate the process.

So basically my main objective now is to figure out how in the world to get the pivot chart/graph to somehow be an automatic process rather than having to switch from SQL View to Pivot Chart and manually insert the parameters.


I placed a few images in here to help visually show a few things I mentioned. The first image,"Capture", is of the pivot chart the way I like it *but have no clue how to automate*
The second image,"Capture1", is the code I'm using for the union.
And the third image,"Capture2", is the datasheet view of the union query running.
 

Attachments

  • Capture.jpg
    Capture.jpg
    88.2 KB · Views: 101
  • Capture1.JPG
    Capture1.JPG
    57 KB · Views: 102
  • Capture2.jpg
    Capture2.jpg
    70.2 KB · Views: 105
Hmm, it does not pull the graph back into Access, it opens in an Excel Workbook. Steps...

1. Select vaules from Combo Box or List Box or combination of both
2. Click button
3. Open Excel showing Graph

As for your example, why not base the Combo Boxes off the UNION query?
 
Why would I base the combo box off the query? The combo boxes are there for the user to select specific parameters, so wouldn't it make more sense to base the query off the combobox?
 
Hmm, I said that back woards didn't I... well you got it... sorry for the confusion!
 
Heh no worries :). Oh and yesterday before I left the office I managed to get my graphs to work correctly!!!

Now to figure out ODBC and get that all setup ^.^ Once I get that all figured out I can start having it gather correct parameters rather than the test parameter ive been using and work on the aesthetics a bit :D Thanks again for all your help
 

Attachments

  • Capture.JPG
    Capture.JPG
    81.3 KB · Views: 109
  • Capture1.jpg
    Capture1.jpg
    103.2 KB · Views: 106
You wouldn't by chance be familiar with ODBC and setting it up to access an online database would you? Found a video on Youtube thats going over it but it looks like its more focused on setting up a path thats in a folder instead.
 
Actually dont sweat it. I think I can manage it. If I cant after a few hours I'll post again but I should be able to figure it out.
 
Quick question, is it bad practice to have my primary key be something like "date"? I am thinking on doing this because I feel like it would make things simple for updating table which will *in theory* happen everyday automatically.
 
Found the answer to that question quickly hah. Tried it and I immediately got a big NOPE from excel ^.^

******************
edit
*************
Just opened the table it created, and all seems well to be honest. Maybe I can have the date as an index?
 
Last edited:
Simple answer... Yes. You can set a DateUpdated field to Date to *make things easier* but do not actually make it a PK.
 
You're making me crazy posting replies before I even get here to post and answer :eek:

Yes, you can Index that date field and that should work fine.
 
Hah sorry about that :p. I thought you said not to index the date field though?

oops I see now you said dont make date the PK. :)
 
Ok, so when I was trying to import another table into my existing one it wasn't organizing them correctly because of the primary key.

I think I found a work around for this maybe you can let me know what you think of it.

I removed the Primary Index # all together and have it indexed based on the date. I can then just change the order of the date column from oldest->newest. This should work correct? Also are there any ramifications from doing things this way?
 
Nope, I said don't make it a PK. You can Index without making it a PK.
 
Hey just a quick question again. It is probably due to having about 1.5mil rows so far but there is a noticeable delay when I select parameters on the form whereas there was 0 delay previously having around 300k rows. Could this be due to the removal of the Primary Key?
 
Hmm, that's alot of rows to return. That would be the more likely reason then the missing PK.
 
You must log in or register to reply here.

Similar threads

C
Automated Production Planner
Replies
11
Views
1,532
GPGeorge
GPGeorge
amorosik
Access to the web - DaDaBik - have you ever used it?
Replies
0
Views
462
amorosik
amorosik
R
To Merge or Not To Merge... Backends
Replies
4
Views
950
The_Doc_Man
The_Doc_Man
Bobcat42
Solved Problem filling a textbox (format RichText) with Richtext
Replies
3
Views
749
Bobcat42
Bobcat42
T
Back End Read-Only Issue
2
Replies
26
Views
2,113
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom