Can anybody help me with some Access 2000 code?
I don't do a lot of coding in Access & so every time I come back to do
something I pretty much have to relearn the syntax from scratch so this is
taking me hours. As with most occasional programmers its the commas, colons,
inverted commas, square brackets etc etc that cause the problem! (Not to
mention the nuances of using Do While, Do until, Case statements If
statements, IIF etc etc...) .I think I have the logic of the thing sorted,
its the syntax that's holding me up.
I'm trying to do something fairly simple to update a table with several
hundred thousand records in it (its a phone bill). I do want to update the
table rather than calculate on the fly because once I have the data I need
then it won't change & there is a whole range of different tasks to carry
out afterwards using queries etc to actually break the thing down and get
some meaningful results from it (which can vary from one bill to the next).
Since there may be several hundred thousand records in the table calculating
this on the fly everytime I want to use the data would take far too long I
think so better to run this once & be done with it.
So here's the problem....
I have a table with records of individual calls - for each one I have a
start time & date and a duration in seconds. In order to rate (re-price) the
call properly I have to ascertain which if three chargebands the call falls
into - and it may cross chargebands in which case part of the call will be
charged at one price and part at another. All I am trying to do is to break
down the duration into the different chargebands. Once I have this then I
can re-price each call & summarise the bill using & I have that part
covered (using a rates table and queries).
So in my table I have three fields prepared (one for each chargeband) into
which I need to divide up the total duration of the call which I already
have in seconds (just stored as a number)
So what I thought I'd do would be
Open the table as a recordset
Declare some variables as follows:
Band1, Band2 & Band3 (to contain the number of seconds from a given call
that fall into each charge band)
sttime to contain the start time of the call
durn to contain the call duration in seconds
Go to the first record (call) in the table
Pull the call start time of the call from a field (which is a properly
formatted general date field including the time and the date) from the
record and put into "sttime"
Pull the total duration from a field in the table and put it into "durn"
Test which chargeband the "sttime" falls into using some case statements to
determine day of week and time of day etc
Add 1 to one of three Band variables based in the result
Deduct 1 from "durn"
Add 1 second to "sttime"
then loop round & keep testing till "durn" gets to zero.
Once "Durn" gets to zero then store/save the results of Band1, Band2 & Band3
back into the table in the prepared fields that are there
Move to the next record, zero all the variables, pull the next record's
start time and duration and repeat all this till it gets to the end of the
file.
At this point for each record in the table I should have the total duration
(which I had to start with) and three fields (Band 1,2 & 3) which contain
numbers of seconds which, if added together, will equal the number in the
Total duration field. For many records two of the three will be zero & the
third will contain the same number as the Total duration field (i.e. where a
call just falls into one chargeband). For some records (for calls that cross
from one band to another) I might have figures in two of the three fields &
for a few (for very long calls) I might have numbers in all three.
If I trigger this from a button on a form it'd be nice to put a counter on
the form that would show the thing working by counting up 1 for each record
it processes. I don't need to display the table as its processing & nor do I
need to see the results in a form - I just want to update the table as
quickly as possible.
thanks in advance for any help with the syntax required.
Iain
