Yes, you are right about the result set. I am an ex-Paradox user having been
dragged kicking and screaming into Access. Paradox produces an answer table in
response to a query which acts in every respect like a real table. That means
you can structure a query that adds a new field to the answer table and
populates the new field with calculations based on the results of the query.
You can even do it in a two pass scenario that allows you to do calculations
based on sums of selected previous records. In Access that is a DSUM
requirement if you have lots of time on your hands.
I don't care if it is a make table or a select query, whatever it takes to
make it work. I think the answer is that I'm going to have to do it in code. I
don't think SQL will help here. Incidently I can do (some) SQL but I avoid it
like the plague that it is. If I can't represent the problem on the QBE, then
I'm probably not interested enough to solve it. That brings up another
question: In Paradox if I enter a QBE with criteria in different fields the
result will be the records which meet all of those criteria. If I enter the
criteria in different fields and different rows the result will be each record
which meets any of those criteria independently. In Access such a query will
fail. Am I missing something here?
As I said, I think the answer is that I'm going to have to do this in code.
For this reason what I'm really looking for is a simple primer for how to
figure out how to reference all of the various Access objects. For this
exercise, it seems like "Tablename".CreateField("Fieldname",Type,Size) ought
to do it. I know, I know, I'm not supposed to hardcode the Tablename,blah blah
blah.
Anyway, I finally figured out that even though a query result is displayed as
a table, it is not really (is this explained somewhere?) so I used a MakeTable
query. But in Access you cannot run a maketable query that automatically
creates a blank field, you have to make the table and then add the field,
which is how I got to the CreateField problem.
From what I can see in the examples, there are lots of references on how to
manipulate recordsets, but nothing on manipulating tables. And the recordset
references often appear to refer to recordsets that are represented on forms
as part of an Access Application. I know I'm suffering from "having used an
'other' product syndrome", but reality being what it is I'm willing to be open
minded.
My real problem looks like this
EventType1 Time1
EventType1 Time2
EventType2 Time3
EventType3 Time4
EventType1 Time5
EventType3 Time6
EventType2 Time7
EventType3 Time8
Question: For Each type3 event, how much time has elapsed since the earliest
non-Type3 event since the previous Type3 event?
Answer: Time8-Time7;Time6-Time5;Time4-Time1
Every time this question comes up, I've got about 120,000 records.
I see this is a two or three-pass problem:
First pass: set a flag on the first record and every record that follows an
EventType3
Second pass: (optional) Delete everything that is not type three or have a
flag.
Third Pass: Calculate the time difference between every EventType3 and the
next previous record with a flag.
In Paradox, this is easy: on the first pass you have access to the Recno field
so you can set a flag on the next record. On the second or third pass you have
access to a real IF statement, not just an IIF. In Access, I have no clue.
Thanks for your help.
Quote:
> Hi Ray,
> "I would like to run a query, add a new field to the resulting table and
> then populate the new field by performing some calculations."
> I think there may be some confusion here. If you are refering to
> a Select query, there is no resulting table, only a result set. Access
> diaplays the results in a table like manner, but this is not a 'real' table.
> If however, you are running a 'Make table query', then ignore
> the above advice.
> If you are running a Select query, it sounds as though you want
> to produce a calculated field, which would be done within your
> SQL statement.
> Let us know.
> HTH
> Dan Artuso, MVP
> > I've used VB with Excel and MS Project etc, but when I come to Access
> > I'm flummoxed. Everything I try to do seems to yield an error and i
> > can't get far enough into it to determine where they start.
> > I can't seem to understand how all the various objects are referenced.
> > So far I can get dbs to refer to the currentDB and I can get MsgBox to
> > report the name of the current DB. There doesn't seem to be any similar
> > way to get the current table or the current field or the current
> > record.
> > Even if I copy the example code I get errors. Is there a simple minded
> > reference that tells how to address these objects and set their
> > properties?
> > I'm trying to create a new field and append it to an existing tabledef.
> > I would like to run a query, add a new field to the resulting table and
> > then populate the new field by performing some calculations. The
> > createfield method example results in somthing that looks like :
> > With tbf
> > .Fields.Append.CreateField("Supptime", dbInteger)
> > End With
> > This results in syntax errors or an error stating its "expected ="
> > Is a recordset object only the result of a query? If so how do you
> > reference a table directly?
> > ???