?
Solved

Generate report pulling data (links) from three tables

Posted on 2016-10-06
31
Medium Priority
?
72 Views
Last Modified: 2016-10-06
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
Comment
Question by:Malloy1446
[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
  • 14
  • 8
  • 6
  • +1
31 Comments
 
LVL 38

Expert Comment

by:PatHartman
ID: 41832633
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
 

Author Comment

by:Malloy1446
ID: 41832728
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
 
LVL 38

Expert Comment

by:PatHartman
ID: 41832730
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Malloy1446
ID: 41832768
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
 
LVL 38

Expert Comment

by:PatHartman
ID: 41832816
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
 
LVL 33

Expert Comment

by:Big Monty
ID: 41832852
try specifying which tables chartID you want to use. change your where clause from

"WHERE ChartID=" & ChartID & ";"

to

"WHERE tblGeneral.ChartID=" & ChartID & ";"
0
 

Author Comment

by:Malloy1446
ID: 41832861
Tried that. No luck. Same "The website cannot display the page" message.
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 41832872
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
 

Author Comment

by:Malloy1446
ID: 41832876
I don't have access to the IIS. I can try to contact someone tomorrow.
Does my JOIN look right?
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 41832878
it does, can you run it directly in Access by filling in the chartID? if so, we know its something else
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41832903
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
 

Author Comment

by:Malloy1446
ID: 41832912
No change.
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41832916
Can you post the Error pls?
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 41832923
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
 

Author Comment

by:Malloy1446
ID: 41832925
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
 
LVL 33

Expert Comment

by:Big Monty
ID: 41832928
Did you try the error handling suggestion?
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41832938
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
 
LVL 33

Expert Comment

by:Big Monty
ID: 41832939
Also did you try running the sql directly in access like I suggested?
0
 

Author Comment

by:Malloy1446
ID: 41832951
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
 
LVL 29

Accepted Solution

by:
Pawan Kumar earned 2000 total points
ID: 41832955
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
 

Author Comment

by:Malloy1446
ID: 41832961
That works in Access.

What kind of punctuation is used in the code?
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41832962
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
 

Author Comment

by:Malloy1446
ID: 41832975
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
 

Author Comment

by:Malloy1446
ID: 41832976
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
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41832978
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
 

Author Comment

by:Malloy1446
ID: 41832980
No.

Back to
The website cannot display the page
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41832981
Pls provide the code you are using
0
 

Author Comment

by:Malloy1446
ID: 41832987
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
 

Author Comment

by:Malloy1446
ID: 41832998
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
 

Author Closing Comment

by:Malloy1446
ID: 41832999
Thank you for sticking with me through this, as usual, learned something new!
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41833001
Great to see that it works. Well done.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

765 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