Using HTML linked tables and Queries Access 2010

[reenieh]

Hi, I have developed an access database using HTML linked tables (so my hyperlinks from excel show in Access). The tables are linked to html documents on my computer as well as to excel documents. This is so I can update only one file and they all update. I have started to create my first query and my problems begin:
1. Using two tables, and a simple three column query, I cannot get the hyperlinks to show properly. If I use "Group", they are not shown as a link, but as text. When I use "First", one column shows as a hyperlink the other column shows as a hyperlink, goggly goup that is not linked to anything.


2. If I combine linked excel tables and linked html tables in one database and try and query them. I get a this message "the sql statement could not be executed because it contains ambiguous outer joins....." even if i am only using two tables and one column from each of the tables.

[Dal Jeanis]

Cool goggly links! I want to know how to get that apple-wearing-a-beret character at the end of Accumap's record!

Please post 1) the relevant layout of each table, with field names and formats 2) the SQL for the "ambiguous outer join"

[reenieh]

Hi Dal,

For 1) I have several columns on each of the tables, more than what is shown and I have indicated when I imported the htm table, all the columns that had hyperlinks. For all the others, i just left as a default of text. There are 4 different tables linked together in this query above.

Item 2 is no longer valid as I cannot make it happen again. It must have been the server issue, as the server went down later on that day and I have not been able to replicate the problem today.

[Dal Jeanis]

Okay, if you describe the problem again, as it exists now, and give the relevant table layouts and the SQL from the query, then we can try to figure it out.

Without explicit details, our input isn't going to add much value.

[reenieh]

Hi Dal, my problem is laid out in Section 1 above in both answers I have given you. If I have not given you enough information, as in the "relevant table layouts" then I don't know what you are asking me - I answered what I understood to be the question in my second response to you. This is the sql of the query:

SELECT [Confirmed Apps].[Application Name], [Confirmed Apps].[Vendor Technical Requirements], [Mashzone 2].[L-Link 1], [Licensing Apps].[SCM Quote], [Mashzone 1].[L-Link 1]
FROM (([Confirmed Apps] LEFT JOIN [Mashzone 2] ON [Confirmed Apps].[Application Name] = [Mashzone 2].Application) LEFT JOIN [Licensing Apps] ON [Confirmed Apps].[Application Name] = [Licensing Apps].[Application Name & Version]) INNER JOIN [Mashzone 1] ON [Confirmed Apps].[Application Name] = [Mashzone 1].Application
GROUP BY [Confirmed Apps].[Application Name], [Confirmed Apps].[Vendor Technical Requirements], [Mashzone 2].[L-Link 1], [Licensing Apps].[SCM Quote], [Mashzone 1].[L-Link 1];

[Dal Jeanis]

Remember, I have no idea what your application is. Here's how I parsed your SQL:

SELECT 
   [Confirmed Apps].[Application Name], 
   [Confirmed Apps].[Vendor Technical Requirements], 
   [Mashzone 2].[L-Link 1], 
   [Licensing Apps].[SCM Quote], 
   [Mashzone 1].[L-Link 1]
FROM 
   (  (  [Confirmed Apps] 
         LEFT JOIN 
         [Mashzone 2] 
         ON [Confirmed Apps].[Application Name] = [Mashzone 2].Application
      ) 
      LEFT JOIN 
      [Licensing Apps] 
      ON [Confirmed Apps].[Application Name] = [Licensing Apps].[Application Name & Version]
   ) 
   INNER JOIN 
   [Mashzone 1] 
   ON [Confirmed Apps].[Application Name] = [Mashzone 1].Application
GROUP BY 
   [Confirmed Apps].[Application Name], 
   [Confirmed Apps].[Vendor Technical Requirements], 
   [Mashzone 2].[L-Link 1], 
   [Licensing Apps].[SCM Quote], 
   [Mashzone 1].[L-Link 1];

Given your description of the issue, your problem is very likely to be in how you created the tables you call [MASHZONE 1] and [MASHZONE 2], but I don't even have a description of those tables.

I do note that you're using GROUP BY in a way that is likely to be unnecessary. Use the "FIRST" keyword if it is possible to get more than one record on a join and they will all have the same value. Also, if you are using a LEFT JOIN, be sure to account for records that have no associated record in the right table - usually the NZ function is useful here.

I'd have to know the relationship between the MASHZONE tables, the [Confirmed Apps] table and the [Licensing Apps] table to code this right, but here's a quick-and-bad SQL query assuming that there is only one [Vendor Technical Requirements] per [Application Name] in the [Confirmed Apps] table, and that there may be multiple MASHZONE records of each type for one App.


By the way, most of us have found that it simplifies our lives immensely, and makes our code easier to read, when we use naming conventions that avoid spaces in the middle of table and variable names. In the next sample, I've aliased the tables to avoid typing all those unnecessary square braces.

SELECT 
   tCA.[Application Name]                     AS AppName, 
   FIRST(tCA.[Vendor Technical Requirements]) AS AppReqmts, 
   NZ(FIRST(tLA.[SCM Quote]))                 AS SCMQuote, 
   NZ(tM2.[L-Link 1]),"")                     AS TM2Link, 
   NZ(tM1.[L-Link 1],"")                      AS TM1Link 
FROM 
   (  (  [Confirmed Apps]                     AS tCA
         LEFT JOIN 
         [Mashzone 2]                         AS tM2 
         ON tCA.[Application Name] = tM2.Application
      ) 
      LEFT JOIN 
      [Licensing Apps]                        AS tLA
      ON tCA.[Application Name] = tLA.[Application Name & Version]
   ) 
   INNER JOIN 
   [Mashzone 1]                               AS tM1
   ON tCA.[Application Name] = tM1.Application
GROUP BY 
   tCA.[Application Name],
   NZ(tM2.[L-Link 1]),""),  
   NZ(tM1.[L-Link 1]),"")

[reenieh]

Hi Dal, sorry if I was not clear in my first comment. If I use "group", the records appear as a hyperlink but gobbly goop, if I use "first", the hyperlinks turn into text and do not link to anything. I am really thinking that this is an issue because I am using excel tables, linked to html tables, linked to this access data base and has nothing to do with my query. I have enclosed the sql for the corrected query (sorry I've been playing with it since i wrote the first note and had some relationships out of wack). I do not know why you are sending me sql, as I do not write my queries or anything else in sql. If Access cannot do what I need it to I might use a sql statement I got off the internet, but I don't know sql so I don't understand what you are sending me.

It is not about the query working, in so far as the records showing up, it is the state of the quality of the html, and from what i understand in order to have html show up in reports generated by Access, I need to link to an html record (or have html stand alone tables). I do not know what you need as far as "a description of the tables". This is a linked table, with the exception of the hyperlinked columns in each of the tables, all columns are imported (linked) as text fields and the hyperlink columns are imported (linked) as hyperlink columns.

I have attached the outcome of "first"ing my columns as well as the sql for this query and a snapshot of the "combined" table which is typical of all the tables.









SELECT [Confirmed Apps].[Application Name], First([Confirmed Apps].[Vendor Technical Requirements]) AS [FirstOfVendor Technical Requirements], First([Mashzone 2].[L-Link 1]) AS [FirstOfL-Link 1], First([Licensing Apps].[SCM Quote]) AS [FirstOfSCM Quote], First([Mashzone 1].[L-Link 1]) AS [FirstOfL-Link 11]
FROM (([Confirmed Apps] LEFT JOIN [Mashzone 2] ON [Confirmed Apps].[Application Name] = [Mashzone 2].Application) LEFT JOIN [Licensing Apps] ON [Confirmed Apps].[Application Name] = [Licensing Apps].[Application Name & Version]) LEFT JOIN [Mashzone 1] ON [Confirmed Apps].[Application Name] = [Mashzone 1].Application
GROUP BY [Confirmed Apps].[Application Name];