Using a Sub-Query as an Expression

[BamaColtsFan]

I am working on an Access 2007 database. This database links to source data (via linked text files) from another system. One of the fields in the source data may possibly be null. When that condition exists, I want the query to look at another table and return the data from there.

The basic logic is thus: Look at the Timecard Approver in the table PPE I, if it is null, go to the Organization Management Data table and get the Timecard Approver from there based on the Group in PPE I. Otherwise, return the value originally found in PPE I.

The text of the expression I am trying to use is:

Timecard Approver: IIf([PPE I]![Timecard Approver] Is Null,(SELECT [Organization Management Data].[Timecard Approver] FROM [PPE I] INNER JOIN [Organization Management Data] ON [PPE I].Group = [Organization Management Data].[Organization Name] GROUP BY [PPE I].Group, [Organization Management Data].[Timecard Approver]),[PPE I]![Timecard Approver])

The error I get is "At most one record can be returned by this subquery."

So, the question is what am I doing wrong? If a subquery is not the proper method to retrieve the data, what is? Is there another function that might return the needed results? Any advice would be appreciated...

 

[LPurvis]

To use an in-line subquery (which returns a SELECT calculated column) the error message gives you the clue. You must return, at most, one row.
Your statement as it stands is presumaby returning more - most likely because you're not filtering the results at all. It's returning rows for all Groups. (Because the [PPE I] table you're joining to in the subquery is a different instance of that table than in the main query).

You could probably use an expression like

Timecard Approver: IIf([PPE I].[Timecard Approver] Is Null, (SELECT [Timecard Approver] FROM [Organization Management Data] WHERE [PPE I].Group = [Organization Management Data].[Organization Name] GROUP BY [Timecard Approver]),[PPE I].[Timecard Approver])

I've changed your bang object separators to dots (as I hate bang used for data objects in queries - I wish Jet wouldn't support it
And filtered the subquery using criteria from the main query (which I presume is [PPE I]).
The Group By worries me slightly. It sounds like you're picking a single value from many returned? Which leaves the potential for more than one row returned again?

Joining to your [Organization Management Data] table in the main query is another option - and should be more efficient. But you're then into even more of an issue with multiple rows.
And it's not possible to comment on that with accuracy without seeing the data.

Cheers.

 

[BamaColtsFan]

Leigh,

Your changes worked PERFECTLY! Thanks so much for your assistance!

 

[LPurvis]

Nae bother.