Solved

Generate report pulling data (links) from three tables

Posted on 2016-10-06
31
46 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
  • 14
  • 8
  • 6
  • +1
31 Comments
 
LVL 34

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 34

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
 

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 34

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 32

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 32

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 32

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 17

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

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

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 32

Expert Comment

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

Expert Comment

by:Pawan Kumar Khowal
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 32

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 17

Accepted Solution

by:
Pawan Kumar Khowal earned 500 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 17

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now