Use VBA To Automatically Insert Fields

[danz013]

Hi Guys,

I'm very new to Visual Basic so you'll have to bare with me. I've got some great big books that I've brought but I'm not even sure where to start. I did a bit of visual basic programming back when I was in college however I need my mind to be refreshed a little, I can't remember a thing!

Anyway... here's the problem:

I run a business and I'm making somewhat of a sales system.

What I'm Trying To Do
I need a table that is going to group the revenue, sales, marketing costs, expenses and so on for each week and plot them on a graph. These amounts are dragged into the table by queries that search other tables holding the relevent info.

What's currently happening (aka the problem)
At the moment, I'm able to grab the data in for each week. The problem is, if there is no data for a particular week (for example, I have data for week 36 and 38, but nothing for week 37), then week 37 won't show up on my graph.

What I Want To Do (this is where I need you help)
I need a way to do the following and I believe it comes through visual basic (but im not sure):

1. Look in the table (the table that will have week 37 missing) and return the 1st and last value for the week number.
2. Delete the records in the table
3. Get something like visual basic to automatically add in record with the week numbers for all the weeks between the largest and lowest value
4. Get an update query to fill in all the other values.

How would I do this?
There is one more problem I have. Week Numbers. When next year comes, this idea of using week numbers is going to create a whole lot of problems, I may have duplicate week numbers, or January 2009 may show up as the start of the graph, when it should show up at the end. How will I get around these issue?

You help will be very very very very much appreciated. I hope I don't trouble you minds too much.

 

[nIGHTmAYOR]

would u help by stating field names and how you store your data types ? (strings / integers / dates)

 

[tranchemontaigne]

Not knowing exactly what you want it's hard to give sound advice....


But here goes...you could always
(1) use a report extract table with a variable for each week
(2) the default value for each variable could be zero, or a function could be written to change nulls to zeros
(3) use some recordset operations to populate the values in each column of the extract table.

This should allow you to bind the extract table to a chart and make a line graph or bar chart that shows zeros for future weeks.

If your end users like MS Excel output, you should explore http://www.zmey.1977.ru/excel_and_autofilter.htm
http://www.zmey.1977.ru/access_to_excel.htm
http://www.access-programmers.co.uk/...348#post742348


<COMMENT>
I've always liked using report extract tables.

One benefit of this kind of approach is that should you ever need to migrate your application back end to another platform or to a mixed platform (e.g. MS Access with Oracle), your work will (arguably) be a little easier to follow.

Once the backend is entirely absorbed by another database (or at least the extract tables) the time for building new front ends with APEX etc will be dramatically reduced because all of the work you have done to QA the extract tables will not have to be repeated. All you would really need to QA is the report display and the binding between the report and the extract table.

One of the earliest systems I worked with had extract tables with fields named after the data type they stored. This allowed the extract table to be used in a multiuser environment where fields could be "overloaded" (i.e. a single field with a data datatype field would store a report date for one report, a transaction date for a different report, an order date for yet another report etc. You can recycle this kind of table indefinitely, so long as fields are used consistently within any given report. The only downside is that it can be a little harder to wrap your head around how someone else has set things up should you need to support someone else's poorly documented system. The upside is that you will not have to build N different fields (one for each field that could appear in reports) or require maintenance to add fields to the extract table every time the use and purpose of the application grows.
</COMMENT>
________
Babimac

 

[nIGHTmAYOR]

tranchemontaigne i understand him fully , yet he has a vision and he wants to achieve HIS vision through someone whilest i have another vision for him (including creating a virtual dates table using sql then using a join from that table to his) which is less reliant on vba yet more practical than manipulating his data just to fill chart gaps.

 

[tranchemontaigne]

nIGHTmAYOR
Thanks for the post. I'm guessing that Danz013 is constructing a make table query out based upon a crosstab query.

I agree with you.

Either using recordsets with values initialized to zero, or a join to a virtual dates table should work for Danz013.

I've used both methods before, but prefer using a recordset method works because it is easier to construct a hash unique to each report request, and pass that hash through stored procedures all the way to the generated report and purging the related records from the temporary/extract table. This works better when coding for a multi-user environment.

Without a unique report hash, resolving the issue with a join to virtual date table alone is faster, but more prone to unexpected results. For example if two people make almost simultaneous calls to the same report but pass different parameters the output might not be right for both of them. Alternately different reports could be called almost simultaneously, but if they are based upon dynamically populated contents where different make table queries producing a results table with the same name, you can expect that the second maketable query will overwrite the results expected by the first report requester.

<comment>
Apologies if this is hard to read.
</comment>
________
CBR250

 

[nIGHTmAYOR]

hmm , i usualy think of a setting of a front end/back end scenario (where what happens on the back end stays on the back end ) never a shared environment. your solution might be more appropriate since i am not sure of his setting nore level in codeing.