Solved

MSAccess Database - Combination of multiple fields as one - Array???

Posted on 2016-07-18
17
49 Views
Last Modified: 2016-07-22
I have an MSAccess database (sample data attached). I have 3 columns that I need to display in a report.
Column 1: City
Column 2: fields J1Title, J2Title, J3Title.... through J25Title.
Column 3: Corresponding J1Cost.....
 
THe report would display like this:
 
Location                  Journal                Journal Cost
Dallas                      Chest                    $1500
Dallas                      NEJM                    $500
Dallas                      Science                 $5000
Milwaukee             NEJM                      $500
 
The fields J1Title through J25Title need to somehow be collated and display as one list with their
correcsponding City and cost. All data shares the ChartID as the common denominator.
Maybe an array?  Just not sure how to do this. I've researched and not been able to figure it
out.


<!-- #Include Virtual="ConneJournals.inc" -->
 
 
<%
dim title
title = "INVENTORY - Journals"
%>         
 
 
<html>
 
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 12.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title><%=title%></title>
 
<link rel=stylesheet type="text/css"
 href="/VALNET/CodeReuse/style.css">
 
</head>
 
<body>
 
<h2><%=title%></h2>
 
 
<%
'Create recordset
Dim sql, objRS
 
 
sql = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, " & _
               "tblJournals.J1Title, tblJournals.J1Cost, tblJournals.J2Title, tblJournals.J2Cost, " & _
               "tblJournals.J3Title, tblJournals.J3Cost, tblJournals.J4Title, tblJournals.J4Cost, " & _
               "tblJournals.J5Title, tblJournals.J5Cost, tblJournals.J6Title, tblJournals.J6Cost, " & _
               "tblJournals.J7Title, tblJournals.J7Cost, tblJournals.J8Title, tblJournals.J8Cost, " & _
               "tblJournals.J9Title, tblJournals.J9Cost, tblJournals.J10Title, tblJournals.J10Cost, " & _
               "tblJournals.J11Title, tblJournals.J11Cost, tblJournals.J12Title, tblJournals.J12Cost, " & _
               "tblJournals.J13Title, tblJournals.J13Cost, tblJournals.J14Title, tblJournals.J14Cost, " & _
               "tblJournals.J15Title, tblJournals.J15Cost, tblJournals.J16Title, tblJournals.J16Cost, " & _
               "tblJournals.J17Title, tblJournals.J17Cost, tblJournals.J18Title, tblJournals.J18Cost, " & _
               "tblJournals.J19Title, tblJournals.J19Cost, tblJournals.J20Title, tblJournals.J20Cost, " & _
               "tblJournals.J21Title, tblJournals.J21Cost, tblJournals.J22Title, tblJournals.J22Cost, " & _
               "tblJournals.J23Title, tblJournals.J23Cost, tblJournals.J24Title, tblJournals.J24Cost, " & _
               "tblJournals.J25Title, tblJournals.J25Cost " & _
                "FROM tblGeneral INNER JOIN tblJournals ON tblGeneral.ChartID= tblJournals.ChartID "
 
                               If Request.querystring("sort") = "" then
                                               sql = sql & "ORDER BY City"
                               Else
                                                sql = sql & "ORDER BY " & Request.querystring("sort")
                                End If                                   
               
                Set objRS = Server.CreateObject("ADODB.Recordset")
                objRS.Open sql, objConn
 
 
 
 
 
 
 
 
'First, display the TABLE header info:
 
 
Response.Write "<br><TABLE class='CCTable' cellspacing='0' width='400px' border='1' cellpadding='5'>"
 
Response.Write "<TR background-color:white;'>"
Response.Write "<TH class='CCTable' scope='col' width='150px'><b>&nbsp;&nbsp;</b></th>"
Response.Write "<TH class='CCTable' scope='col' width='150px'><b>Location</b></th>"
Response.Write "<TH class='CCTable' scope='col' width='50px'><b>Title</b></th>"
Response.Write "<TH class='CCTable' scope='col' width='125px'><b>Cost</b></th></tr>"
 
While Not objRS.EOF
 
   'Advance the recordset
   objRS.MoveNext
 
Wend
objRS.Close
Set objRS = Nothing
 
Response.Write "</TABLE></div>" & vbCrLf
 
%>
 
<p>&nbsp;</p>

Open in new window

Journals.mdb
0
Comment
Question by:Malloy1446
  • 8
  • 4
  • 2
  • +2
17 Comments
 
LVL 10

Expert Comment

by:LukeChung-FMS
ID: 41718207
This is where you should design your database in a relational manner.

Rather than 25 title/cost fields, you should have another table that has a City Field (or ID that links to a City name), Journal Name, and Journal Cost.

And your parent table would be your city list (CityID as the primary key, and City Name)

Create a query that links the two tables.

Then you can support an infinite number of journals in each city without ever having to modify your query.

If you're converting it to HTML, you'd just work through the list and add a new row for each journal until you run out of rows in you query.

Hope this helps.
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 41719329
You may also want to have a look at the STUFF() function in sql server. It'll allow you to concatenate multiple fields into one field.

another example (I had the same issue awhile back)
0
 

Author Comment

by:Malloy1446
ID: 41719352
LukeChung-FMS: The idea of having an infinite number of titles is definitely something I could use. I don't understand what additional table I need. My primary table is tblGeneral which includes fields: ChartID and City.
0
 
LVL 10

Expert Comment

by:LukeChung-FMS
ID: 41719420
Your main table should have a Primary Key (autonumber) called something like ChartCityID -- all tables should have a meaningless Primary Key. An autonumber field type gets incremented every time a new record is added. Then the other data fields (ChartID and CityName).

Your second table would have it's own meaningless Primary Key like CityJournalID. Then you'd have a field for ChartCityID to match what's in your main table, then a field for JournalName and JournalCost. That makes it a one-to-many relationship.

You can then run a query that links the two tables on their shared ChartCityID field, select the ChartID and CityName from the first table and the Journal info from the second. It'll look like the grid your comfortable with, but the data is very compact. If you want to change the CityName, you change it in one place and it's refreshed on all the rows in your query.

For data entry, you can create a form for the master table with a subform for the second table so that as you move through your master records, the corresponding journals appear.

Hope this helps.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41719525
I modified the tables in your database to show how the m-m relationship works.  I didn't populate the data completely.  I also attached a working sample of a m-m so you can see how forms and queries work with the junction table.
ManyToMany.zip
JournalsUpdated.mdb
0
 

Author Comment

by:Malloy1446
ID: 41719917
Pat Hartman: The method you are describing seems like a better way of setting up the database. Although the relationship is totally new. I did have a form created (code attached). Not sure how the relationship will be set up to use this form. You mentioned that this will allow for infinite entries. I can't figure out how you would create a form to enter or data or allow for infinite entries. sorry I am not an expert and this relationship is new to me. Your help is appreciated!
0
 

Author Comment

by:Malloy1446
ID: 41719918
Here is my form code:

<%
ChartID = Request.querystring("ChartID")

	'Get values for form below

	dim facility, VISNID, city, state
	sql = "SELECT * FROM tblGeneral WHERE ChartID=" & ChartID & ";"
	set objrec = objConn.Execute(sql)

		City = objrec("City")
		State = objrec("State")
		Facility = objrec("Facility")
		VISNID = objrec("VISNID")
	set objRec = nothing
	

'Now get the data for the form below

sql = "SELECT * FROM tblJournals WHERE ChartID = " & ChartID
Set objrec = Server.CreateObject("ADODB.Recordset")
objrec.Open sql, objConn

'Following code creates a station entry when none exists
'create variables and give them values from recordset fields
'Then use variables in form code below, rather than recordset fields

If not objrec.eof then


	J1Title = objrec("J1Title")
	J1Type = objrec("J1Type")
	J1Cost = objrec("J1Cost")

	J2Title = objrec("J2Title")
	J2Type = objrec("J2Type")
	J2Cost = objrec("J2Cost")
		
	J3Title = objrec("J3Title")
	J3Type = objrec("J3Type")
	J3Cost = objrec("J3Cost")
		
	J4Title = objrec("J4Title")
	J4Type = objrec("J4Type")
	J4Cost = objrec("J4Cost")
		
	J5Title = objrec("J5Title")
	J5Type = objrec("J5Type")
	J5Cost = objrec("J5Cost")
		
	J6Title = objrec("J6Title")
	J6Type = objrec("J6Type")
	J6Cost = objrec("J6Cost")
		
	J7Title = objrec("J7Title")
	J7Type = objrec("J7Type")
	J7Cost = objrec("J7Cost")

	J8Title = objrec("J8Title")
	J8Type = objrec("J8Type")
	J8Cost = objrec("J8Cost")
		
	J9Title = objrec("J9Title")
	J9Type = objrec("J9Type")
	J9Cost = objrec("J9Cost")
		
	J10Title = objrec("J10Title")
	J10Type = objrec("J10Type")
	J10Cost = objrec("J10Cost")
		
	J11Title = objrec("J11Title")
	J11Type = objrec("J11Type")
	J11Cost = objrec("J11Cost")
		
	J12Title = objrec("J12Title")
	J12Type = objrec("J12Type")
	J12Cost = objrec("J12Cost")
		
	J13Title = objrec("J13Title")
	J13Type = objrec("J13Type")
	J13Cost = objrec("J13Cost")
		
	J14Title = objrec("J14Title")
	J14Type = objrec("J14Type")
	J14Cost = objrec("J14Cost")
		
	J15Title = objrec("J15Title")
	J15Type = objrec("J15Type")
	J15Cost = objrec("J15Cost")
		
	J16Title = objrec("J16Title")
	J16Type = objrec("J16Type")
	J16Cost = objrec("J16Cost")
		
	J17Title = objrec("J17Title")
	J17Type = objrec("J17Type")
	J17Cost = objrec("J17Cost")
		
	J18Title = objrec("J18Title")
	J18Type = objrec("J18Type")
	J18Cost = objrec("J18Cost")
		
	J19Title = objrec("J19Title")
	J19Type = objrec("J19Type")
	J19Cost = objrec("J19Cost")
		
	J20Title = objrec("J20Title")
	J20Type = objrec("J20Type")
	J20Cost = objrec("J20Cost")
		
	J21Title = objrec("J21Title")
	J21Type = objrec("J21Type")
	J21Cost = objrec("J21Cost")
		
	J22Title = objrec("J22Title")
	J22Type = objrec("J22Type")
	J22Cost = objrec("J22Cost")
		
	J23Title = objrec("J23Title")
	J23Type = objrec("J23Type")
	J23Cost = objrec("J23Cost")

	J24Title = objrec("J24Title")
	J24Type = objrec("J24Type")
	J24Cost = objrec("J24Cost")
		
	J25Title = objrec("J25Title")
	J25Type = objrec("J25Type")
	J25Cost = objrec("J25Cost")
	End if

%>


<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title><%=title%></title>
</head>

<body>

<h2><font face="Arial">
	<%=title%> - <font color="#FF0000"><%=City%>, <%=State%> </font>
</h2>

<p><b>
	Facility Number:<font color="#FF0000"> <%=Facility%></font><br>
	VISN: <font color="#FF0000"> <%=VISNID%></font><br>
	Site: <font color="#FF0000"> <%=City%>, <%=State%></font>
</b></p>



<form method="POST" action="End.asp">
<font face="Arial" size="2">



<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J1Title" size="100" value="<%=J1Title%>"><br>
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="number" name="J1Cost" size="10" value="<%=J1Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J1Type" value="Print" <%If J1Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J1Type" value="Online" <%If J1Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J1Type" value="Print and Online" <%If J1Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J2Title" size="100" value="<%=J2Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J2Cost" size="10" value="<%=J2Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J2Type" value="Print" <%If J2Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J2Type" value="Online" <%If J2Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J2Type" value="Print and Online" <%If J2Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J3Title" size="100" value="<%=J3Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J3Cost" size="10" value="<%=J3Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J3Type" value="Print" <%If J3Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J3Type" value="Online" <%If J3Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J3Type" value="Print and Online" <%If J3Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J4Title" size="100" value="<%=J4Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J4Cost" size="10" value="<%=J4Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J4Type" value="Print" <%If J4Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J4Type" value="Online" <%If J4Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J4Type" value="Print and Online" <%If J4Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J5Title" size="100" value="<%=J5Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J5Cost" size="10" value="<%=J5Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J5Type" value="Print" <%If J5Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J5Type" value="Online" <%If J5Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J5Type" value="Print and Online" <%If J5Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J6Title" size="100" value="<%=J6Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J6Cost" size="10" value="<%=J6Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J6Type" value="Print" <%If J6Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J6Type" value="Online" <%If J6Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J6Type" value="Print and Online" <%If J6Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J7Title" size="100" value="<%=J7Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J7Cost" size="10" value="<%=J7Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J7Type" value="Print" <%If J7Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J7Type" value="Online" <%If J7Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J7Type" value="Print and Online" <%If J7Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J8Title" size="100" value="<%=J8Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J8Cost" size="10" value="<%=J8Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J8Type" value="Print" <%If J8Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J8Type" value="Online" <%If J8Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J8Type" value="Print and Online" <%If J8Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J9Title" size="100" value="<%=J9Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J9Cost" size="10" value="<%=J9Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J9Type" value="Print" <%If J9Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J9Type" value="Online" <%If J9Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J9Type" value="Print and Online" <%If J9Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J10Title" size="100" value="<%=J10Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J10Cost" size="10" value="<%=J10Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J10Type" value="Print" <%If J10Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J10Type" value="Online" <%If J10Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J10Type" value="Print and Online" <%If J10Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J11Title" size="100" value="<%=J11Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J11Cost" size="10" value="<%=J11Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J11Type" value="Print" <%If J11Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J11Type" value="Online" <%If J11Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J11Type" value="Print and Online" <%If J11Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J12Title" size="100" value="<%=J12Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J12Cost" size="10" value="<%=J12Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J12Type" value="Print" <%If J12Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J12Type" value="Online" <%If J12Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J12Type" value="Print and Online" <%If J12Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J13Title" size="100" value="<%=J13Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J13Cost" size="10" value="<%=J13Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J13Type" value="Print" <%If J13Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J13Type" value="Online" <%If J13Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J13Type" value="Print and Online" <%If J13Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J14Title" size="100" value="<%=J14Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J14Cost" size="10" value="<%=J14Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J14Type" value="Print" <%If J14Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J14Type" value="Online" <%If J14Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J14Type" value="Print and Online" <%If J14Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J15Title" size="100" value="<%=J15Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J15Cost" size="10" value="<%=J15Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J15Type" value="Print" <%If J15Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J15Type" value="Online" <%If J15Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J15Type" value="Print and Online" <%If J15Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J16Title" size="100" value="<%=J16Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J16Cost" size="10" value="<%=J16Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J16Type" value="Print" <%If J16Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J16Type" value="Online" <%If J16Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J16Type" value="Print and Online" <%If J16Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J17Title" size="100" value="<%=J17Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J17Cost" size="10" value="<%=J17Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J17Type" value="Print" <%If J17Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J17Type" value="Online" <%If J17Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J17Type" value="Print and Online" <%If J17Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J18Title" size="100" value="<%=J18Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J18Cost" size="10" value="<%=J18Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J18Type" value="Print" <%If J18Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J18Type" value="Online" <%If J18Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J18Type" value="Print and Online" <%If J18Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J19Title" size="100" value="<%=J19Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J19Cost" size="10" value="<%=J19Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J19Type" value="Print" <%If J19Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J19Type" value="Online" <%If J19Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J19Type" value="Print and Online" <%If J19Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J20Title" size="100" value="<%=J20Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J20Cost" size="10" value="<%=J20Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J20Type" value="Print" <%If J20Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J20Type" value="Online" <%If J20Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J20Type" value="Print and Online" <%If J20Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J21Title" size="100" value="<%=J21Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J21Cost" size="10" value="<%=J21Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J21Type" value="Print" <%If J21Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J21Type" value="Online" <%If J21Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J21Type" value="Print and Online" <%If J21Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J22Title" size="100" value="<%=J22Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J22Cost" size="10" value="<%=J22Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J22Type" value="Print" <%If J22Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J22Type" value="Online" <%If J22Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J22Type" value="Print and Online" <%If J22Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J23Title" size="100" value="<%=J23Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J23Cost" size="10" value="<%=J23Cost%>">
&nbsp;&nbsp;`
<b>Type of Subscription:</b> 
	<input type="radio" name="J23Type" value="Print" <%If J23Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J23Type" value="Online" <%If J23Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J23Type" value="Print and Online" <%If J23Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J24Title" size="100" value="<%=J24Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J24Cost" size="10" value="<%=J24Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J24Type" value="Print" <%If J24Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J24Type" value="Online" <%If J24Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J24Type" value="Print and Online" <%If J24Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->
<hr><br>
<b>Journal Title:</b>  		
	<input type="text" name="J25Title" size="100" value="<%=J25Title%>"><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<b>Cost:  $</b>
	<input type="text" name="J25Cost" size="10" value="<%=J25Cost%>">
&nbsp;&nbsp;
<b>Type of Subscription:</b> 
	<input type="radio" name="J25Type" value="Print" <%If J25Type = "Print" then response.write "checked" End If%>>Print&nbsp;&nbsp;
	<input type="radio" name="J25Type" value="Online" <%If J25Type = "Online" then response.write "checked" End If%>>Online&nbsp;&nbsp;
	<input type="radio" name="J25Type" value="Print and Online" <%If J25Type = "Print and Online" then response.write "checked" End If%>>Print and Online&nbsp;&nbsp;<p>


<!-- ********************************************************* -->

<p>

<input type="submit" value="Continue">
<input type="hidden" name="ChartID" value="<%=ChartID%>">



</form>

<% 
'End If
	objrec.close
	set objrec=nothing
%>

</body>

</html>

Open in new window

0
 
LVL 6

Expert Comment

by:bcnagel
ID: 41720910
There have been good suggestions regarding a database redesign, but if you just need a query to "unpivot" your current structure into the three column view that you described, you could use something like this:

select tg.City, der.Title, der.Cost
	from (
		select ChartID, J1Title as Title, J1Cost as Cost from tblJournals union
		select ChartID, J2Title, J2Cost from tblJournals union
		select ChartID, J3TItle, J3Cost from tblJournals union
		select ChartID, J4Title, J4Cost from tblJournals union
		select ChartID, J5Title, J5Cost from tblJournals union
		select ChartID, J6Title, J6Cost from tblJournals union
		select ChartID, J7Title, J7Cost from tblJournals union
		select ChartID, J8Title, J8Cost from tblJournals union
		select ChartID, J9Title, J9Cost from tblJournals union
		select ChartID, J10Title, J10Cost from tblJournals union
		select ChartID, J11Title, J11Cost from tblJournals union
		select ChartID, J12Title, J12Cost from tblJournals union
		select ChartID, J13Title, J13Cost from tblJournals union
		select ChartID, J14Title, J14Cost from tblJournals union
		select ChartID, J15Title, J15Cost from tblJournals union
		select ChartID, J16Title, J16Cost from tblJournals union
		select ChartID, J17Title, J17Cost from tblJournals union
		select ChartID, J18Title, J18Cost from tblJournals union
		select ChartID, J19Title, J19Cost from tblJournals union
		select ChartID, J20Title, J20Cost from tblJournals union
		select ChartID, J21Title, J21Cost from tblJournals union
		select ChartID, J22Title, J22Cost from tblJournals union
		select ChartID, J23Title, J23Cost from tblJournals union
		select ChartID, J24Title, J24Cost from tblJournals union
		select ChartID, J25Title, J25Cost from tblJournals
	) der
	inner join tblGeneral as tg
	on tg.ChartID = der.ChartID

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Expert Comment

by:PatHartman
ID: 41721263
That's why I attached a working sample.

The key is to use a subform for the journals.  The subform is in continuous view and can have an infinite number or rows.  Please take a look at the sample I attached.  There are other m-m examples.  Northwinds also has one.  Northwinds is an order entry application and in an order entry application, the order details is the junction table.  It links the order with the products that were ordered and usually includes data specific to the junction such as the price for the product on a particular order since the standard price, which lives with the product, might be discounted for a particular customer order.

Of course ASP isn't Access so I'm not sure what ASP calls the equivalent of a subform.  Since I have seen web pages that use the continuous "subform" concept, I'm sure there is a way.
0
 

Author Comment

by:Malloy1446
ID: 41725246
I'm missing something with this union. I don't see where the tblGeneral is linked to the city. Can you take a look at see where my code is wrong?

<%
'Create recordset
 Dim sql, objRS


 sql = "SELECT tg.City, der.Title, der.cost
 from (
  	"select ChartID, J1Title, J1Cost from tblJournals union, " & _
 	"select ChartID, J2Title, J2Cost from tblJournals union, " & _
 	"select ChartID, J3Title, J3Cost from tblJournals union, " & _
 	"select ChartID, J4Title, J4Cost from tblJournals union, " & _
 	"select ChartID, J5Title, J5Cost from tblJournals union, " & _ 
 	"select ChartID, J6Title, J6Cost from tblJournals union, " & _
 	"select ChartID, J7Title, J7Cost from tblJournals union, " & _ 
 	"select ChartID, J8Title, J8Cost from tblJournals union, " & _
 	"select ChartID, J9Title, J9Cost from tblJournals union, " & _ 
 	"select ChartID, J10Title, J10Cost from tblJournals union, " & _
 	"select ChartID, J11Title, J11Cost from tblJournals union, " & _ 
 	"select ChartID, J12Title, J12Cost from tblJournals union, " & _
 	"select ChartID, J13Title, J13Cost from tblJournals union, " & _ 
 	"select ChartID, J14Title, J14Cost from tblJournals union, " & _
 	"select ChartID, J15Title, J15Cost from tblJournals union, " & _
 	"select ChartID, J16Title, J16Cost from tblJournals union, " & _
 	"select ChartID, J17Title, J17Cost from tblJournals union, " & _
 	"select ChartID, J18Title, J18Cost from tblJournals union, " & _
 	"select ChartID, J19Title, J19Cost from tblJournals union, " & _
 	"select ChartID, J20Title, J20Cost from tblJournals union, " & _
 	"select ChartID, J21Title, J21Cost from tblJournals union, " & _
 	"select ChartID, J22Title, J22Cost from tblJournals union, " & _
 	"select ChartID, J23Title, J23Cost from tblJournals union, " & _ 
 	"select ChartID, J24Title, J24Cost from tblJournals union, " & _
 	"select ChartID, J25Title, J25Cost from tblJournals " & _ 	
 	
 	") der " & _
	"inner join tblGeneral as tg " & _
	"on tg.ChartID=der.ChartID "
	
	
 		If Request.querystring("sort") = "" then
 			sql = sql & "ORDER BY Title"
 		Else
  			sql = sql & "ORDER BY " & Request.querystring("sort")
  		End If			
	
	Set objRS = Server.CreateObject("ADODB.Recordset")
	objRS.Open sql, objConn


'First, display the TABLE header info:


Response.Write "<br><TABLE class='CCTable' cellspacing='0' width='400px' border='1' cellpadding='5'>"

Response.Write "<TR background-color:white;'>"
Response.Write "<TH class='CCTable' scope='col' width='150px'><b>&nbsp;&nbsp;</b></th>"
Response.Write "<TH class='CCTable' scope='col' width='50px'><b>Title</b></th>"
Response.Write "<TH class='CCTable' scope='col' width='125px'><b>Cost</b></th></tr>"
Response.Write "<TH class='CCTable' scope='col' width='150px'><b>Location</b></th>"

While Not objRS.EOF
counter = counter + 1
	Response.Write "<TR><TD class='GeneralTable' scope='row' width='25px'>" & counter
	Response.Write "<TD class='GeneralTable' scope='row' width='400px'>" & Title & "&nbsp;</TD>" & VbCrLf
	Response.Write "<TD class='GeneralTable' scope='row' width='150px'>$" & Cost & ", " & objRS("State") & "</TD>" & VbCrLf
	Response.Write "<TD class='GeneralTable' scope='row' width='100px'>" & City & "&nbsp;</TD>" & VbCrLf
		
   'Advance the recordset
   objRS.MoveNext

Wend


objRS.Close
Set objRS = Nothing

Response.Write "</TABLE></div>" & vbCrLf

Open in new window

0
 
LVL 6

Expert Comment

by:bcnagel
ID: 41725267
Hello.

In your Journals database example, tblGeneral just has a field called City--there's no need for a link--it's right in the table. Does that make sense?
0
 

Author Comment

by:Malloy1446
ID: 41725289
Yes, but I thought I needed to identify the field in my SQL statement.
With the code above, I am getting a "The website cannot display the page" message.
Checked tbl name and all fields so something else is wrong with the sql statement, not sure what....
0
 
LVL 6

Expert Comment

by:bcnagel
ID: 41725303
In your SQL, you have the line "SELECT tg.City, der.Title, der.cost..." That's where you are identifying the City.

Try running the query I posted in Access--it will work. If it doesn't, post the error, and we can fix it.
0
 

Author Comment

by:Malloy1446
ID: 41725306
Sorry, I don't see your ACCESS post.
0
 
LVL 6

Accepted Solution

by:
bcnagel earned 500 total points
ID: 41725312
No, I mean run this query...

select tg.City, der.Title, der.Cost
	from (
		select ChartID, J1Title as Title, J1Cost as Cost from tblJournals union
		select ChartID, J2Title, J2Cost from tblJournals union
		select ChartID, J3TItle, J3Cost from tblJournals union
		select ChartID, J4Title, J4Cost from tblJournals union
		select ChartID, J5Title, J5Cost from tblJournals union
		select ChartID, J6Title, J6Cost from tblJournals union
		select ChartID, J7Title, J7Cost from tblJournals union
		select ChartID, J8Title, J8Cost from tblJournals union
		select ChartID, J9Title, J9Cost from tblJournals union
		select ChartID, J10Title, J10Cost from tblJournals union
		select ChartID, J11Title, J11Cost from tblJournals union
		select ChartID, J12Title, J12Cost from tblJournals union
		select ChartID, J13Title, J13Cost from tblJournals union
		select ChartID, J14Title, J14Cost from tblJournals union
		select ChartID, J15Title, J15Cost from tblJournals union
		select ChartID, J16Title, J16Cost from tblJournals union
		select ChartID, J17Title, J17Cost from tblJournals union
		select ChartID, J18Title, J18Cost from tblJournals union
		select ChartID, J19Title, J19Cost from tblJournals union
		select ChartID, J20Title, J20Cost from tblJournals union
		select ChartID, J21Title, J21Cost from tblJournals union
		select ChartID, J22Title, J22Cost from tblJournals union
		select ChartID, J23Title, J23Cost from tblJournals union
		select ChartID, J24Title, J24Cost from tblJournals union
		select ChartID, J25Title, J25Cost from tblJournals
	) der
	inner join tblGeneral as tg
	on tg.ChartID = der.ChartID

Open in new window


... inside the Journals.mdb file that you posted. You'll see that the query works. Right?
0
 

Author Comment

by:Malloy1446
ID: 41725330
Right!  SO there is something wrong with syntax in my code!
0
 

Author Closing Comment

by:Malloy1446
ID: 41725446
Thank you!
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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

757 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