Master Detail Page..Help!

[hdgirl]

Hi

this is my first web application and i am finding it very difficult.

i am trying to create a master detail page with data that is linked from 3 tables in an access database. i can set it up with the data from the 1 table but as soon as i start to insert from 2 it all go's to pot, does anyone have any ideas on how to do this please.

thanks

c


CJB

 

[steven290]

post your code

 

[hdgirl]

It's a lot - the two tables are Complaint and Customer and RefNo is the PK / AN in complaint and the FK in customer

thanks for responding


<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/connMAC.asp" -->
<%
Dim rsComplaint
Dim rsComplaint_numRows

Set rsComplaint = Server.CreateObject("ADODB.Recordset")
rsComplaint.ActiveConnection = MM_connMAC_STRING
rsComplaint.Source = "SELECT * FROM tblComplaint"
rsComplaint.CursorType = 0
rsComplaint.CursorLocation = 2
rsComplaint.LockType = 1
rsComplaint.Open()

rsComplaint_numRows = 0
%>
<%
Dim rsCustomer
Dim rsCustomer_numRows

Set rsCustomer = Server.CreateObject("ADODB.Recordset")
rsCustomer.ActiveConnection = MM_connMAC_STRING
rsCustomer.Source = "SELECT * FROM tblCustomer"
rsCustomer.CursorType = 0
rsCustomer.CursorLocation = 2
rsCustomer.LockType = 1
rsCustomer.Open()

rsCustomer_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = 10
Repeat1__index = 0
rsComplaint_numRows = rsComplaint_numRows + Repeat1__numRows
%>
<%
' *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

Dim rsComplaint_total
Dim rsComplaint_first
Dim rsComplaint_last

' set the record count
rsComplaint_total = rsComplaint.RecordCount

' set the number of rows displayed on this page
If (rsComplaint_numRows < 0) Then
rsComplaint_numRows = rsComplaint_total
Elseif (rsComplaint_numRows = 0) Then
rsComplaint_numRows = 1
End If

' set the first and last displayed record
rsComplaint_first = 1
rsComplaint_last = rsComplaint_first + rsComplaint_numRows - 1

' if we have the correct record count, check the other stats
If (rsComplaint_total <> -1) Then
If (rsComplaint_first > rsComplaint_total) Then
rsComplaint_first = rsComplaint_total
End If
If (rsComplaint_last > rsComplaint_total) Then
rsComplaint_last = rsComplaint_total
End If
If (rsComplaint_numRows > rsComplaint_total) Then
rsComplaint_numRows = rsComplaint_total
End If
End If
%>
<%
' *** Recordset Stats: if we don't know the record count, manually count them

If (rsComplaint_total = -1) Then

' count the total records by iterating through the recordset
rsComplaint_total=0
While (Not rsComplaint.EOF)
rsComplaint_total = rsComplaint_total + 1
rsComplaint.MoveNext
Wend

' reset the cursor to the beginning
If (rsComplaint.CursorType > 0) Then
rsComplaint.MoveFirst
Else
rsComplaint.Requery
End If

' set the number of rows displayed on this page
If (rsComplaint_numRows < 0 Or rsComplaint_numRows > rsComplaint_total) Then
rsComplaint_numRows = rsComplaint_total
End If

' set the first and last displayed record
rsComplaint_first = 1
rsComplaint_last = rsComplaint_first + rsComplaint_numRows - 1

If (rsComplaint_first > rsComplaint_total) Then
rsComplaint_first = rsComplaint_total
End If
If (rsComplaint_last > rsComplaint_total) Then
rsComplaint_last = rsComplaint_total
End If

End If
%>
<%
Dim MM_paramName
%>
<%
' *** Move To Record and Go To Record: declare variables

Dim MM_rs
Dim MM_rsCount
Dim MM_size
Dim MM_uniqueCol
Dim MM_offset
Dim MM_atTotal
Dim MM_paramIsDefined

Dim MM_param
Dim MM_index

Set MM_rs = rsComplaint
MM_rsCount = rsComplaint_total
MM_size = rsComplaint_numRows
MM_uniqueCol = ""
MM_paramName = ""
MM_offset = 0
MM_atTotal = false
MM_paramIsDefined = false
If (MM_paramName <> "") Then
MM_paramIsDefined = (Request.QueryString(MM_paramName) <> "")
End If
%>
<%
' *** Move To Record: handle 'index' or 'offset' parameter

if (Not MM_paramIsDefined And MM_rsCount <> 0) then

' use index parameter if defined, otherwise use offset parameter
MM_param = Request.QueryString("index")
If (MM_param = "") Then
MM_param = Request.QueryString("offset")
End If
If (MM_param <> "") Then
MM_offset = Int(MM_param)
End If

' if we have a record count, check if we are past the end of the recordset
If (MM_rsCount <> -1) Then
If (MM_offset >= MM_rsCount Or MM_offset = -1) Then ' past end or move last
If ((MM_rsCount Mod MM_size) > 0) Then ' last page not a full repeat region
MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
Else
MM_offset = MM_rsCount - MM_size
End If
End If
End If

' move the cursor to the selected record
MM_index = 0
While ((Not MM_rs.EOF) And (MM_index < MM_offset Or MM_offset = -1))
MM_rs.MoveNext
MM_index = MM_index + 1
Wend
If (MM_rs.EOF) Then
MM_offset = MM_index ' set MM_offset to the last possible record
End If

End If
%>
<%
' *** Move To Record: if we dont know the record count, check the display range

If (MM_rsCount = -1) Then

' walk to the end of the display range for this page
MM_index = MM_offset
While (Not MM_rs.EOF And (MM_size < 0 Or MM_index < MM_offset + MM_size))
MM_rs.MoveNext
MM_index = MM_index + 1
Wend

' if we walked off the end of the recordset, set MM_rsCount and MM_size
If (MM_rs.EOF) Then
MM_rsCount = MM_index
If (MM_size < 0 Or MM_size > MM_rsCount) Then
MM_size = MM_rsCount
End If
End If

' if we walked off the end, set the offset based on page size
If (MM_rs.EOF And Not MM_paramIsDefined) Then
If (MM_offset > MM_rsCount - MM_size Or MM_offset = -1) Then
If ((MM_rsCount Mod MM_size) > 0) Then
MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
Else
MM_offset = MM_rsCount - MM_size
End If
End If
End If

' reset the cursor to the beginning
If (MM_rs.CursorType > 0) Then
MM_rs.MoveFirst
Else
MM_rs.Requery
End If

' move the cursor to the selected record
MM_index = 0
While (Not MM_rs.EOF And MM_index < MM_offset)
MM_rs.MoveNext
MM_index = MM_index + 1
Wend
End If
%>
<%
' *** Move To Record: update recordset stats

' set the first and last displayed record
rsComplaint_first = MM_offset + 1
rsComplaint_last = MM_offset + MM_size

If (MM_rsCount <> -1) Then
If (rsComplaint_first > MM_rsCount) Then
rsComplaint_first = MM_rsCount
End If
If (rsComplaint_last > MM_rsCount) Then
rsComplaint_last = MM_rsCount
End If
End If

' set the boolean used by hide region to check if we are on the last record
MM_atTotal = (MM_rsCount <> -1 And MM_offset + MM_size >= MM_rsCount)
%>
<%
' *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters

Dim MM_keepNone
Dim MM_keepURL
Dim MM_keepForm
Dim MM_keepBoth

Dim MM_removeList
Dim MM_item
Dim MM_nextItem

' create the list of parameters which should not be maintained
MM_removeList = "&index="
If (MM_paramName <> "") Then
MM_removeList = MM_removeList & "&" & MM_paramName & "="
End If

MM_keepURL=""
MM_keepForm=""
MM_keepBoth=""
MM_keepNone=""

' add the URL parameters to the MM_keepURL string
For Each MM_item In Request.QueryString
MM_nextItem = "&" & MM_item & "="
If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then
MM_keepURL = MM_keepURL & MM_nextItem & Server.URLencode(Request.QueryString(MM_item))
End If
Next

' add the Form variables to the MM_keepForm string
For Each MM_item In Request.Form
MM_nextItem = "&" & MM_item & "="
If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then
MM_keepForm = MM_keepForm & MM_nextItem & Server.URLencode(Request.Form(MM_item))
End If
Next

' create the Form + URL string and remove the intial '&' from each of the strings
MM_keepBoth = MM_keepURL & MM_keepForm
If (MM_keepBoth <> "") Then
MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1)
End If
If (MM_keepURL <> "") Then
MM_keepURL = Right(MM_keepURL, Len(MM_keepURL) - 1)
End If
If (MM_keepForm <> "") Then
MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1)
End If

' a utility function used for adding additional parameters to these strings
Function MM_joinChar(firstItem)
If (firstItem <> "") Then
MM_joinChar = "&"
Else
MM_joinChar = ""
End If
End Function
%>
<%
' *** Move To Record: set the strings for the first, last, next, and previous links

Dim MM_keepMove
Dim MM_moveParam
Dim MM_moveFirst
Dim MM_moveLast
Dim MM_moveNext
Dim MM_movePrev

Dim MM_urlStr
Dim MM_paramList
Dim MM_paramIndex
Dim MM_nextParam

MM_keepMove = MM_keepBoth
MM_moveParam = "index"

' if the page has a repeated region, remove 'offset' from the maintained parameters
If (MM_size > 1) Then
MM_moveParam = "offset"
If (MM_keepMove <> "") Then
MM_paramList = Split(MM_keepMove, "&")
MM_keepMove = ""
For MM_paramIndex = 0 To UBound(MM_paramList)
MM_nextParam = Left(MM_paramList(MM_paramIndex), InStr(MM_paramList(MM_paramIndex),"=") - 1)
If (StrComp(MM_nextParam,MM_moveParam,1) <> 0) Then
MM_keepMove = MM_keepMove & "&" & MM_paramList(MM_paramIndex)
End If
Next
If (MM_keepMove <> "") Then
MM_keepMove = Right(MM_keepMove, Len(MM_keepMove) - 1)
End If
End If
End If

' set the strings for the move to links
If (MM_keepMove <> "") Then
MM_keepMove = Server.HTMLEncode(MM_keepMove) & "&"
End If

MM_urlStr = Request.ServerVariables("URL") & "?" & MM_keepMove & MM_moveParam & "="

MM_moveFirst = MM_urlStr & "0"
MM_moveLast = MM_urlStr & "-1"
MM_moveNext = MM_urlStr & CStr(MM_offset + MM_size)
If (MM_offset - MM_size < 0) Then
MM_movePrev = MM_urlStr & "0"
Else
MM_movePrev = MM_urlStr & CStr(MM_offset - MM_size)
End If
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "

http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<table width="838" border="1" align="center">
<tr>
<td align="left" width="33%"> RefNo </td>
<td align="left" width="33%"> SugarDate </td>
<td align="left" width="33%"> SummaryDescription </td>
<td align="left" width="33%"><%=(rsCustomer.Fields.Item("lastName").Value)%></td>
<td align="left" width="33%">&nbsp;</td>
<td align="left" width="33%">&nbsp;</td>
<td align="left" width="33%">&nbsp;</td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT rsComplaint.EOF))
%>
<tr>
<td align="left" width="33%"><a href="complaint.asp?<%= Server.HTMLEncode(MM_keepBoth) & MM_joinChar(MM_keepBoth) & "RefNo=" & rsComplaint.Fields.Item("RefNo").Value %>"><%=(rsComplaint.Fields.Item("RefNo").Value)%></a> </td>
<td align="left" width="33%"><%=(rsComplaint.Fields.Item("SugarDate").Value)%> </td>
<td align="left" width="33%"><%=(rsComplaint.Fields.Item("SummaryDescription").Value)%> </td>
<td align="left" width="33%">&nbsp;</td>
<td align="left" width="33%">&nbsp;</td>
<td align="left" width="33%">&nbsp;</td>
<td align="left" width="33%">&nbsp;</td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsComplaint.MoveNext()
Wend
%>
</table>
<br>
<table border="0" width="50%" align="center">
<tr>
<td width="23%" align="center"><% If MM_offset <> 0 Then %>
<a href="<%=MM_moveFirst%>">First</a>
<% End If ' end MM_offset <> 0 %>
</td>
<td width="31%" align="center"><% If MM_offset <> 0 Then %>
<a href="<%=MM_movePrev%>">Previous</a>
<% End If ' end MM_offset <> 0 %>
</td>
<td width="23%" align="center"><% If Not MM_atTotal Then %>
<a href="<%=MM_moveNext%>">Next</a>
<% End If ' end Not MM_atTotal %>
</td>
<td width="23%" align="center"><% If Not MM_atTotal Then %>
<a href="<%=MM_moveLast%>">Last</a>
<% End If ' end Not MM_atTotal %>
</td>
</tr>
</table>
Records <%=(rsComplaint_first)%> to <%=(rsComplaint_last)%> of <%=(rsComplaint_total)%></body>
</html>
<%
rsComplaint.Close()
Set rsComplaint = Nothing
%>
<%
rsCustomer.Close()
Set rsCustomer = Nothing
%>


CJB

 

[steven290]

try some thing like this

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/connMAC.asp" -->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Contact List</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<%
function emptyrecordfix(strtext)
if isempty(strtext) or isnull(strtext) or strtext="" then
temp="&nbsp;"
else
temp=strtext
end if
emptyrecordfix=temp
end function

'strDBPath = Server.MapPath("yourdb.mdb")

'Set MM_connMAC_STRING = Server.CreateObject("ADODB.Connection")

'MM_connMAC_STRING.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"

sql="select lastname from tblCustomer where id="&request.querystring("id") 

Set info = MM_connMAC_STRING.Execute(sql)
lastname=info("lastname")
info.Close
Set info = Nothing

sql="select id,refno,sugardate,summarydescription from tblComplaint where userid="&request.querystring("id") 

Set rs = MM_connMAC_STRING.Execute(sql)


if not rs.eof then
rows = rs.GetRows()
rs.Close
Set rs = Nothing
FRec   = LBound(rows, 2)
LRec    = UBound(rows, 2)
compid=
refno=emptyrecordfix(rows(1, r))
sugardate=emptyrecordfix(rows(2, r))
summarydescription=emptyrecordfix(rows(3, r))
end if

MM_connMAC_STRING.close
set MM_connMAC_STRING = nothing
%>

<table width="838" border="1" align="center">
  <tr>
    <td align="left" width="33%"> RefNo </td>
    <td align="left" width="33%"> SugarDate </td>
    <td align="left" width="33%"> SummaryDescription </td>
    <td align="left" width="33%"><%=lastname%></td>
    <td align="left" width="33%">&nbsp;</td>
    <td align="left" width="33%">&nbsp;</td>
    <td align="left" width="33%">&nbsp;</td>
  </tr>
  <%
For r = FRec To LRec
%>
  <tr>
    <td align="left" width="33%"><a href="complaint.asp?RefNo="<%=refno%>"><%=refno%></a></td>
    <td align="left" width="33%"><%=sugardate%></td>
    <td align="left" width="33%"><%=summarydescription%></td>
    <td align="left" width="33%">&nbsp;</td>
    <td align="left" width="33%">&nbsp;</td>
    <td align="left" width="33%">&nbsp;</td>
    <td align="left" width="33%">&nbsp;</td>
  </tr>
  <%
next
%>
</table>
</body>
</html>

 

[bslintx]

if set up as a true relational database and normalized; why not join the 3 tables so you won't have to make a connection/recordet for each query?

BSL

 

[hdgirl]

i have set them up with relationships but i am still having problems displaying data from all three tables.
i have tblComplaints
RefNo PK & AN

tblCustomer
RefNo FK
CustNo PK & AN

tblAction
CustNo FK
ActNo PK & AN

is this correct?


CJB

 

[damber]

CJB,

Based on your last post, are you saying that a complaint may be made by several customers ?

I would have assumed you meant to structure them in this way:

tblCustomers
	PK CustNo

	
tblComplaints
	PK RefNo
	FK CustNo
	
tblAction
	PK ActNo
	FK RefNo

This will mean that a customer can have many complaints, and each complaint can have many actions.

It may have just been a typo in your post, but your setup would mean that a complaint would have many customers and each customer could have many actions.. which in a customer service type environment, seems a little strange.


Also if you join the tables as per bslintx's suggestion be careful what type of JOIN you use in your SQL statement, otherwise you will not get results if sub-tables do not have any records - for example, if there are no actions for a complaint then an INNER JOIN will return no rows, but a LEFT JOIN will show as expected, with the details of the sub table (i.e. actions) empty.

As a suggestion in terms of approach, it may be better if you take bslintx's advice and create a joined recordset from the 3 tables - then return into an array that you can cycle through.

This means that you can then step through each row, conditionally creating the table levels depending on whether the current CustNo or RefNo is the same as the last etc, which means you would only need 1 pass to generate the entire table - efficient on the processing and the coding.

Anyway, I hope these random thoughts help..

Damian

A smile is worth a thousand kind words. So smile, it's easy!

 

[hdgirl]

Sorry, we can have one complaint with many customers that may have many actions

CJB

 

[bslintx]

CJB, This is where many get confused but it is critical that it is set up correctly....you are saying only one complaint can be complained by a customer...this is incorrect logically...obviously he can have as many complaints he wants.......

say a customer files a complaint 1 day - 1 complaint
then 2 weeks later he files another complaint - 2 complaints

Sooo, that means a customer CAN have more than 1 complaint,
NOT just one as you have indicated. Each complaint should be linked to the customerID so if you queried Joe Blow with an ID of 234 the query can search in complaint table where customerID = 234 and show all his complaints. This is a one-to-many relationshipt between Customer (the one) and Complaints (the many)...Technically any one customer can have the SAME complaint sooo it COULD be set up as a many-tomany between the 2 tables if say you are using complaint options from a select box where the data will always be the same...if a simple input box...i would do the 1-to-many.

Now it's assumed the Complaints can have many fix actions...again depends how you have it set up...could be a many-to- many or 1-to-many; however, I agree with dambers approach..it's simple and would work.

I HIGHLY recommend to use MS Access query maker and add these tables and experiment with the JOIN statements...best way to figure it out joins!!! and very easy to use. look at the results of the query in the datasheet view...if you get the result you want...go to sql view and voila...there is your sql statent to insert into your asp page to make your table.

If you have anymoretroubles please let us know...we'll get you through it...just remember to think about ALL posibilities when creating realtionships---very important and again the join will containg the data you need to create the 3 tables you need because you have JOINED them...and again as damber and i mentioned uses only 1 sql statement!

Good Luck

BSL

 

[hdgirl]

guys thanks for your replies and i think im getting my head around this. let me explain a bit more,this is how we have to set it up. we are an airport, for example check in desk fails we get many complaints, this can then have many actions ie handling agents will deal as will airport customer service. again baggage belt fails we get many complaints with many actions. so we have a complaint with many customers complaining with many actions taken.

thx again

CJB

 

[damber]

CJB,

I think I see what you're trying to do. The first thing to do with any data design is to understand the discrete entities, such as customer, complaint, action etc and then to work out what meta data compliments these (such as customer name, address, telephone no etc) ensuring that it can all be encapsulated in a single 'unit' one level deep (i.e. don't put 'home address' and 'business address' in the customer table, create a linked table 'addresses' and set the type of address in there). Then it is necessary to relate the tables together in a meaningful way.

If we were to apply this to your problem, we would get something like:

1. ServiceComponents (Conveyors, CheckinDesks, TicketSalesOffice etc)
2. Customers (users of the ServiceComponents)
3. Complaints (complaints raised by the customer for an event concerning a Service Component)
4. Actions (Pre-Defined Actions that COULD be taken)
5. ComplaintActions (Actions that WERE taken for this specific complaint)
6. Problem (the singular event that occurred to cause the complaints)


There are many more tables you could use, such as (airport, airline, employee etc etc, though to keep it simple the above should suffice). Then we need to relate them together...

Customers are your main focus.. this is after all a customer service! So we should start with them. The purpose of this is to capture complaints, so they should be the next consideration...

1. ONE Customer can have MANY Complaints

But what are they complaining about?

2. ONE Complaint can have ONE Problem

Actually, you could also have this as a Many to Many relationship where you create a table in between Problems and Complaints so that a complaint can refer to several problems... though it is most likely that you would want a complaint to focus on just 1 problem at a time. We then need to relate the problems to the components

3. ONE ServiceComponent can have MANY Problems

Then we would need to do something about the complaint, so..

4. ONE Complaint can have MANY ComplaintActions

As the Actions list is predefined - you can select the actions to take which then updates the ComplaintActions above, so there would implicitly be a relationship between Actions and ComplaintActions

5. ONE Action can have MANY ComplaintActions


The next step once you've defined the data and how it relates to each other, is to consider the usage of the information. (In the real world, you need to do a bit of this during the data design to ensure you don't preclude any function by applying a particular design)

The data usage and presentation is concerned with user requirements and business process. What does the user want to see? This needs to also consider perspectives... not everyone will want to see the data in the same way - managers will be concerned more about stats - i.e. how many complaints have we had about our checkin desks or how many times have we refunded customers their money (linked from the actions table) etc etc. However, a customer service operator will be more interested in viewing the customer and their related complaints.

So, you could create a 'problem' table to capture the failures of the components. The customers can then have individual 'incidents' or complaints that relate to a problem which relates to ServiceComponents and also each can have many actions selected from a set list. This is a little more involved than the 3 original tables, but is much more flexible and appropriate for a complaint/incident logging application.

I hope these ramblings will help you in understanding the data better before you move on to consider presentation, process and perspective.

Hope that helps

A smile is worth a thousand kind words. So smile, it's easy!

 

[bslintx]

dang,


couldn't have said it better myself damber!!! ;-) Although we see what you are TRYING to do CJ, we are just letting you know that the database yu have right now has "holes" in it and will become a maintenance nightmare later. What damber is doing (sounds like he does this on a professional level) is true database design at its finest. You really do have to consider all the potential "future" changes that may affect the overall database design. Otherwise you will have to redesign the database and this you certainlt do not want to have to do. It's paramount you capture anything that creates "a domino effect" hence realtionships. Now, since damber and i have tried to explain the sensitivities of database design and come up w/ a pretty good idea of what tables you need (the backend)let's get down to what YOU/customer/bosses cowworkers want to see see as a result of querying? Are you using a customer complaint form? If so, please post code. Do your bsses want a stat sheet as damber mentioned? How do you want to track the customers complants? All at once? One at a time? Both capabilities? Etc. If you have a form damber, myself, and others can see more on the frontend side and the know exactly how the backend should look. Although this is backwards, it's apparent you may be new to designing databases but you probably will only get frustrated if don't speed things along. But remember...good database design takes time. i can't speak for damber but i imagine he may want to look at what you have to since he's tried so hard explaining. need-less-to say....what he had last posted should be what you need as far as tables and design for the backend. Sorry for the "rambling" here too but before we can "vie" the data we need to ensure the backend is setup correctly. Once that is done you can query your lil heart out!!! let us know and hang in there!!!

BSL