vlookup help?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I have an interesting report question....

I have a report that exports transactions per USERID, account value of the
USER, and some other stuff about the USER. But the question revolves around
three fields.

One field is called "Adjusted Cost basis", it is a small, calculated number.
I have another field called "Post Tefra" (this number is in the table I'm
pulling from), and a third field called "After Trans Post Tefra" (calculated
field). This is how I want it to populate:

For the first time a transaction shows up, I want it to show the "Post
Tefra" field that is found in the table. And then I want the "After Trans
Post Tefra" field to be the result of the sum of the "Post Tefra" and the
"Adjusted Cost Basis". This is fine and I have that working.

The NEXT time the same USERID shows up (another transaction at a later
date), I want the "Post Tefra" field to show what was in the "After Trans
Post Tefra" of the previous record. And then I will calculate the other two
fields the same way as before. Would this be a Vlookup? I have never used one
of those before, let alone in a report.

Any help would be appreciated!! Thanks!!
 
I have a report that exports transactions per USERID, account value of the
USER, and some other stuff about the USER. But the question revolves around
three fields.
Click to expand...

If I understand, you are going to be grouping on USERID -- USERID will be
the group header. And, within that, transactions -- you don't say, but the
implication of transactions is that they will be ordered by date/time in
ascending order.

I'm not sure I understand when talk about "the table" or "the table I am
pulling from" -- a Report has a RecordSource which can be an SQL statement
or a stored Query (either of which could include multiple tables) or a
table.

In most transaction-processing systems, the transactions would be recorded
in one table, and each transaction would refer to a customer/client/USER. In
most transaction processing systems that I have worked on, each transaction
is an entity... and would have one record (although if that transaction were
a sale, there might be a related table containing "order detail" or "detail
lines" about the sale). I am understanding you to say that there will be
multiple records per transaction.

I am getting an overall impression that you want to do "sequential file
processing" and that is not the strong point of relational database systems.

If you could clarify some of these things: what data you have, how it is
stored in tables, and specifically how you want it presented, it would be
more likely that someone could give you better suggestions. I could _guess_
at some of these issues, but if I guess wrong, and formulated a suggestion
on that basis, it would be a waste of your time and mine.

Larry Linson
Microsoft Access MVP
 
OK, I will give as much detail as possible...

I started with a table full of transactional data. I have a table of
tranactions on contracts, with a userID associated with each contract. I
wrote a query for the data, which really doesn't do much, other than format
some of the data. Here is the SQL for the record source the report is running
off of:

SELECT [Data with Import Date].[Import Date], [Data with Import Date].[Owner
SSN], [Data with Import Date].[Contract Number], [Data with Import
Date].[Transaction Type], [Data with Import Date].[Last Name], [Data with
Import Date].[First Name], [Data with Import Date].[Transaction Amount],
[Data with Import Date].Gain, [Data with Import Date].[Pre Tefra], [Data with
Import Date].[Post Tefra], [Data with Import Date].[Federal Tax], [Data with
Import Date].[State Tax], [Data with Import Date].[Account Value],
IIf([Transaction Date]="00/00/0000","NO TRANSACTION",CDate([Transaction
Date])) AS [Trans Date], [Data with Import Date].Date1 AS [Eff Date], [Data
with Import Date].Date2 AS DOB, [Total Account Value Per Trans
Date].[SumOfAccount Value]
FROM [Data with Import Date] INNER JOIN [Total Account Value Per Trans Date]
ON ([Data with Import Date].[Owner SSN] = [Total Account Value Per Trans
Date].[Owner SSN]) AND ([Data with Import Date].[Import Date] = [Total
Account Value Per Trans Date].[Import Date]);


In my report, I am grouping by transaction date first, then by USERID.
Basically the point of my report is that I need to see all of the contracts
of each user when they make a transaction, even if they only make a
transaction one 1 of 5 contracts they have. So an example of how my report
looks now is as follows:

Transaction Date: 1/3/2005
OwnerID: AAABBB

Trans Date Contract Trans Amt PT After Trans PT Adj
Cost Basis
1/3/2005 1 $20 $18 $17
-$1
No Transaction 2 $0 $18 $18
$0

OwnerID: CCCDDD

Trans Date Contract Trans Amt PT After Trans PT Adj
Cost Basis
1/3/2005 1 $10 $8 $7
-$1
No Transaction 2 $0 $18 $18
$0

etc down the line....till yesterday's date.

The two calculated fields are "After Trans PT" and "Adj Cost Basis". They
are calculated right in the report itself. They are working fine. What I NEED
to do, is say for OwnerID= 'AAABBB' in the above example, if he has a
transaction in February, I want $17 do show up for PT (Because it was in his
After Trans PT box for 1/3/2005) INSTEAD of whatever number is in the
recordsource for PT for that February transaction. (If I look at the data
there will be a number in PT for February for that person)

Does that make sense? Thanks!
 
Back
Top