HELP! Dlookup 2

[accountinggeek]

I still can't figure out the problem. And MS tech support doesn't seem to have the answer either. Anyway, I modified my table to make it completely flat, so no other tables are involved. So, now I have the following table:

ID-------Value------UniqueVal-----Cost--Quantity--MarketVal
1--------Cost-------Cost1---------100----1000-----2000
1--------Quantity---Quantity1-----100----1000-----2000
1--------MarketVal--MarketVal1----100----1000-----2000
2--------Cost-------Cost2---------200----1750-----2200
2--------Quantity---Quantity2-----200----1750-----2200
2--------MarketVal--MarketVal2----200----1750-----2200

I created a unique field (UniqueVal) via concatenation of ID&Value.I then perform dlookup(Value,"table Name",UniqueVal)

I also have tried: dlookup(Value,"table Name",ID&Val = UniqueVal)

In either case, my query returns(ReturnVal is the val from dlookup):

ID-------ReturnVal-UniqueVal-----Cost--Quantity--MarketVal
1--------100--------Cost1---------100----1000-----2000
1--------1000-------Quantity1-----100----1000-----2000
1--------2000-------MarketVal1----100----1000-----2000
2--------100--------Cost2---------200----1750-----2200
2--------1000-------Quantity2-----200----1750-----2200
2--------2000-------MarketVal2----200----1750-----2200

It repeats this for all records. What I am expecting is:

ID-------ReturnVal-UniqueVal-----Cost--Quantity--MarketVal
1--------100--------Cost1---------100----1000-----2000
1--------1000-------Quantity1-----100----1000-----2000
1--------2000-------MarketVal1----100----1000-----2000
2--------200--------Cost2---------200----1750-----2200
2--------1750-------Quantity2-----200----1750-----2200
2--------2200-------MarketVal2----200----1750-----2200

HELP!

 

[jrbarnett]

Can you please post the following information:

1. The field (s) that make up the primary key of this table.
2. The SQL code that makes up your query (go to View -> SQL in Query Design view).
3. The data type of each field from the table design.

John

 

[accountinggeek]

1) unique_val is the primary key;
2)Here goes(note that I was using an example- I query many fields)

SELECT henrybak.Value, henrybak.unique_val, [Value] & [ID] AS Expr1, DLookUp([henrybak].[Value],"henrybak",'unique_val') AS [Money], henrybak.TRANID_TRN, henrybak.ID, henrybak.ACCRBAS, henrybak.ACCRLCL, henrybak.BVBAS, henrybak.BVLCL, henrybak.TRNBVBAS, henrybak.TRNBVLCL, henrybak.CALCACCLCL, henrybak.CPTLAWBAS, henrybak.CPTLAWLCL, henrybak.CASHDECRB, henrybak.CASHDECRL, henrybak.CASHINCRB, henrybak.CASHINCRL, henrybak.RGLCCYCLS, henrybak.RGLCCYTS, henrybak.RGLCCY, henrybak.CONTRAAMT, henrybak.RGLCCYCSH, henrybak.CUSIP_SMSEQ, henrybak.CONTRACCY, henrybak.GRACCRBAS, henrybak.GRACCRLCL, henrybak.STLFXRATE, henrybak.FXRATE, henrybak.LCLCCY, henrybak.LS, henrybak.RGLMKTBAS, henrybak.RGLMKTLCL, henrybak.GROSSBAS, henrybak.GROSSLCL, henrybak.QTY, henrybak.QTYADD, henrybak.QTYRMV, henrybak.RGLBVBAS, henrybak.RGL, henrybak.RELSCTY, henrybak.SCTYID_SMSEQ, henrybak.SCTYNM, henrybak.Field39, henrybak.SCTYTYPE, henrybak.STLCSTLCL, henrybak.PAYCCY, henrybak.STLDATE, henrybak.STLBAS, henrybak.STLCSTBAS, henrybak.RCLMTAX, henrybak.RCLMTAXBAS, henrybak.WITHTAX, henrybak.WITHTAXBAS, henrybak.TRDDATE, henrybak.TRANCOD, henrybak.TRANNO, henrybak.[Asset Kind], henrybak.TRANTYP, henrybak.SUPUNSUP, henrybak.TranKind, henrybak.TRANID_TRN, henrybak.TRANID_TRN, henrybak.TRANID_TRN
FROM henrybak
ORDER BY henrybak.TRANID_TRN;

3) Value, unique val, and ID are text types; the rest are numeric(I've tries swithing all to text and still didn't work)

THANKS!!!!!

 

[jrbarnett]

The problem is that your DLookup field is not specifying the criteria. Just putting the criteria as "unique_val" means that DLookup will retrieve the first value field from the henrybak table.

The use of DLookup within a query will significantly slow down the query's execution, as it means that the VBA code has to be executed once per row within the table.

If you change it to use a subquery instead:

(select value from henrybak h where henrybak.unique_val =h.unique_val) as ReturnValue

it should work fine as well as not slow down too much if you have large quantities of data as it would if you used VBA code.

John

 

[accountinggeek]

John;

Thanks so much! I will check it out and let you know how it goes.

You might have saved what was looking like a bad weekend!

Henry

 

[accountinggeek]

Hi John;

Adding that subquery simply returns the value column name, not the corresponding value in the column that I am trying to fetch. For example- value = apples; i am looking for the number in the record that corresponds to the column. With the sub query, the column is returning 'apples', not the number.

Thanks

henry'

 

[PHV]

And this ?[tt]
DLookUp([Value],"henrybak","unique_val='" & [Value] & [ID] & "'") AS [Money], [/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[accountinggeek]

PHV

You did it!!!! Thank you so much- worked like a charm!!!!

BTW- MS is like a gang that can't shoot straight. You just wouldn't beleive what I've been through.

THANKS THANKS THANKS