How do I display two entries under separate fields?

LacieMay · November 17th, 2016, 07:18 PM

I need to display data from two separate fields in a .mdb file, and I can't envision how this is done. I'd like it to display the data like this:
https://i.stack.imgur.com/rk5Xa.jpg

I set my access database like this:
https://s16.postimg.org/o8nmjucph/411_a.jpg

I've already created a page for this and plugged in some of the info, but I can't figure out how to display multiple fields.

I thought this would work:

Code:

<% do until myResults.EOF %>
               <tr>
                 <td><% response.write myResults.Fields.Item("categories").Value %></td>
                 <td><% response.write myResults.Fields.Item("salary").Value %></td>
                 <td><% response.write myResults.Fields.Item("respondents").Value %></td>
               </tr>
               <% myResults.MoveNext
               loop %>

...but this shows all of the information in the database. For this example I just need the ones under "byBusinessSector" to show in this table.

I think I may have built the database wrong. Can .asp pages only read one data cell at a time? I'm not sure. Help!

mat41 · November 17th, 2016, 10:22 PM

its all about the query, this is the bit that you have not shown us. Yes it very likely that I would have built the database different. e.g There is a very good chance I would have normalized section and categories....anyhow to work with what you have I would:

At the moment association is the only category that is related to byBusinessSector, you need to update the section values for records 2-6 to create this relationship, the you may conditionally select based on this

Code:

 
'update the table so you can use the where clause in the select below
sql = "UPDATE allTitles set section='byBusinessSector' where ID < 7;"
myConnection.Execute(sql)

'now after executing the above update just select certain records 
sql = "SELECT categories, salary, respondents from allTitles where section = 'byBusinessSector';"
set myResults = myConnection.Execute(sql)
<% if not myResults.eof then
       do until myResults.EOF %>
           <tr>
            <td><%= trim(myResults("categories")) %></td>
            <td><%= trim(myResults("salary")) %></td>
            <td><%= trim(myResults("respondents")) %></td>
            </tr>
<%          myResults.MoveNext
        loop 
   else
       response.write "No records found"
   end if %>

LacieMay · November 21st, 2016, 05:19 PM

Sorry! That would help.

I have it as:

Code:

Set myResults = myConnection.Execute("SELECT * FROM allTitles")

As you may have guessed, I know very little about classic asp!

mat41 · November 21st, 2016, 05:31 PM

I am surprised that you could not take what I gave you and use it. Which part of my response do you not understand?

Now that I can see your connection object and the table name I have edited my post above. It should simply be copy/paste and the run. After you run it check that the UPDATE statement has fired, if so remove it from your code.