alternative to nested iifs

[scotty22]

Hi guys,

I currently have a query where the first column [Flagged] consists of specific fields that happen to have been 'flagged' via another query. The other columns are all the remaining fields.

What I am trying to do is run a query so that it reads what field has been flagged, and then looks up that field, for that record, and passes that value to a column called [Mistakes]. I want this to be repeated for all records.

I came up with some partial solutions but all seemed to hit a snag. For example Dlookup - only looks for first value of a field, nested ifs - too many conditions to satisfy.

Any ideas? Have I explained my self properly?

Kind regards,

Scotty 22

[June7]

Nope, not fully understanding.

Post the SQL statements for analysis.

Give sample data.

[scotty22]

I thought I explained myself badly.

OK, I have found a way to do what I want, but I'm sure there's a less messy, more efficeint way to do it. So here's the SQL statement:

SELECT [Flag], iif([Flag]="Cover1", [Cover1],"") & iif([Flag]="Cover2",[Cover2],"")&iif(Flag="Premium",[Premium],"")&iif(Flag="Name",[Name],"")&iif(flag="Sex",[Sex],"") as [Values], [Coverage], [Cover1], [Cover2], [Premium], [Name], [Sex]
FROM Failed

However I need to do this for many more fields.

Is this any clearer?

Kind regards,

Scotty22

[June7]

Where is the column Mistakes you refer to? How do you pass data to it?
Show sample data, especially for the field Flag. How many more fields?

I don't understand what you are really trying to accomplish but offer the following.

DLookup finds the first value that meets the WHERE argument conditions which could include a unique record ID. The Flag field would be a variable for the desired search field and don't think can build a DLookup in Access query with a variable for field name. VBA might be another story. I have used variables in VBA to construct names of textboxes and recordset fields:
Form_formname.Controls(varName)
rs.Fields("Age" & i)

Never tried this directly on table or query object with DLookup in another query nor in VBA. Likely not possible. Use recordset. Build function that takes Flag and unique ID as arguments, opens recordset and gets value from appropriate field, and returns to calling query. Be aware, custom functions like this can slow down a query.

[scotty22]

Sorry, I inadvertently changed my field name from [Mistakes] to [Values].

The following fields will have typical data in them:
Cover1 Cover2 Premium Name Sex
10000 0 1.45 MR A M
6000 0 2.50 MRS B F
7000 0 5.00 MR C M
5500 2000 -5.50 MRS D F
8000 0 2.20 MR E M
6400 12000 0 MRS F F
1200 0 9.56 MR G M
8000 0 7.20 MRS H F
91000 0 5.50 MRS I F
15000 0 1.50 MR J M
2000 0 0 MR K M

and if the field [Flag] has the following data, then I want the [Values] field to correspond in this way:
Cover1 10000
Cover1 6000
Cover2 0
Sex F
Cover1 8000
Name MRS F
Premium 9.56
Sex F
Premium 5.50
Cover1 15000
Cover2 0

Is this any clearer?

In total, there will be about 75 fields to be used in this.

Kind regards,

Scotty22






I will be doing this for, if possible, about 70 different fields.

and so I want the field [V

[June7]

This DLookup works with the data sample you posted:
Values: DLookUp([Flag],"Table1","[Name]='" & [Name] & "'")

I hadn't thought it would but proved myself wrong. However, if there is more than one record for each Name, need a unique record ID as criteria instead.

[scotty22]

Ye there probably is more than one record for a given name. In fact, its more likely than not. The method I am using now, with the concatenates works fine and faster than I thought, but the code is quite messy and not very elegant.

However I am now lead to a different probelm. This table, which contains the flagged fields, will be updated every so often by an append query.

What I am trying to do is create a query which will only extract the latest records which have not been in the table before i.e. unique (with respect to their RecordID, flagged field, and flagged field value).

I am able to obtain new unique records however I also get old unique records too. Every time I try to add the max(date) or similar, I get only the new records, but the uniqueness disappears.

Any thoughts?

[June7]

What do you mean by 'uniqueness' disappears? Show the query statement.

[scotty22]

SELECT Sorted.Flags, Sorted.Not_Accepted, IIf([Flags]="Class",[Class],"") & IIf([Flags]="Name",[Name],"") & iif([Flags]="Sex",[Sex],"") & iif([Flags]="Cover1",[Cover1],"") & iif([Flags]="Cover2",[Cover2],"") & & iif([Flags]="Premium",[Premium],"") as [Values]
FROM Sorted
GROUP BY Sorted.Flags, Sorted.Not_Accepted, IIf([Flags]="Class",[Class],"") & IIf([Flags]="Name",[Name],"") & iif([Flags]="Sex",[Sex],"") & iif([Flags]="Cover1",[Cover1],"") & iif([Flags]="Cover2",[Cover2],"") & & iif([Flags]="Premium",[Premium],"")
HAVING (((Count(Sorted.Class))=1));

So at the moment this shows all records, from the table [Sorted] that are unique. However if I add a date field, then all records which may be unique are not anymore, because of the date field having different values for otherwise identical records.

[June7]

You want to use the IIf for 75 fields?

Fields Not_Accepted and Class were not in the original data sample. What is this data, why Class as filter criteria?

So by adding the date field, you get multiple records that would be identical except for the Date value. You want to show the later date?

Is this getting there (requires unique record ID from source table)?
SELECT Flags, Not_Accepted, Class, DLookUp([Flags],"Sorted","ID=" & [ID]) AS [Values], Max([Date]) AS MaxOfDate
FROM Sorted
GROUP BY Flags, Not_Accepted, Class, DLookUp([Flags],"Sorted","ID=" & [ID]);

[scotty22]

Doesn't seem to work. It pops up with a message box stating: Microsoft Office Can't find the name ****** you entered in the expression.

This could be becuase I'm searching for my own reference rather than an ID. Should I be using a primary key? Is there a way to enter one without resetting the table.

Many thanks,

Scotty22

[June7]

Needs to be unique ID. Could be an Autonumber field. Does the source data table have a unique ID field?

Why would table have to be 'reset'?

[scotty22]

OK, so I created an autonumber field on my table and used the code you suggested, with an extra condition. I get exactly the same results popping up as before - however the code is much nice...thank you very much.

The problem I have now, which is what I meant to say before but didn't, is I don't just want 'the latest' unique record shown up, but only the unique records of a particular date.

So at the moment I get all unique records, no matter when they were entered. I only want the unique records from, say, today. Is this doable?

Kind regards,

Scotty22

[June7]

One of your earlier posts mentioned trying Max(date) so I thought that was a need.

If you just want to filter on a date, include a WHERE clause in the SQL. For current date: WHERE [Date]= Date()

[scotty22]

Just tried it and that just filters through all records that are unique today. What I am trying to get is only unique records from the whole table, that have todays date. Not all todays records that are unique.

I suppose I could just run the code I have and then filter once I open the query, but I thought there could be another way.

Also, is there an alternative to Dlookup because it makes my query run quite slow?

Many thanks

Scotty22