Help with Dlookup needed

[Bokazoit]

I have this function:

=DLookUp("[LYFY]";"DVH_krydstabel";"[Market] = DAGLIGVAREHANDLEN EX. COOP")

But it gives me an error (#error) in my report

DVH_krydstabel has these coloumns:

Market, SegmentID, DMLY, DMTY, LYFY, YTDLY, YTDTY

The Dlookup function should give me the value of [LYFY] in the tabel 'DVH_krydstabel' where [Market] = 'DAGLIGVAREHANDLEN EX. COOP' and SegmentID equal the current Segment shown in the report.

Tx ;-)

 

[mordja]

Bokazoit,

Use


DLookUp("[LYFY]","DVH_krydstabel","[Market] = ""DAGLIGVAREHANDLEN EX. COOP""")


Instead.

Mordja

 

[Bokazoit]

Now it works but only gives me the value according to one Segment. As You can see I have a coloumn called SegmentID and I would like to retrieve the value fra DVH according to the Segment (There is 10 different segments). What is wrong here

 

[mordja]

Bokazoit,

Im not quite sure what you mean, please include your table structure and some sample data. However if I understand you correctly, firstly the DLookup function only returns one value, secondly the DLookup function above will return the value for [LYFY] for the first instance of [Market] = "DAGLIGVAREHANDLEN EX. COOP" that it finds.

Or are you expecting the DLookup function to link to the current SegmentID on your report. In that case you need to do something like


DLookUp("[LYFY]","DVH_krydstabel","[Market] = ""DAGLIGVAREHANDLEN EX. COOP"" AND [SegmentID] = [YourReportSegmentIDField]")


Where [YourReportSegmentIDField] is the name of the field on your report that contains your segment ID.

Hope this helps/.

Mordja

 

[Bokazoit]

You got the second part right but what is wrong?

=DLookUp("[LYFY]";"DVH_krydstabel";"[Market] = ""DAGLIGVAREHANDLEN EX. COOP"" AND "[SegmentID] = [Segment]")

SegmentID is the coloumn name and [Segment] is the fieldname in the report. In general it should say [SegmentID] = [SegmentID] 'cause it should refer to the same segment in the report as the fieldname Segment refers to

I think it's really close now hope You can guide me

 

[Bokazoit]

Forget to tell that when I preview the report, the above function returns #Name?

 

[mordja]

Bokazoit,

Try


=DLookUp("[LYFY]";"DVH_krydstabel";"[Market] = ""DAGLIGVAREHANDLEN EX. COOP"" AND [SegmentID] = [Segment]")



Instead

Mordja

 

[Bokazoit]

the above function returns #Name?

 

[mordja]

Bokazoit,

Try and test it to see that its working ie with a segment id you know.

For example
try


=DLookUp("[LYFY]";"DVH_krydstabel";"[Market] = ""DAGLIGVAREHANDLEN EX. COOP"" AND [SegmentID] = 12")


Where 12 is an existing SegmentID.
Other wise you may need to reference the [segment] field more directly.


=DLookUp("[LYFY]";"DVH_krydstabel";"[Market] = ""DAGLIGVAREHANDLEN EX. COOP"" AND [SegmentID] =" & [Reports]![yourReportNameHere]![Segment])


Hope this helps.

MOrdja

 

[Bokazoit]

Ok if I use this:

=DLookUp("[LYFY]";"DVH_krydstabel";"[Market] = ""DAGLIGVAREHANDLEN EX. COOP"" AND [SegmentID] = ""Deo""")

Were deo is one of the Segmentnames I get the right data So what am I doing wrong?

 

[mordja]

Bokazoit,

I didnt realise that SegmentID was a string !! I should have asked.

Try


=DLookUp("[LYFY]","DVH_krydstabel","[Market] = ""DAGLIGVAREHANDLEN EX. COOP"" AND [SegmentID] =""" & Reports!test!Segment & """")


Replace test with the name of your report.

Mordja

 

[Bokazoit]

I get nothing just blank fields with this:

=DLookUp("[LYFY]";"DVH_krydstabel";"[Market] = ""DAGLIGVAREHANDLEN EX. COOP"" AND [SegmentID] = ""& Reports!report1!Segment &""")

 

[mordja]

Bokazoit,

Strange works ok with me. Oh, quite important, where in your report are you putting the DLookup ?

Please post a few sample lines from your DVH_krydstabel table so I can see if it could be anything else.

Mordja

 

[Bokazoit]

Market SegmentID DMLY DMTY LYFY YTDLY YTDTY
BILKA & A-Z BODYSHOWER 2081,47 1979,72 22549,76 16591,13 15953,61
BILKA & A-Z DEO 6277,38 5590,18 49547,78 33534,56 34816,99

The above is an example of my DVH_krydstabel

In the report I have made it very simple since I wanted to see if it's possible to do what You are helping me with

So in the detail-field of the report I have:

Market, SegmentID, LYFY, YTDLY, YTDTY, DMLY, DMTY & Dlookup

 

[mordja]

Bokazoit,

What table is the report based on ? from the looks of it it looks like it is based on DVH_krydstabel !!! is this correct, if so why do you need a lookup ? If not can you post a few lines of data from the table that the report is based on.

Mordja

 

[Bokazoit]

You are correct the report is based on DVH_krydstabel. The reason I need the lookup is that I need the Market 'DVH' in a calculation. I'm not just going to do a lookup for that specific value and print it. I need to say:

Market/DVH within each segment. DVH is part of the DVH_krydstabel

So the lines posted above is from that table

 

[mordja]

Bokazoit,

It does not seem logical to me that you are using a dlookup function to lookup the same table that your report is based on. What do you want to happen for instance when the report displays the row with the Market Value of "DAGLIGVAREHANDLEN EX. COOP", the Lookup will then be looking up the table for the exact same data that is already in the row !

As you are using a constant "DAGLIGVAREHANDLEN EX. COOP" throughout maybe you should think about your table design and create a table with just Segment and LYFY as its two fields. Then lookup that up using the variable field Segment as the criteria.

Hope this helps

Mordja

 

[Bokazoit]

Perhaps it doesn't seem logical but fact is that I will use it to find marketshare of the different chain-stores. True, when 'DAGLIGVAREHANDLEN EX. COOP' is shown (Only the first 9 rows out of the 60 it will give me 1. But I need to calculate the marketshare for the 51 remaining lines.

I have already spend much time trying to create a table structure that could handle the data in the first place. I use 'Append queries' to create a table that gives me the structure I need, then I build the Cross_table 'DVH_krydstabel'.

I could - perhaps (I really don't know), make the calculations I need to get marketshares, in a different manner, but I thought it would be much easier this way. Here is 1 out of the 5 'Append queries' I made to create my cross_table:

INSERT INTO [DVH tabel] ( Market, SegmentID, Oms, Period )
SELECT [ACNielsen data].Market, [ACNielsen data].SegmentID, Sum([ACNielsen data].Oms) AS Oms, IIf([Periode]>((DMax("[Periode]","[ACNIelsen data]")-100)-Right(DMax("[Periode]","[ACNIelsen data]"),1)) And [Periode]<=((DMax("[Periode]","[ACNIelsen data]")-88)-Right(DMax("[Periode]","[ACNIelsen data]"),1))=True,"LYFY","-") AS Period
FROM [ACNielsen data]
GROUP BY [ACNielsen data].Market, [ACNielsen data].SegmentID, IIf([Periode]>((DMax("[Periode]","[ACNIelsen data]")-100)-Right(DMax("[Periode]","[ACNIelsen data]"),1)) And [Periode]<=((DMax("[Periode]","[ACNIelsen data]")-88)-Right(DMax("[Periode]","[ACNIelsen data]"),1))=True,"LYFY","-")
HAVING (((IIf([Periode]>((DMax("[Periode]","[ACNIelsen data]")-100)-Right(DMax("[Periode]","[ACNIelsen data]"),1)) And [Periode]<=((DMax("[Periode]","[ACNIelsen data]")-88)-Right(DMax("[Periode]","[ACNIelsen data]"),1))=True,"LYFY","-"))="LYFY"));


Perhaps You have a better idea then? or a way to get the data I need in the dlookup

 

[mordja]

Bokazoit,

Its hard for me to get a clear picture of the above, but as I said before it seems to me because you dont actually use market = DAGLIGVAREHANDLEN EX. COOP to determine your LYFY value (it is really dependent on Segment) then maybe you should have a lookup table with all the LYFY values for DAGLIGVAREHANDLEN EX. COOP by segment. Then you could easily do the lookup as before.

Mordja

 

[Bokazoit]

Hmmm it's not only for LYFY, but also for YTDLY, YTDTY, DMLY and DMTY.

But why can't I just make the dlookup? and why doesn't it work. I mean it works when I use the exact segment-label, but not when I use a fieldname in report ?:/