Dlookup alternative

[scotty22]

Hi there,

I have this query which works relatively well with a a couple or a few thousand records. However it takes too long or just doesn't work with an amount of say 30,000. Is there an alternative method to that I am using?

SELECT f.Flags, f.Not_Accepted, DLookup([Flags],"Failed_Cols","ID=" & [ID]) AS [Values], Max([DateRef]) AS Date_Flagged, f.name
FROM Failed_Cols AS f


WHERE [DateRef]=[Enter Valuation Date:] Or [DateRef]=[Enter Old Valn Date:]
GROUP BY f.Flags, f.Not_Accepted, DLookup([Flags],"Failed_Cols","ID=" & [ID]), f.name
HAVING (Count(f.name)=1 AND Max([DateRef])=[Enter Valuation Date:]) Or Max([DateRef])=[Enter Old Valn Date:];


Thank you

Scotty22

[pbaldy]

I would expect to get that info with a join. With the query in design view, add the Failed_Cols table to the top section, and if a line doesn't automatically appear between the ID fields in each table, you can click and drag to put one there. Add the field from that table to the grid below and see if that does what you want. Joins are far more efficient than domain aggregate functions in queries.

[scotty22]

Add which field from that table?

Scotty22

[pbaldy]

The field you're looking up, Flags.

[scotty22]

This doesn't do what I need. This brings the entries from the Flags field into that column. What I need to do is bring in the entry from whatever field may be stated in the 'Flags' field.

For example, if a record has a value of Address in it's flags field, then I need the value from the address field for this record. Then if the next record has a value of Telephone in the flags field, then I need the telephone number.

Only the Dlookup function seems to allow me to read a value from a column, and then use that as another column name to read off another value.

Any ideas?

Kind regards,

Scotty22

[Rawb]

Can we see the Table structure you're using? It will probably help us figure out what's causing it to not work.

[scotty22]

How would I go about showing this? Do you want a description or a print screen view ?

[Rawb]

Either way would be fine. The easiest would likely be screenshot.

[pbaldy]

The easiest would be a sample of the db with the tables and your working query. The join should bring in the same value as the DLookup, unless I'm having serious brain cramps.

[scotty22]

Hi there,

I've uploaded the table structure (admitedly there are more columns but I left them out for simplicity).

@PBaldy:The Dlookup gives me the same as the join IF I changed

DLookup([Flags],"Failed_Cols","ID=" & [ID]) .....to.....

DLookup("Flags","Failed_Cols","ID=" & [ID])

Hope you guys can help.

Scotty22

[scotty22]

Any feedback on this?

Kind regards,

Scotty22

[Rawb]

I think I may have stumbled upon the answer. But to test it out fully, I'd need a sample of the data in your Table.

I you want to try and take it from my theoretical-stage to practical usage on your own, I've included my unfinished Query below.

SELECT 
    [FC_Outer].[Flags], 
    [FC_Outer].[Not_Accepted], 
    (
        SELECT 
            [FC_Inner].[Flags]
        FROM 
            Failed_Cols AS FC_Inner
        WHERE
            [FC_Inner].[ID]=[FC_Outer].[ID]
    ) AS [Values], 
    Max([FC_Outer].[DateRef]) AS [Date_Flagged], 
    [FC_Outer].[name]
FROM 
    Failed_Cols AS FC_Outer
WHERE 
    [FC_Outer].[DateRef]=[Enter Valuation Date:] Or 
    [FC_Outer].[DateRef]=[Enter Old Valn Date:]
GROUP BY 
    [FC_Outer].[Flags], 
    [FC_Outer].[Not_Accepted], 
    [Values]
    [FC_Outer].[name]
HAVING 
    (
        Count([FC_Outer].[name])=1 AND 
        Max([FC_Outer].[DateRef])=[Enter Valuation Date:]
    ) Or 
    Max([FC_Outer].[DateRef])=[Enter Old Valn Date:];

The spacing is just for readability. Once it's working, you can compress it down to a single line.

However, without seeing your actual data and knowing, I have no way of knowing if this will work (or even if it's what you want!).

[Rawb]

So, uhhh. . .

I just realized my Query was WAAAAY off.

If you look at the SubSELECT, you'll notice that all the whole thing does is look up the Flags Field for the current ID. This means, the Query is actually:

SELECT
    [FC_Outer].[Flags],
    [FC_Outer].[Not_Accepted],
    [FC_Outer].[Flags] AS [Values],
    Max([FC_Outer].[DateRef]) AS [Date_Flagged],
    [FC_Outer].[name]
FROM
    Failed_Cols AS FC_Outer
WHERE
    [FC_Outer].[DateRef]=[Enter Valuation Date:] Or
    [FC_Outer].[DateRef]=[Enter Old Valn Date:]
GROUP BY
    [FC_Outer].[Flags],
    [FC_Outer].[Not_Accepted],
    [Values]
    [FC_Outer].[name]
HAVING
    (
        Count([FC_Outer].[name])=1 AND
        Max([FC_Outer].[DateRef])=[Enter Valuation Date:]
    ) Or
    Max([FC_Outer].[DateRef])=[Enter Old Valn Date:];

Hooray for obfuscation!

[scotty22]

Surely that query is just doing the same? as the one above (I havent tried this as yet).

Essentially [values] should be equal to the value from the field [...], where [...] is the field stated in [Flags].

So a variable column name.

I'm sure I'm explaining this badly.

Scotty22

[pbaldy]

I'm sure I'm explaining this badly.

Not necessarily, but like I said, a sample db with the working query would go a long way to letting us see what it's doing and replicate it.