Microsoft Access Sub-query

[englishtwit]

Hi

Version 2000 of MS-Access.

I am trying to get all the records relating to suppliers that supply printing as part of their function. I.E. Joe Bloggs supplies printing services, but he might also supply jeans.

Rather than go through all suppliers, I thought a sub-query would do it, so the logic is (to me)

Find me all the names of the suppliers who supply printing, and tell me what else they cost as well.

The query looks like:

Select [Ap Spend 0304].[Supplier Name],[AP spend 0304].[Net spend] from
[Ap spend 0304] where exists
(SELECT [AP Spend 0304].[Supplier name],[subjective codes].[Description]
FROM ([AP Spend 0304] INNER JOIN [Code Combinations] ON [AP Spend 0304].[Dist Code Comb ID]=[Code Combinations].[Code Combination ID]) INNER JOIN [Subjective Codes] ON [Code Combinations].[Subjective]=[Subjective Codes].[Subjective]
where [subjective codes].[Description] like '*print*');

So this should return all suppliers who provide print services and all the other records that are related to those suppliers.

At the moment it returns ALL suppliers data.

look forward to your responses!

ET

 

[PHV]

Something like this ?
Select [Ap Spend 0304].[Supplier Name],[AP spend 0304].[Net spend] from
[Ap spend 0304] where [Supplier Name] IN
(SELECT DISTINCT [AP Spend 0304].[Supplier name]
FROM ([AP Spend 0304] INNER JOIN [Code Combinations] ON [AP Spend 0304].[Dist Code Comb ID]=[Code Combinations].[Code Combination ID]) INNER JOIN [Subjective Codes] ON [Code Combinations].[Subjective]=[Subjective Codes].[Subjective]
where [subjective codes].[Description] like '*print*');


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

 

[englishtwit]

PH

Thanks for this - it's taking a long while to run, but I'll let you know!

ET