Searching Multible Unrelated Unconforming Tables 2

[luke242]

Hello,
I am a relative novice so bear with me.

I have a database that has six tables in it (Orders 2003, Orders 2002, Orders 2001, Orders 2000, Orders 1999, and Orders 1998). Each table contains was created by different office staff over a number of years. They all have some of the some basic data, but some include extra fields, some have the same fields but different names, etc. They each have there own separate key value, called either file number or file id, and some duplicates of this value exist between the different tables over the years. Because of the duplicates, my understanding is that no real relationship exists between these tables.

What I would like to do is search all six tables for a specified address (they all have an address field, though there names do not conform) and return all matches. The information included in the new table would be the address, borrower name, and file id or file number. Again, each of these fields exists in all tables, but they may be named differently and the file id can not be used as a key field over multiple tables since there are duplicate values.

How would I go about setting up this search?

Again, I new to access, but one thought I had was to setup queries for each individual table and to have the output from these queries conform to some standards and then somehow combined the outputted data into one table. Not really sure if this is feasible or if it would provide the ease of use that I am looking for.

Thanks for any help,
~Luke

 

[scriverb]

This is a very iffy situation at best as an address field is usually free format based on the user entering the data and this can be off by one character and a "NO match" can take place very easily. We can construct a query where the criteria will look for a specific address from a table and append the record with the appropriate information in a new table. This can be done with each table using their own unique table names and append the records as needed to the table. Each record can have an indicator as to the Table name from which it was found. In the end you would have a table of all of the matches to the selected address with the corresponding information that you need from that table.

Is this something that you could use. If so I can get you started but I need the field names of a couple of tables. Not all the field names just the ones that you are interested in and what you would like them to be called in the new table. I will develope a couple of SQL strings that can be pasted into a query to start the process. You can then modify each of them for each of your tables and after running them all in sequence the table and data requested will be in the new table.

Post back with the information as requested.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[luke242]

Got my answer from another forum. Information is posted below for others referance:
___________________________________________________

You could use UNION to join different queries. This can only be done in SQL view (i think). Be sure the returned columns all have the same name (you can use aliases), and that all columns are represented in each Select statement. Otherwise the query will bomb. EG:

Select A, B, C, D, E FROM Table1
UNION SELECT A, B, C, D, F as E From Table2
UNION SELECT A, B, C, 0 as D, E From Table3;

Note in table2 you are giving field F an alias of E. If Table3 doesn't have a Field D, you can assign a value (or use a function), zero here, and give it the alias D.

--------------------
J. Hamilton

 

[scriverb]

Luke,great!! but they have forgotten the WHERE clause for each of the Selects. They each need to reference the correct field in their tables and the prompt for the search address needs to be identical for each WHERE clause so that you only get prompted once.

If you want a table from this UNION query Name and save it as a SAVED query. Now take that SAVED query in as input to a Make-table query. That should do it.

Good luck.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[luke242]

scriverb,
Yes, if correctly follow what you are saying, that would definately be valuable. Also, when the user searches for an address, they will only use the beginning. (Ex: "5232 Tablot" for "5232 Talbot Rd S" or "5232 S" for "5232 S Tablot Rd"). This may return more than a couple results, but it should be be easy to see which on is correnct. It would be very nice to know which table they come from.

Here is the information you requested:
Table
Fields1, Field2, etc
New Field1, etc

Orders 1999
File #, Street Address, Appraiser
File #, Address, Appraiser
Orders 2000
File #, Address, Appraiser
File #, Address, Appraiser
Orders 2001
File #, Address, Appraiser
File #, Address, Appraiser

Thanks for your help,
~Luke Campbell

 

[lespaul]

plus you don't need the alais information in the subsequent queries:

SELECT A,B,C,D,E,F From table1
UNION
SELECT D,A,C,B,F,G from Table1
UNION
SELECT Z,Y,X,'something',U,V from table3


Leslie

 

[scriverb]

Here is some SQL that you can work with. If any of the field names for each respective table is different just go ahead and change it. The recordset will end up with the field names from the first select. You will only be prompted once for the search address beginning. The records will be sorted by the [File #] field. There is a new field called Order_Year that has the four digit Year of the records.

Select A.[File #], A.[Street Address], A.[Appraiser], "1998" as Order_Year FROM Orders_1998 WHERE A.[Street Address] Like [Enter Search Address Beginning: ] & "*" ORDER BY A[File #] 
UNION
Select B.[File #], B.[Street Address], B.[Appraiser], "1999" as Order_Year FROM Orders_1999 AS B WHERE B.[Street Address] Like [Enter Search Address Beginning: ] & "*" 
UNION 
Select C.[File #], C.[Street Address], C.[Appraiser], "2000" as Order_Year FROM Orders_2000 AS C WHERE C.[Street Address] Like [Enter Search Address Beginning: ] & "*" 
UNION 
Select D.[File #], D.[Street Address], D.[Appraiser], "2001" as Order_Year FROM Orders_2001 AS D WHERE D.[Street Address] Like [Enter Search Address Beginning: ] & "*" 
UNION 
Select E.[File #], E.[Street Address], E.[Appraiser], "2002" as Order_Year FROM Orders_2002 AS E WHERE B.[Street Address] Like [Enter Search Address Beginning: ] & "*" 
UNION 
Select F.[File #], F.[Street Address], F.[Appraiser], "2003" as Order_Year FROM Orders_2003 AS F WHERE F.[Street Address] Like [Enter Search Address Beginning: ] & "*";

Let me know if I can help with anything more.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[luke242]

Excellent!!
I put my variables in and it worked beautifully. Thank you very much for your help.

Below is the final code anyone searching for answers in the future.

~Luke

Select  [ORDERS 1998].[File #], [ORDERS 1998].[Street Address], [ORDERS 1998].[Appraiser], "1998" as [Order Year] FROM [ORDERS 1998] WHERE [ORDERS 1998].[Street Address] Like [Enter Search Address Beginning: ] & "*" ORDER BY [ORDERS 1998].[File #] 
UNION
Select [ORDERS 1999].[File #], [ORDERS 1999].[Street Address], [ORDERS 1999].[Appraiser], "1999" as [Order Year] FROM [ORDERS 1999] AS [ORDERS 1999] WHERE [ORDERS 1999].[Street Address] Like [Enter Search Address Beginning: ] & "*" 
UNION 
Select [ORDERS 2000].[File #], [ORDERS 2000].[Address], [ORDERS 2000].[Appraiser], "2000" as [Order Year] FROM [ORDERS 2000] AS [ORDERS 2000] WHERE [ORDERS 2000].[Address] Like [Enter Search Address Beginning: ] & "*" 
UNION 
Select [ORDERS 2001].[File #], [ORDERS 2001].[Address], [ORDERS 2001].[Appraiser], "2001" as [Order Year] FROM [ORDERS 2001] AS [ORDERS 2001] WHERE [ORDERS 2001].[Address] Like [Enter Search Address Beginning: ] & "*" 
UNION 
Select [ORDERS 2002].[File #], [ORDERS 2002].[Address], [ORDERS 2002].[Appraiser], "2002" as [Order Year] FROM [ORDERS 2002] AS [ORDERS 2002] WHERE [ORDERS 2002].[Address] Like [Enter Search Address Beginning: ] & "*" 
UNION Select [ORDERS 2003].[File #], [ORDERS 2003].[Address], [ORDERS 2003].[Appraiser], "2003" as [Order Year] FROM [ORDERS 2003] AS [ORDERS 2003] WHERE [ORDERS 2003].[Address] Like [Enter Search Address Beginning: ] & "*";