Exception Table: How to make one?

[nameuser321]

Does anyone have an idea of how to create a query that pulls information from one table if a certain patern is matched, but if its not matched to pull that information from another table. For example to pull a specific location name from an "exception table" if it meets a complex criteria like "location code + allocation code", if it doesn't meet this "patern" to go to another table and pull the standard location name. If you need an example in access I'd be more than happy to provide one. Any suggestions will be very helpful. Thanks!

[tfurnivall]

This is probably too simplistic, and misunderstands your question but have you tried:

dim SQLString as string
dim TableName as string

if complicatedconditionismet then
   TableName="Table1"
else
   TableName="Table2"
end if

sqlstring="SELECT * FROM " & tablename & " WHERE "

'etc, etc

HTH - if not, perhaps you could expand your problem statement a little?

Tony

[nameuser321]

No that's pretty close. I'm just not 100% sure how to do that. Apperantly I can't load an example db to show you because it's not an approved forum file type. I have 3 tables. One with all the primary data (Main) another with location codes (Locations) and a third one containing the exceptions (Exceptions). Without the exception table this would be a simple relationship, just an inner join between the two tables. But since I have the exception table I need it to match the "center" first so 594:2411 and 61N:2411 should connect to Houston, but all other locations should be defined through the standard inner join between the two tables.


Exceptions

ID	Location_Code	Center	City
1		2411	Houston

Location

ID	City
345	Austin
594	Dallas
61N	Corpus Christi
62P	El Paso
634	Freeport

Main

ID	Location_Code	Center
1	594	2411
2	61N	2701
3	634	2301
4	62P	4210
5	61N	2411

[tfurnivall]

OK - seems like you are trying to solve the problem - What is the City for this Location Code/Center Combination?

Assuming that this is the case, and continuing on that basis, the critical 'test' values are Location Code of 61N and some Center code or other, or Location Code of 594 and a Center Code of 2411. Right?

So - the Exception takes precedence over the Main Table. (I'd love to know if there is any value at all to the Location Code field in the Exception Table...)
Can you, perhaps, share what these values represent - in terms of the real world? I'm trying to imagine a warehouse type application where if Location Code 594 orders a stock item with a Center value of 2411, then it should be ordered from Houston, rather than Dallas. Similarly if Corpus Christi wants an item like that it should come from Houston rather than Corpus Christi.

I realize you probably don't have much flexibility in database structure, but given my hypothetical scenario, there's an easy alternative design that pushes the 'decision' out to the item table. Your situation is undoubtedly different, but it would be interesting to hear the 'real world' problem you're trying to solve.

Tony

[mrojas]

As they say, there's more than one way to skin a cat.
I would use the DCount built-in function to find it there's a match in your "exception table". If the count is greater than zero, you've got a match. If you got a match, use the DLookup funtion to retrieve value.