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.