Hi,
If you need it in Access the a crosstab query is the way to go.
But here is some food for thought.
I had the same problem. Access Graphing is awful!!!!! and overall Excel is more flexable.
The way I sloved this was to link the my query data into excel.
You might want to try this.
In Excel
1. Go to the Data Menu / Import External Data / Import data.
...
Boxcar74
Last Activity: Jun 3 '10, 07:43 PM
Joined: May 7 '07
Location: RI, USA
-
Excel "Open_Workbook" Macro-Refresh Data and Save
Hi Everybody!!!
I have an Issue.
I have an Excel file that queries an Access db. I’m trying to have it so I don’t have to keep updating it manually everyday and save it to a network drive with the file name coming from a cell reference and the current date.
What I’ve done have the Excel file open via a Batch file and on “Workbook_Open” I run a few macro to refresh the data then remake and save... -
Ok, I think i miss understood.
So the second field is dependant on the first fields selection.
I belive the best way to handle this is in a form. But I'm not sure how.
I know there is a way. I'm just not sure if you need to add vb code to the form or if there is a build in function.
But I'm sure there is a way to do it.
Update me if you made any progress.
I will...Leave a comment:
-
Easy to do two ways.
1. In the RowSourceType setting
Select Table/Query to reference a column in a table or query.
I recommend this one for you
2. In the RowSourceType setting
Select Value List and you can type in the names of the options you want for your list in the Row Source Field and use semicolons (;) as separators.
Like: A;B;C;D
-------------------------------------------------------------------------------------...Leave a comment:
-
One way to do this is to query from Excel.
I posted it here.
http://www.thescripts.com/forum/thread686051.html
It's different but it work for me!!
-- Boxcar...Leave a comment:
-
I forgot you are using VBA to run the queries automatically.
So I doubt my way is how you want to go. You may want to consider it.
Sorry :(
-- BoxLeave a comment:
-
I don't see how you can do it with out VBA code.
The post above I do not belive will work. I could be wrong. I hope it is right and it will solve all you problems.
I think you can only export one table or query at a time and you will still end up with Multiple workbooks.
------
I don't know if this is the route you want to go, but it may help.
I run the SQL from Excel and update muiltple...Leave a comment:
-
I think you tables are fine.
I would run a crosstab query off the table with
consumer name / date / meter reading
Having "Consumer name" as the Row Heading, "Date" as the Column heading and "meter reading" as the value (use sum), it will just give you the Customer total.
Then run a query off of that to find the monthly differance.
IT should be something like:...Leave a comment:
-
It seem too simple of an answer but:
If you can have a query for each region.
You can create a simple macro in the macro section no VBA needed.
Have the Macro action be "TransferSpread sheet" set it to export but the query name in the table field, punt in the path of where you want it to be exported to.
I'm not sure if this is what because you would have to do this for each region...Leave a comment:
-
The 12 Monthly Columns are automatically created in the Crosstab Query Wizard. They list as JAN, FEB, MAR, etc......
With the formats yyyymm you can use "yyyy/mm" or "mm/yyyyy"
How ever you want it formated.
The Crosstab automatically does it as "mmm" Ex: Jan, Feb, etc... you can change the format however you want i.e yyymm in the SQL text but is kind of a pain.
...Leave a comment:
-
Also since your DB isn't extremely complicted you might want to look at templates for examples.
Microsoft has some at:
http://office.microsoft.com/en-us/te...3&CTT=6&av=ZAC
But there are plenty out there.
I'm sure they done have one to fit your needs but you probably find one to serve as a guide.
Especially for the reports/invoices,...Leave a comment:
-
Not 100% sure what you are looking for
Do you want the results like this:
Example: Columns Headings
Customer, JAN, FEB , MAR, etc.....
With the monthly totals on the grid for each month.
Like :
Joe S , 100202, 102020, 192837, etc...
If so, a simple Crosstab Query will do this.
The Wizard will walk you though it.
But since you are new I'll ad some info....Leave a comment:
-
Hello,
I run a ton of macro via the Windows Scheduler. I created a batch file that triggers (/x) the macro and set the scheduler to run it whenever I want.
Here is a basic Article from Microsoft
http://support.microsoft.com/kb/230575/
This is an example of the batch file I use: w/ Office 2003, my file name Myissue.mdb and my macro “Import Daily csv”
--------------------------------------------------...Leave a comment:
-
I tired one like that before. With no luck.
I tried again with your format still no luck. When I get more time I will play with it more.
In therory it should work.
Thanks Again !!
-- BoxcarLeave a comment:
-
Hello
First if you have 186 fields you should rethink your table.
I would suggest trying the Analyze Table and/or Analyze Performance features in Access and see what they say.
With that said:
Access allows the maximum Number of fields in a table to be 255.
So you should be all set. But you not :-(
In my experiences I’ve found if I have a lot of data, in my case 400,000 rows if I try...Leave a comment:
-
YES I just edited the post above!Leave a comment:
-
Yes and Then some from 1/1/2007 to 12/31/2015 with everyday in between, accounting for leap year and all.
I know the db will be long gone or in a different format by then but I just fill the table when I created it. :-)
I found in the if in the first query I put: HAVING Between #1/1/2007# and Date()-1
I can use the Second (Crosstab) query to adjust the date parameters i.e. Between Date()-13 and Date()-1...Leave a comment:
-
One Last note:
Other options that were suggested to me:
1. Combining the two queries, where the Union query will function as a Crosstab query.
2. Use a VBA module: How to Count Values in Record or Recordset Across Fields
http://support.microsoft.com/kb/q142227/
I never got either of these to work, but give it a try.
As always thanks to all the Scripts folks that helped.
...Leave a comment:
-
OK, the answer I got is using two queries.
A UNION Subquery then a Crosstab Query !!!
So to review for this example: I’m running a crosstab query with dates as my columns heading, but if nothing happens (no record) on that day I end up with no column for that day. (see previous post for examples)
Here my setup. I tried to simplified it.
One table with all my data called DAILY and another with...Leave a comment:
-
That didn't work either. I appreciate the effort though.
I set up a small test DB and even renamed the tables and field to match the query.
One thing that could have thrown you off is I posted the first SQL line incorrectly.
It should have been:
TRANSFORM Count(Daily.[ID]) AS COUNT
So this is how I tried it:
TRANSFORM IIf(Count([ID]) Is Null,0,Count([ID])) AS Expr1
SELECT...Leave a comment:
No activity results to display
Leave a comment: