Solved

Search Form / Searching Multiple Tables w/ different fields in each

Posted on 2003-11-20
15
245 Views
Last Modified: 2013-12-24
Ok, now I need a search form that can search multiple tables in the same db (Access). The only thing is there are different fields in some of the tables (they exist in the others, but not the same number of fields).

4 tables:
A,B,C,D

Fields in A:
1,2,3,4,5

Fields in B:
1,2,3,4,5

Fields in C:
1,2,3,4,5

Fields in D:
1,2,4

Anyone have some quick code for me???
God forbid I do it myself :)

Thanks,
Michelle
0
Comment
Question by:mflagstad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 11

Expert Comment

by:hart
ID: 9795033
could u be more descriptive by giving the field names properly

is there relationshipd between A and B
B and C, C and D ???

then u can write a join get all the corresponding records

say field 1 in A is pk
and field 2 in B is fk

say field1 in B is pk
and field2 in C is fk

and so on...


then joins can help u search records across tables

Regards
Hart
0
 
LVL 10

Expert Comment

by:Mause
ID: 9795356
If the fields are the same in the tables you can also use union like this:

select 1, 2, 3, 4, 5
from A
union
select 1, 2, 3, 4, 5
from B
union
select 1, 2, 3, 4, 5
from C
union
select 1, 2, '' as 3, 4, '' as 5
from D

Mause
0
 
LVL 5

Expert Comment

by:nathans
ID: 9796704
You can give us FAKE names just change the name to make sense so we know what goes together and what are Primary keys etc...
0
Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

 
LVL 1

Author Comment

by:mflagstad
ID: 9796787
here is current search pages:

search.cfm
<form method="GET" action="searchresults.cfm" name="get">
 
  <div align="left">
    <table border="0" cellpadding="0" cellspacing="0">
      <tr>
        <td>Name:</td>
        <td><input type="text" name="name" size="20"></td>
      </tr>
      <tr>
        <td>Lot:</td>
        <td><input type="text" name="lot" size="20"></td>
      </tr>
      <tr>
        <td>Date:</td>
        <td><input type="text" name="date" size="20"></td>
      </tr>
      <tr>
        <td>Many:</td>
        <td><input type="text" name="many" size="20"></td>
      </tr>
      <tr>
        <td>Cremains/Burial:</td>
        <td><input type="text" name="cb" size="20"></td>
      </tr>
    </table>
  </div>
  <p><input type="submit" value="Submit"></p>
</form>


searchresults.cfm

<cfquery name="get" datasource="kidwell" dbtype="ODBC">
SELECT *
from sectionA INNER JOIN peopleA ON sectionA.ID = peopleA.division
union
SELECT *
from sectionBINNER JOIN peopleBON sectionB.ID = peopleB.division
union
SELECT *
from sectionPINNER JOIN peoplePON sectionP.ID= peoplePdivision
union
SELECT *
from sectioncol INNER JOIN peoplecol ON sectioncole.ID = peoplecol.division
union
 </cfquery>

<cfoutput query="get">
<cfif get.recordcount gt 0>
Click on the name below to edit information.<br>
<a href="edit.cfm?id=#id#">
#name##date##id#
</a>
<cfelse>

There were no listings found.  Please try again.
</cfif>
</cfoutput>
</div>

However, this produces an error. How do I do this?
Thanks,
Michelle
0
 
LVL 1

Author Comment

by:mflagstad
ID: 9796848
Sorry, I forgot to include the data names, etc.:

peopleA: name, lot, id, date, many, cb,division (fk)
peopleB: name, lot, id, date, many, cb,division(fk)
peopleP: name, lot, id, date, many, cb,division(fk)
peopleCOL: id, name, date,division(fk)

sectionA:ID (PK)
sectionB: ID(PK)
sectionP:ID(PK)
sectionCOL: ID(PK)

0
 
LVL 10

Expert Comment

by:Mause
ID: 9796877
You dont need the last union (just before </cfquery>) and there need to be a space before INNER
So it would be:
<cfquery name="get" datasource="kidwell" dbtype="ODBC">
SELECT *
from sectionA INNER JOIN peopleA ON sectionA.ID = peopleA.division
union
SELECT *
from sectionB INNER JOIN peopleBON sectionB.ID = peopleB.division
union
SELECT *
from sectionP INNER JOIN peoplePON sectionP.ID= peoplePdivision
union
SELECT *
from sectioncol INNER JOIN peoplecol ON sectioncole.ID = peoplecol.division
</cfquery>

Mause
0
 
LVL 10

Expert Comment

by:Mause
ID: 9796890
and the selected fields need to be the same:

 <cfquery name="get" datasource="kidwell" dbtype="ODBC">
SELECT name, lot, id, date, many, cb,division
from sectionA INNER JOIN peopleA ON sectionA.ID = peopleA.division
union
SELECT name, lot, id, date, many, cb,division
from sectionB INNER JOIN peopleBON sectionB.ID = peopleB.division
union
SELECT name, lot, id, date, many, cb,division
from sectionP INNER JOIN peoplePON sectionP.ID= peoplePdivision
union
SELECT name, '' as lot, id, date, '' as many, '' as cb,division
from sectioncol INNER JOIN peoplecol ON sectioncole.ID = peoplecol.division
</cfquery>
0
 
LVL 10

Expert Comment

by:Mause
ID: 9796891
and the selected fields need to be the same:

 <cfquery name="get" datasource="kidwell" dbtype="ODBC">
SELECT name, lot, id, date, many, cb,division
from sectionA INNER JOIN peopleA ON sectionA.ID = peopleA.division
union
SELECT name, lot, id, date, many, cb,division
from sectionB INNER JOIN peopleBON sectionB.ID = peopleB.division
union
SELECT name, lot, id, date, many, cb,division
from sectionP INNER JOIN peoplePON sectionP.ID= peoplePdivision
union
SELECT name, '' as lot, id, date, '' as many, '' as cb,division
from sectioncol INNER JOIN peoplecol ON sectioncole.ID = peoplecol.division
</cfquery>
0
 
LVL 1

Author Comment

by:mflagstad
ID: 9796917
here's the error message:

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.



The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (3:1) to (3:55).
0
 
LVL 1

Author Comment

by:mflagstad
ID: 9797000
Is there maybe a problem with the actual form that is being subitted?
0
 
LVL 10

Expert Comment

by:Mause
ID: 9797106
No its the FROM line in your query
did you pace a [SPACE] between 'sectionB' AND 'INNER' ???

And also between 'sectionP' and 'INNER'

Mause
0
 
LVL 1

Author Comment

by:mflagstad
ID: 9797140
yes... this is exactly what I have:
<cfquery name="get" datasource="kidwell" dbtype="ODBC">
SELECT name, lot, id, date, many, cb,division
from sectionA INNER JOIN peopleA ON sectionA.ID = peopleA.division
union
SELECT name, lot, id, date, many, cb,division
from sectionB INNER JOIN peopleBON sectionB.ID = peopleB.division
union
SELECT name, lot, id, date, many, cb,division
from sectionP INNER JOIN peoplePON sectionP.ID= peoplePdivision
union
SELECT name, '' as lot, id, date, '' as many, '' as cb,division
from sectioncol INNER JOIN peoplecol ON sectioncol.ID = peoplecol.division

</cfquery>

<cfoutput query="get">
<cfif get.recordcount gt 0>
Click on the name below to edit information.<br>
<a href="edit.cfm?id=#id#">
#name##date##id#
</a>
<cfelse>

There were no listings found.  Please try again.
</cfif>
</cfoutput>
</div>

Any clue what the problem is here?
0
 
LVL 10

Expert Comment

by:Mause
ID: 9797198
try this (you needed some more [SPACE]:

<cfquery name="get" datasource="kidwell" dbtype="ODBC">
SELECT name, lot, id, date, many, cb,division
from sectionA INNER JOIN peopleA ON sectionA.ID = peopleA.division
union
SELECT name, lot, id, date, many, cb,division
from sectionB INNER JOIN peopleB ON sectionB.ID = peopleB.division
union
SELECT name, lot, id, date, many, cb,division
from sectionP INNER JOIN peopleP ON sectionP.ID= peopleP.division
union
SELECT name, '' as lot, id, date, '' as many, '' as cb,division
from sectioncol INNER JOIN peoplecol ON sectioncol.ID = peoplecol.division
</cfquery>

0
 
LVL 1

Author Comment

by:mflagstad
ID: 9797633
OK it returned something, just not the right things.
It returned all the names, dates, and id's from ALL the tables. Not just the one I was searching for. How can I make it return just the one, or list of ones that match (like the date for example) or ones that "sound" like the one I was searching for?
Thanks for your help!
0
 
LVL 10

Accepted Solution

by:
Mause earned 100 total points
ID: 9797709
then you also need a WHERE in your query

like this:

SELECT name, lot, id, date, many, cb,division
from sectionA INNER JOIN peopleA ON sectionA.ID = peopleA.division
where date = #form.date#

or something like

SELECT name, lot, id, date, many, cb,division
from sectionA INNER JOIN peopleA ON sectionA.ID = peopleA.division
where name like '%#form.name#%'

0

Featured Post

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question