• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • Last Modified:

Generate report pulling data (links) from three tables

I have three tables:

tblContractJournals                tblContractJournalsSubs              tblGeneral
- JournalID                              - ChartID                                      = ChartID                  
- Title                                       - Facility                                       - Facility
- Type                                      - JournalID                                   - City            
- Cost
- Vendor

I need a report for a specific ChartID based on a querystring.

I need to link tblGeneral.ChartID to tblContractJournalsSubs.ChartID, then link the records that link tblContractJournalsSubs.JournalID to tblContractJournals.JournalID.

The from in the following sql statement is the problem. Could someone explain where the problem is?

sql2 = "SELECT tblGeneral.ChartID, tblGeneral.Facility, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, " & _
	"tblGeneral.FacName, tblGeneral.CorpName, " & _
	"tblContractJournalsSubs.JournalID, " & _
	"tblContractJournals.Title, tblContractJournals.Cost, tblContractJournals.Type, tblContractJournals.Vendor " & _
		"FROM (tblGeneral INNER JOIN tblContractJournalsSubs ON tblGeneral.ChartID= tblContractJournalsSubs.ChartID) " & _
		"INNER JOIN tblContractJournals on tblContractJournalsSubs.JournalID = tblContractJournals.JournalID " & _
		"WHERE ChartID=" & ChartID & ";"

Open in new window

0
Malloy1446
Asked:
Malloy1446
  • 14
  • 8
  • 6
  • +1
1 Solution
 
PatHartmanCommented:
You don't say exactly what the problem is.  One potential problem is that ChartID exists in two tables and so in the Where clause, you would need to specify to which table you wanted the field to refer.
0
 
Malloy1446Author Commented:
The ChartID in the querystring is selecting a unique record in tblGeneral.

ChartID is then used to select all the records in tblContractJournalsSubs. There are multiple records in this table with the same ChartID. The JournalID in tblContractJournalsSubs will be unique.

Next step is matching the tblContractJournalsSubs.JournalID with tblContractJournals.JournalID which stores the journal specific information.

Results will be
Location (from tblGeneral)
   Subscribes to JournalID from tblContractJournalsSubs which links to tblContractJournals.TItle

My error is Website cannot display page.
0
 
PatHartmanCommented:
Please run the query in Access.  If the query works when you run it in Access, then this isn't an Access question.  Otherwise, please add whatever software you are using to render the web page.

If the string is being built in the web code.  Stop the code after the string is built so you can copy the generated string and paste it into an Access query.

ChartID exists in BOTH  tblContractJournalsSubs AND tblGeneral Therefore

"WHERE ChartID=" & ChartID & ";"

Should be:

"WHERE tblGeneral.ChartID=" & ChartID & ";"
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Malloy1446Author Commented:
This is not an Access question. I am using Access only to store the data.

I am using ASP to pull the data from Access to generate a report and display on a web page. The problem  is my JOINS, but not sure what is wrong with the statement.
eResources.mdb
0
 
PatHartmanCommented:
Please add ASP to the topics to attract the attention of the people who can help you.  It is unlikely any Access person can help so you need ASP people
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
try specifying which tables chartID you want to use. change your where clause from

"WHERE ChartID=" & ChartID & ";"

to

"WHERE tblGeneral.ChartID=" & ChartID & ";"
0
 
Malloy1446Author Commented:
Tried that. No luck. Same "The website cannot display the page" message.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
you'll need to send the errors to the browser. this is done within IIS. It will be very hard to diagnose without the exact error
0
 
Malloy1446Author Commented:
I don't have access to the IIS. I can try to contact someone tomorrow.
Does my JOIN look right?
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
it does, can you run it directly in Access by filling in the chartID? if so, we know its something else
0
 
Pawan KumarDatabase ExpertCommented:
I think bracket was the issue. can you please try below..

--

sql2 = "SELECT tblGeneral.ChartID, tblGeneral.Facility, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, " & _
	"tblGeneral.FacName, tblGeneral.CorpName, " & _
	"tblContractJournalsSubs.JournalID, " & _
	"tblContractJournals.Title, tblContractJournals.Cost, tblContractJournals.Type, tblContractJournals.Vendor " & _
		"FROM tblGeneral INNER JOIN tblContractJournalsSubs ON tblGeneral.ChartID= tblContractJournalsSubs.ChartID " & _
		"INNER JOIN tblContractJournals on tblContractJournalsSubs.JournalID = tblContractJournals.JournalID " & _
		"WHERE tblGeneral.ChartID = " & ChartID & ";"
--

Open in new window


Enjoy!!
0
 
Malloy1446Author Commented:
No change.
0
 
Pawan KumarDatabase ExpertCommented:
Can you post the Error pls?
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
I've requested the error message, see above the comments asking to make the change in IIS.

If you can't get to IIS easily,  try putting in error handling right after you execute the sql:

On error resume next
sql = "....."
Set rs = conn.execute sql

If err.state <> 1 then Response.Write err.description
0
 
Malloy1446Author Commented:
All I get is the generic "The Website Cannot display the page."

When I remove the SQL statement the page does display fine. There is another SQL statement connecting to the same DB and that displays correctly. When I add the SQL back in, I get the error.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
Did you try the error handling suggestion?
0
 
Pawan KumarDatabase ExpertCommented:
Try this. can you try the SQL directly on the DB?

sql2 = `SELECT tblGeneral.ChartID, tblGeneral.Facility, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, ` & _
`tblGeneral.FacName, tblGeneral.CorpName, ` & _
`tblContractJournalsSubs.JournalID, ` & _
`tblContractJournals.Title, tblContractJournals.Cost, tblContractJournals.Type, tblContractJournals.Vendor ` & _
`FROM tblGeneral INNER JOIN tblContractJournalsSubs ON tblGeneral.ChartID= tblContractJournalsSubs.ChartID ` & _
`INNER JOIN tblContractJournals on tblContractJournalsSubs.JournalID = tblContractJournals.JournalID ` & _
`WHERE tblGeneral.ChartID = ` & ChartID & `;`

Open in new window

0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
Also did you try running the sql directly in access like I suggested?
0
 
Malloy1446Author Commented:
I pasted the following in Access:

SELECT tblGeneral.ChartID, tblGeneral.Facility, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID,
      tblGeneral.FacName, tblGeneral.CorpName,
      tblContractJournalsSubs.JournalID,
      tblContractJournals.Title, tblContractJournals.Cost, tblContractJournals.Type, tblContractJournals.Vendor ,

            FROM (tblGeneral INNER JOIN tblContractJournalsSubs ON tblGeneral.ChartID= tblContractJournalsSubs.ChartID) ,
            INNER JOIN tblContractJournals on tblContractJournalsSubs.JournalID = tblContractJournals.JournalID
            WHERE tblGeneral.ChartID=112

I get:
"The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect
0
 
Pawan KumarDatabase ExpertCommented:
Got it .. try below directly in Access..

SELECT tblGeneral.ChartID, tblGeneral.Facility, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID,
      tblGeneral.FacName, tblGeneral.CorpName,
      tblContractJournalsSubs.JournalID,
      tblContractJournals.Title, tblContractJournals.Cost, tblContractJournals.Type, tblContractJournals.Vendor 
            FROM tblGeneral , tblContractJournalsSubs , tblContractJournals  WHERE tblGeneral.ChartID= tblContractJournalsSubs.ChartID 
            AND tblContractJournalsSubs.JournalID = tblContractJournals.JournalID
            AND tblGeneral.ChartID=112

Open in new window

0
 
Malloy1446Author Commented:
That works in Access.

What kind of punctuation is used in the code?
0
 
Pawan KumarDatabase ExpertCommented:
Great !! Finally it worked.

punctuation meaning ?  I have used old syntax coding since the new one not working.

Now can you try this with your UI code?
0
 
Malloy1446Author Commented:
This SQL statement is working with the SPECIFIC ChartID coded (         "AND tblGeneral.ChartID=112 "   )


But, I need to use the querystring ChartID which is identified as

     ChartID = Request.querystring("ChartID")
0
 
Malloy1446Author Commented:
CUrrent code:


 
Dim objRS2, sql2
	
sql2 = "SELECT tblGeneral.ChartID, tblGeneral.Facility, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, " & _
       "tblGeneral.FacName, tblGeneral.CorpName, " & _
       "tblContractJournalsSubs.JournalID, " & _
       "tblContractJournals.Title, tblContractJournals.Cost, tblContractJournals.Type, tblContractJournals.Vendor " & _ 
       
	     "FROM tblGeneral, tblContractJournalsSubs, tblContractJournals  WHERE tblGeneral.ChartID= tblContractJournalsSubs.ChartID " & _
         "AND tblContractJournalsSubs.JournalID = tblContractJournals.JournalID " & _
         "AND tblGeneral.ChartID=112 "

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Try this..

Dim objRS2, sql2 , GetValue
      
      GetValue = 112
      
sql2 = "SELECT tblGeneral.ChartID, tblGeneral.Facility, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, " & _
       "tblGeneral.FacName, tblGeneral.CorpName, " & _
       "tblContractJournalsSubs.JournalID, " & _
       "tblContractJournals.Title, tblContractJournals.Cost, tblContractJournals.Type, tblContractJournals.Vendor " & _
       
           "FROM tblGeneral, tblContractJournalsSubs, tblContractJournals  WHERE tblGeneral.ChartID= tblContractJournalsSubs.ChartID " & _
         "AND tblContractJournalsSubs.JournalID = tblContractJournals.JournalID " & _
         "AND tblGeneral.ChartID = " & GetValue
0
 
Malloy1446Author Commented:
No.

Back to
The website cannot display the page
0
 
Pawan KumarDatabase ExpertCommented:
Pls provide the code you are using
0
 
Malloy1446Author Commented:
This is the code which works but is using the hard coding for ChartID=112

Dim objRS2, sql2

sql2 = "SELECT tblGeneral.ChartID, tblGeneral.Facility, tblGeneral.City, tblGeneral.State, tblGeneral.VISNID, " & _
       "tblGeneral.FacName, tblGeneral.CorpName, " & _
       "tblContractJournalsSubs.JournalID, " & _
       "tblContractJournals.Title, tblContractJournals.Cost, tblContractJournals.Type, tblContractJournals.Vendor " & _ 
       
	     "FROM tblGeneral, tblContractJournalsSubs, tblContractJournals  WHERE tblGeneral.ChartID= tblContractJournalsSubs.ChartID " & _
         "AND tblContractJournalsSubs.JournalID = tblContractJournals.JournalID " & _
         "AND tblGeneral.ChartID=112 "


 		If Request.querystring("sort") = "" then
 			sql2 = sql2 & "ORDER BY Title,Type"
 		Else
  			sql2 = sql2 & "ORDER BY " & Request.querystring("sort")
  		End If			
	
	Set objRS2 = Server.CreateObject("ADODB.Recordset")
	objRS2.Open sql2, objConn

Open in new window



I need to use the querystring I am getting from the sign-in page which I have identified on this page with the  

ChartID = Request.querystring("ChartID")

Open in new window

0
 
Malloy1446Author Commented:
GOt it. I reversed the order in the FROM lines, put the ChartID = querystring 1st and it works.

           "FROM tblGeneral, tblContractJournalsSubs, tblContractJournals WHERE tblGeneral.ChartID=" & ChartID & _
         "AND tblGeneral.ChartID= tblContractJournalsSubs.ChartID " & _
         "AND tblContractJournalsSubs.JournalID = tblContractJournals.JournalID "
0
 
Malloy1446Author Commented:
Thank you for sticking with me through this, as usual, learned something new!
0
 
Pawan KumarDatabase ExpertCommented:
Great to see that it works. Well done.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 14
  • 8
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now