John, There are only 2 Fields of data. Below is a copy of how i did this in
excel.
Maybe this will be a little easier to understand this way. I would like to
pefrom this in access since i have about 30000 rows in excel to do this to,
and it is really slow.
The starting data is in column B and the result is in column a. In access to
achive this, then the numbered rows in excel, would be the primary ID number
assigned when inported.
The replacement values would be in excel, is done in column b.
1 A B
2 RESULT STARTING DATA
3 EXCEL FORMULA IN A
4 [P2005JURAT1 [P2005JURAT1 =IF(B4="D",A3,B4)
5 [P2005JURAT1 D =IF(B5="D",A4,B5)
6 [P2005JURAT2 [P2005JURAT2 =IF(B6="D",A5,B6)
7 [P2005JURAT2 D =IF(B7="D",A6,B7)
8 [P2005JURAT3 [P2005JURAT3 =IF(B8="D",A7,B8)
9 [P2005JURAT3 D =IF(B9="D",A8,B9)
10 [P2005JURAT3 D =IF(B10="D",A9,B10)
11 [P2005JURAT3 D =IF(B11="D",A10,B11)
12 [P2005JURAT3 D =IF(B12="D",A11,B12)
13 [P2005JURAT3 D =IF(B13="D",A12,B13)
14 [P2005JURAT3 D =IF(B14="D",A13,B14)
15 [P2005JURAT4 [P2005JURAT4 =IF(B15="D",A14,B15)
16 [P2005JURAT4 D =IF(B16="D",A15,B16)
:
You say you have three fields, but you only name ID and FEILD1 (which
looks like a typing mistake).
Please tell me the actual names and data types of the fields in your
table, and confirm that the name of the table is TEST.
Also please say which is the field from which you want to remove periods
.. and opening square brackets [
Finally please give some realistic sample data (three or four records)
so I can be sure I understand what's needed.
On Wed, 15 Mar 2006 08:13:03 -0800, dcozzi
John,
I’m having a little trouble with this. The "valuefield" in your example
below is the result of the same query but 1 line above. I’m not sure how to
write this all in 1 query.
My 3 fields are [ID] (which has the sequence of numbers), [FEILD1](which has
the value we are testing, which are both in the table named [TEST].
The field is based off the query I am running. So it would be something like
[expr1] in [QUERY1]. How would I name the field in which the result of the
query is shown, so that I can reference the line above it. The function I
used in excel which is I guess what the query would be is IF(A2="D",A1,B2). I
do not know how to reference the result of the same query but 1 line above
it? That was my issue in excel until I figured out how to use the result from
the line above.
Since I am still trying to master my syntax, if possible, please write the
exact way the function should be written.
Once again, I greatly appreciate your help.
:
I think so: try it and then compare the order of records in the table,
sorted on the autonumber primary key, with that in the text file.
If not, you can add line numbers to a text file with the 'nl' utility,
part of the textutils package downloadable from
http://unxutils.sourceforge.net
On Tue, 14 Mar 2006 13:58:27 -0800, dcozzi
When i import from the text file, is there a way to add sequetial numbering?
Would this be as simple as letting access assign a primary key?
:
If you have field containing a sequential line number, it's possible to
sort the records on that field and therefore (at least in principle) to
do what you want with two or three queries (or maybe with one more
complicated query).
Assuming you're using a recent version of Access, you can use the
Replace() function in a query. So if the field in question is called
XXX, updating XXX to this
Replace([XXX], ".", "")
will remove the periods, and this
Replace(Replace([XXX], ".", ""), "[", "")
will remove the "["s.
Having done that, you can then use a second query to do the calculation
you need (this could be either an update query, if you want to modify
the records permanently, or a select query, if there's a possibility
that the values that feed into the calculation may change.
Either way you can refer back to the previous record using the line
numbers, with the DLookup() function. For example, if
the line number field is called LineNum you
As I understand your example, you want to check whether one field in the
current record (let's call it TestField) has the value "D".
If TestField = "D", you want to return the value from a field (let's
call it ValueField) in the previous record, otherwise you want the value
from that field in the current record.
If I've got that right, you need something like this expression (replace
the field and table names with your actual ones):
IIF([TestField]="D", DLookup("ValueField", "MyTable", "LineNum=" &
[LineNum] -1), [ValueField])
The term
"LineNum=" & [LineNum] - 1
tells DLookup to find the record where the value of LineNum is one less
than the value of LineNum in the current record.
You can use an expression like this either in an update query, or in a
calculated field in a select query.
The best way to proceed is to make a copy of your database and
experiment with that.
On Fri, 10 Mar 2006 13:12:26 -0800, dcozzi
Is there anywhere I can get an example of that code and how to use it?
This may be completely irrelevant, cause im new at this, but what if I added
a row number for each line and then use that as a reference?
:
It's only possible to do this sort of thing reliably with a query if
there is a field or combination of fields in the data that can be used
to sort the records into the order you need. Otherwise, you can't be
certain that the query will find the correct "cell above" (i.e. the
"previous" record.
This doesn't appear to be the case with your data. So the "Access"
approach might be to write VBA code that reads a line from the text
file, stores the value you're interested in for comparison next time,
uses the previously stored value to process the current line, and then
loops back to read the next line.
On Fri, 10 Mar 2006 08:13:27 -0800, dcozzi
After reading some other posts, I want to correct my question.
Can the result of the excel function described below be achieved thru a
query in Access?
:
Is there any chance you can help me with the second part regarding the
formula I only know how to run in excel? It is in step 2 below and it is the
first calc that needs to be done in order to create the first part of the
identifier. Just to clarify, the text file I currently receive only has a
section identifier which is in the same column as the "D"s. I need to use the
formula to say if the cell to the right is not a "D", then it equals that
cell, if it is a "D", use the result of the cell above. Is this possible in
access?
2. I perform the following calculations create the identifier.
a. I need to take out the following characters from column C via a find all
and replace all
Find All ], replace all with nothing
Find all ., replace with nothing.
B. In A2, I run following function (via a macro all the way down to line
60,000)
=IF(A2="D",A1,B2)
D in the above expression is a specific character
An example of the data manipulating I achieve in step 2 is below. B and C
are what I paste in. D is my unique identifier.
IDENTIFER = (CONCENTRATE A2,C2)
ROW COLUMN A COLUMN B COLUMN C IDENTIFIER
1 [P2004JURAT1 [P2004JURAT1 1 [P2004JURAT11
2 [P2004JURAT1 D 2
3 [P2004JURAT2 [P2004JURAT2 1
4 [P2004JURAT2 D 2
5 [P2004JURAT3 [P2004JURAT3 1
6 [P2004JURAT3 D 2
7 [P2004JURAT3 D 3
8 [P2004JURAT3 D 4
9 [P2004JURAT3 D 5
10 [P2004JURAT3 D 6
11 [P2004JURAT3 D 7
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
