Help with table names in a query

[jpatchak]

I have a query that uses a person's state and other demographic information to look up rate factors. The rate factors are stored in tables. Each state has its own table (each state will have at least 15,000 records). I can get the rate factors to pull up correctly if I use a bunch of nested IIF statements. For example;

IIF(tblPerson.State = "AL", (Select [Factor] from [tblAL] where ...), IIF(tblPerson.State = "AZ", (Select [Factor from [tblAZ] where...), IIF(...

you get the idea - on and on for each state. It returns a single value. Is there an easier way to do this? We're talking 50 states here and my "where" conditions are somewhat complex - making the expression for this field extremely unwieldy.

Also, while we're at it, is there a way to do this for table fields as well. For instance, let's say I've got 50 fields in a table each corresponding to a state - is there a way to select the field name based on the person's state. Again, I can do it with nested conditionals, but it's messy:

IIF(tblPerson.State = "AL", (Select [fldAL] from [Table] Where ...), IIF(tblPerson.State = "AZ", (Select [fldAZ] from [Table] Where ...), IIF...

[cyberdwarf]

Couple of thoughts...

1. You could normalize your data by pulling all your tables into one and adding the state code as an index
2. You could use SQL to create a SELECT query with UNION links to each bit

Of course you may not want normalized data coz of the number of records and the potential performance hit, but IMHO it sure as hell beats trying to code it your way..

HTH

Steve

[MMcCarthy]

Create a union query, you can only do this in sql view by the way. Since I don't know what the where statement is based on I am only guessing its included here. If you tell me what it is I'll have a better idea.

SELECT [Factor] FROM [tblAL]
WHERE ....
UNION
SELECT [Factor] FROM [tblAZ]
WHERE ....
UNION
SELECT [Factor] FROM ... etc.

This will give you one query to join to tblPerson

I can help a lot more if you give me the relevant fields from both tables and the relationship between the table and explain the WHERE condition you're using and why...

[jpatchak]

I appreciate your patience. I am pretty new to SQL programming. As an example, I'll use one of my simpler tables (there are 10 tables for each state). Let's call there are 4 fields in this table. The first two are lookup fields - Spouse (Y or N) and Children (0 to 10 without spouse, 0 to 9 with spouse). So, there are 20 combinations of these two fields. Then there are two factors stored in this table, one called BMM, the other called RX.

To get the BMM factor for a prospect, I would normally use the following SELECT query:

SELECT [BMM] from [tblAL]
WHERE [tblAL].[SpouseYN] = [tblProspectInfo].[SpouseYN] AND [tblAL].[Children] = [tblProspectInfo].[Children]

Unfortunately, not all the tables are this simple. They have more lookup fields that are used in the where statement. This results in some huge tables for each state. All the Queries that get factors from these tables have a similar form though:

SELECT [field] from [tblState]
WHERE {info about the prospect from tblProspectInfo matches lookup fields}

Does this give you enough info to help further? When I tried to make a Union query on just two states, using the example you gave me, it was treating the fields from the tblProspectInfo table in the "Where" clause as parameters and asked for user input.

[MMcCarthy]

SELECT [BMM] from [tblAL]
WHERE [tblAL].[SpouseYN] = [tblProspectInfo].[SpouseYN] AND [tblAL].[Children] = [tblProspectInfo].[Children]

I don't see how this ever worked as you are not referencing the second table.

SELECT [BMM]
FROM [tblAL] INNER JOIN tblProspectInfo
ON ([tblAL].[SpouseYN] = [tblProspectInfo].[SpouseYN] AND [tblAL].[Children] = [tblProspectInfo].[Children])
UNION
SELECT [BMM]
FROM [tblAZ] INNER JOIN tblProspectInfo
ON ([tblAZ].[SpouseYN] = [tblProspectInfo].[SpouseYN] AND [tblAL].[Children] = [tblProspectInfo].[Children])
UNION

etc

GROUP BY [BMM];

This would be a lot easier if you weren't trying to join on more than one value like this. This is why Primary and Foreign keys are so important.

If you had a foreign key reference in each of the tables to the primary key of the tblState you could just join on that key.

I think you are going to continue to have a lot of problems unless you go back and look at the way you have created your tables.

Check out the post at the top of the Access Forum called Links to Useful Sites.

http://www.thescripts.com/forum/thread562851.html

There is a link in there on how to structure your tables. Going back now and fixing them will save you a lot of time later on.

Have a good look at the site and try reforming your tables yourself then if you've any questions please ask.

I appreciate your patience. I am pretty new to SQL programming. As an example, I'll use one of my simpler tables (there are 10 tables for each state). Let's call there are 4 fields in this table. The first two are lookup fields - Spouse (Y or N) and Children (0 to 10 without spouse, 0 to 9 with spouse). So, there are 20 combinations of these two fields. Then there are two factors stored in this table, one called BMM, the other called RX.

To get the BMM factor for a prospect, I would normally use the following SELECT query:

SELECT [BMM] from [tblAL]
WHERE [tblAL].[SpouseYN] = [tblProspectInfo].[SpouseYN] AND [tblAL].[Children] = [tblProspectInfo].[Children]

Unfortunately, not all the tables are this simple. They have more lookup fields that are used in the where statement. This results in some huge tables for each state. All the Queries that get factors from these tables have a similar form though:

SELECT [field] from [tblState]
WHERE {info about the prospect from tblProspectInfo matches lookup fields}

Does this give you enough info to help further? When I tried to make a Union query on just two states, using the example you gave me, it was treating the fields from the tblProspectInfo table in the "Where" clause as parameters and asked for user input.