Thank you so much for answering me, I used the code you gave me and it didn’t
help, I must be doing something wrong,, here is the code.
SELECT [PRODUCT TABLE].[Customer ID], [PRODUCT TABLE].ProductName, [RADIO
STATIONS TABLE].[Station Call Letters], [PRODUCT TABLE].[Target Audience
ID].Value, [RADIO STATIONS TABLE].[Market ID], [RADIO STATIONS TABLE].Rank,
[Rank Table].Rank, [PRODUCT TABLE].Rank, [PRODUCT TABLE].Rank
FROM [MARKETS TABLE] INNER JOIN ((([FORMATS TABLE] INNER JOIN [RADIO
STATIONS TABLE] ON [FORMATS TABLE].Format = [RADIO STATIONS TABLE].[Format
ID]) INNER JOIN [TARGET AUDIENCE TABLE] ON [FORMATS TABLE].Format = [TARGET
AUDIENCE TABLE].Format.Value) INNER JOIN ([Rank Table] INNER JOIN [PRODUCT
TABLE] ON [Rank Table].ID = [PRODUCT TABLE].Rank) ON [TARGET AUDIENCE
TABLE].[Target Audience ID] = [PRODUCT TABLE].[Target Audience ID].Value) ON
([RADIO STATIONS TABLE].[Market ID] = [MARKETS TABLE].[Market ID]) AND
([MARKETS TABLE].[Market ID] = [PRODUCT TABLE].[Market ID].Value)
WHERE ((([PRODUCT TABLE].ProductName) Like "*" & [Enter Product] & "*") AND
(([RADIO STATIONS TABLE].Rank)<=[RANK TABLE]![Rank]) AND (([PRODUCT
TABLE].Rank)=[Forms]![Maintain Products Form]![Rank])) OR ((([PRODUCT
TABLE].ProductName) Like "*" & [Enter Product] & "*") AND (([RADIO STATIONS
TABLE].Rank)<=[RANK TABLE]![Rank]) AND (([Forms]![Maintain Products
Form]![Rank]) Is Null))
ORDER BY [PRODUCT TABLE].[Customer ID], [PRODUCT TABLE].ProductName, [RADIO
STATIONS TABLE].[Station Call Letters];
I told you the lookup fields I had were ‘rank’ and ‘location’,,, but
location is actually called Market ID in my db,, I just didn’t want to
confuse you,,,
And here is the just of what I am trying to do:
This DB is for a company that is a broker for advertisers and radio
stations. Based on the target audience and other critieria of the
Advertiser’s Product, this query tries to find Radio Stations that match the
criteria to run the ads
Tables:
Product Table - contains the product info being advertised and criteria to
find the most suitable Radio Stations
Radio Station Table – list of radio stations with formats, ranks, markets
Criteria Tables:
Format Table – list of Radio Station formats. (Each Radio Station has 1
format, such as Rock, News, Sports, Hip Hop, Jazz, etc)
Target Audience Table – each target audience has a multiselect lookup to the
Format Table, ie if the Product is Burger King Whopper, the Target Audiences
might be Spanish, and Teens.
Teens in the target audience table might have the following formats: ‘hip
hop’, ‘rock’, Top 40.
Spanish Target Audience might have formats: Hip Hop, Hot Urban, Span CHR,
Urban, etc
Rank Table – can be Top 5, Top 10, Top 50, Top 100, the advertiser may only
want to go with Radio stations that are ranked in the Top 10, Top 20, Top 100
etc.
Market Table – Each Radio Station has 1 market, ie LA,r NY, Boston, etc
The query matches Target Audience -> Format -> Radio Station and this works
fine.
For Rank I check that the Radio Station Rank is less than or eq the drop
down selection from the Product Table Rank, which is a lookup field to the
Rank Table.
This also works fine
Except if they don’t select anything in the Rank Drop Down, then no radio
stations are returned. When I added the code you gave me, I get the same
results when nothing is selected.
I have not tried adding the code for the Target Audience or Market yet, I
think I will wait for your feedback before I go any further. Thanks in
advance for any help you can provide!!!
Nancy
The whole expression goes in the first 'criteria' row of the relevant column
in query design view, so in the Rank column's first 'criteria' row you'd put:
[quoted text clipped - 58 lines]
form,
it will look for all fields that are "*". Any suggestions?
