Solved

SQL Syntax and Logic Help - Match Merge Data in two tables

Posted on 2006-07-06
11
306 Views
Last Modified: 2012-05-05
rs through rs3 works fine.  rs4 is where i am having an issue.

here is the short version of what i want to do:

the database search starts from a text box, query's CONSDB_MBRHISTDETL by MBRSEP.  CONSDB_MBRHISTDETL has the MBRSEP and LOCATION fields.  CONSDB_LOCINFODETL has the LOCATION field but NOT the MBRSEP field.  how do i match the LOCATION fields on both tables to be able to pull the SVCADDR field from CONSDB_LOCINFODETL based on my MBRSEP query?

i made my attempt with rs4, but i think the logic is wrong, along with the syntax.  any help would be appreciated.

<%
Dim Con
Dim rs
Dim rs2
Dim rs3
Dim rs4

Set rs = Server.CreateObject ("ADODB.Recordset")
Set rs2 = Server.CreateObject ("ADODB.Recordset")
Set rs3 = Server.CreateObject ("ADODB.Recordset")
Set rs4 = Server.CreateObject ("ADODB.Recordset")

Set Con = Server.CreateObject("ADODB.Connection")
Con.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\Inetpub\wwwroot\Intranet\bill\database\bills.mdb"

mySQL1 = "select * from CONSDB_MBRHISTDETL where MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
mySQL2 = "select * from CONSDB_MBRSEPMSTR where MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
mySQL3 = "select * from CONSDB_MEMBERDETL where MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
mySQL4 = "select SVCADDR from CONSDB_LOCINFODETL where rs("LOCATION") = rs4("LOCATION") AND MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"

Set rs = Con.Execute(mySQL1)
Set rs2 = Con.Execute(mySQL2)
Set rs3 = Con.Execute(mySQL3)
Set rs4 = Con.Execute(mySQL4)

Con.Execute mySQL1
Con.Execute mySQL2
Con.Execute mySQL3
Con.Execute mySQL4
%>


<%

IF rs.EOF Then
      Response.Redirect ("../nodata.html")
end if      

do while not rs.eof
      MBRSEP = rs("MBRSEP")
      DUEDATE      = rs("DUEDATE")
      KWH      = rs("KWH")
      NAME = rs2("NAME")
      TELEPHONE = rs3("TELEPHONE")
      ADDR1 = rs2("ADDR1")
      CYCLE = rs3("CYCLE")
      RATE = rs3("RATE")
      LOCATION1 = rs("LOCATION")
      LOCATION2 = rs4("LOCATION")

%>
0
Comment
Question by:bschwarting
[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
  • 6
  • 4
11 Comments
 
LVL 1

Author Comment

by:bschwarting
ID: 17053470
FYI, here is the error i get on my syntax.

Microsoft VBScript compilation  error '800a0401'
Expected end of statement
/bill/cgi-bin/search_member_sep_electric.asp, line 28
mySQL4 = "select SVCADDR from CONSDB_LOCINFODETL where rs("LOCATION") = rs4("LOCATION") AND MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17054011
>mySQL4 = "select SVCADDR from CONSDB_LOCINFODETL where rs("LOCATION") = rs4("LOCATION") AND MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"

this cannot work like this, but this will:

mySQL4 = "select a.SVCADDR from CONSDB_LOCINFODETL a JOIN CONSDB_MBRHISTDETL b on a.LOCATION = b.LOCATION and a.MBRSEP = b.MBRSEP where MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"

0
 
LVL 1

Author Comment

by:bschwarting
ID: 17054084
ok, i tried that, here is what i got:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
/bill/cgi-bin/search_member_sep_electric.asp, line 34

line 34 --> Set rs4 = Con.Execute(mySQL4)

mySQL4 = "select a.SVCADDR from CONSDB_LOCINFODETL a JOIN CONSDB_MBRHISTDETL b on a.LOCATION = b.LOCATION and a.MBRSEP = b.MBRSEP where MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 17054132
Obviously I missed the "ACCESS Database" information...

Access requires to write INNER JOIN (and not simply JOIN):

mySQL4 = "select a.SVCADDR from CONSDB_LOCINFODETL a INNER JOIN CONSDB_MBRHISTDETL b on a.LOCATION = b.LOCATION and a.MBRSEP = b.MBRSEP where MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
0
 
LVL 1

Author Comment

by:bschwarting
ID: 17054164
ok, now i get this:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/bill/cgi-bin/search_member_sep_electric.asp, line 34
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17054215
any better with this:
mySQL4 = "select a.SVCADDR from CONSDB_LOCINFODETL a INNER JOIN CONSDB_MBRHISTDETL b on a.LOCATION = b.LOCATION and a.MBRSEP = b.MBRSEP where a.MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"
0
 
LVL 1

Author Comment

by:bschwarting
ID: 17054281
same thing:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/bill/cgi-bin/search_member_sep_electric.asp, line 34
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 17054440
could you take the sql generated and run that directly on access, and tell us what he prompts you for?
0
 
LVL 1

Author Comment

by:bschwarting
ID: 17054660
how is best to do that?
0
 
LVL 25

Assisted Solution

by:clockwatcher
clockwatcher earned 100 total points
ID: 17056527
You said:

  CONSDB_LOCINFODETL has the LOCATION field but NOT the MBRSEP field.  

based on that give this a try:

  select a.SVCADDR from CONSDB_LOCINFODETL a INNER JOIN CONSDB_MBRHISTDETL b on a.LOCATION = b.LOCATION where b.MBRSEP LIKE '%" & replace(Request.Form("MBRSEP"),"'","''") & "%'"

and you may really be after this:

  select distinct a.SVCADDR from CONSDB_LOCINFODETL a INNER JOIN CONSDB_MBRHISTDETL b on a.LOCATION = b.LOCATION where b.MBRSEP LIKE '%" & replace(Request.Form("MBRSEP"),"'","''") & "%'"

If your history detail has multiple records per mbrsep and you only want a the distinct svcaddr-- not sure what you're really after nor clear on your data structure but based on the fact you're only pulling SVCADDR the distinct clause probably makes more sense.
0
 
LVL 1

Author Comment

by:bschwarting
ID: 17063500
i couldn't get any of the above to work, so i'm trying another route.  i've setup the query to retrieve the data in access, then tried to convert that code to work in ASP.  can anyone assist?

FYI, the 330 at the end needs to be CONSDB_MEMBERDETL.MBRSEP LIKE '%" & Request.Form("MBRSEP") & "%'"

#################
ACCESS GENERATED SQL
#################

SELECT CONSDB_MEMBERDETL.MBRSEP, CONSDB_MBRSEPMSTR.NAME, CONSDB_MEMBERDETL.RATE, CONSDB_MEMBERDETL.CLASS, CONSDB_MEMBERDETL.TELEPHONE, CONSDB_MBRHISTDETL.LOCATION, CONSDB_MEMBERDETL.METER, CONSDB_MBRHISTDETL.READDATE, CONSDB_MBRHISTDETL.READDATE, CONSDB_MBRHISTDETL.NBRDAYSSVC, CONSDB_BILLHISTDETL.PREVMTRREAD, CONSDB_BILLHISTDETL.METERREAD, CONSDB_MBRHISTDETL.KWH, CONSDB_MBRHISTDETL.FUEL2, CONSDB_MEMBERDETL.APPLNBR

FROM CONSDB_MEMBERDETL INNER JOIN ((CONSDB_MBRSEPMSTR INNER JOIN CONSDB_BILLHISTDETL ON CONSDB_MBRSEPMSTR.MBRSEP = CONSDB_BILLHISTDETL.MBRSEP) INNER JOIN (CONSDB_MBRHISTDETL INNER JOIN CONSDB_LOCINFODETL ON CONSDB_MBRHISTDETL.LOCATION = CONSDB_LOCINFODETL.LOCATION) ON CONSDB_BILLHISTDETL.MBRSEP = CONSDB_MBRHISTDETL.MBRSEP) ON CONSDB_MEMBERDETL.MBRSEP = CONSDB_MBRSEPMSTR.MBRSEP

WHERE (((CONSDB_MEMBERDETL.MBRSEP)=330));

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 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