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

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
LVL 1
mflagstadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hartCommented:
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
MauseCommented:
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
Nathan Stanford SrSenior ProgrammerCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

mflagstadAuthor Commented:
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
mflagstadAuthor Commented:
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
MauseCommented:
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
MauseCommented:
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
MauseCommented:
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
mflagstadAuthor Commented:
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
mflagstadAuthor Commented:
Is there maybe a problem with the actual form that is being subitted?
0
MauseCommented:
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
mflagstadAuthor Commented:
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
MauseCommented:
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
mflagstadAuthor Commented:
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
MauseCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.