Thanks - but this is not helping.... I have already optimized my structure
by employing a complex join in the jet engine...... I am not really sure
how to re-adapt my db and asp code to fit that model...
I mean, I have the damn Cust ID and Inventory ID already in my query...there
has just got to be a way to make sure the Customer ID (and related customer
info fields in query) show up ONCE with multiple inventory items listed....
Can someone help me get my head around this...Bobs suggestion seems to fall
short....I cannot pinpoint where the script if flawed as I am no array
expert. It appears that If I take this section and place it outside the For
Loop I end up with one inventory item as oppossed to 3 repeats:
response.write "<tr><td> </td><td>"
response.write ar(1,i) & " + " & ar(4,i)
response.write "</td></tr>"
response.write "</table>"
But, I am at a loss as to how to get the script to loop for the individual
customer and show just the boats related to him. Full code:
dim cnn, rs, ar, iRow, curCust, newCust
cnn.Open strCon
'//------------------------------ PAGEWATCH
SQL -------------------------------
SQL="EXEC [qry_PageWatch+PriceChanges] @CID=NULL,@LID=NULL" '//This
'//-------------------------------------------------------------------------
-
set rs = cnn.execute(SQL) '//This
'open a connection using cn
'set rs = server.createobject("adodb.recordset")
'cnn.qGetCustInv rs
if not rs.eof then ar = rs.GetRows
rs.close: set rs = nothing
cnn.close: set cn = nothing
if isarray(ar) then
curCust = ar(0,0)
Response.Write curCust
response.write "<table border=0>"
' b=0
For iRow = 0 to ubound(ar,2)
' b=b+1
'Response.Write b
newCust = ar(0,i)
if newCust <> curCust then
curCust = newCust
response.write "<tr><td colspan=2>"
response.write curCust & "hi"
response.write "</td></tr>"
end if
'//Response.Write "hi"
next
response.write "<tr><td> </td><td>"
response.write ar(1,i) & " + " & ar(4,i)
response.write "</td></tr>"
response.write "</table>"
erase ar
else
response.write "No records"
end if
"Me" <none@none.com> wrote in message
news:2752801c38f4d$564cbfb0$a601280a@phx.gbl...[color=blue]
> I would use this as a reference
> (
http://aspfaq.com/show.asp?id=2241), it does what you are
> looking for but uses 2 tables opposed to 1. The key is
> setting a variable to nothing, reading the first record,
> comparing the variable to the first records variable, if
> not matched, move new value to the variable, write out the
> 101 the write out the details, if it is matched, just
> write out the details. I hope that helps.
>
>[color=green]
> >-----Original Message-----
> >Hey Bob...this does not appear to solve the[/color]
> problem.....when I run your[color=green]
> >script for my query I get the following and only the[/color]
> following for the first[color=green]
> >customer in the query:
> >
> >101 (hidden)
> >
> > 356
> > 356
> > 356
> >
> >Thus, it appears to be finding the first customer record[/color]
> and listing the[color=green]
> >yacht attached to that customer 3 times (matching the[/color]
> record count for all[color=green]
> >the records in my table). It ignores the 2 other customer[/color]
> records and their[color=green]
> >yacht inventory items.
> >
> >I followed your general instructions for my existing[/color]
> query which is slightly[color=green]
> >more complex but nevertheless should work:
> >
> >PARAMETERS CID Long;
> >SELECT tblCustomer.CustomerID, tblPageWatch.ListingsID AS[/color]
> ListingsID,[color=green]
> >qry_ListingsPriceChanges.NewPrice,[/color]
> qry_ListingsPriceChanges.Original_Price,[color=green]
> >qry_ListingsPriceChanges.Name,[/color]
> qry_ListingsPriceChanges.tblCompany.Company,[color=green]
> >qry_ListingsPriceChanges.Model,[/color]
> qry_ListingsPriceChanges.Size_ID,[color=green]
> >qry_ListingsPriceChanges.Hull_Number,[/color]
> qry_ListingsPriceChanges.Year,[color=green]
> >qry_ListingsPriceChanges.Location_Status,
> >qry_ListingsPriceChanges.Yacht_Type,[/color]
> qry_ListingsPriceChanges.Market_Status,[color=green]
> >qry_ListingsPriceChanges.Charter_Status,
> >qry_ListingsPriceChanges.Broker_Name,[/color]
> qry_ListingsPriceChanges.Broker_Email,[color=green]
> >tblCustomer.first_name, tblCustomer.last_name,[/color]
> tblCustomer.email_address,[color=green]
> >qry_ListingsPriceChanges.Condition,[/color]
> qry_ListingsPriceChanges.Insertion_Date[color=green]
> >FROM tblCustomer INNER JOIN (qry_ListingsPriceChanges[/color]
> INNER JOIN[color=green]
> >tblPageWatch ON[/color]
> qry_ListingsPriceChanges.tblListings.ListingsID =[color=green]
> >tblPageWatch.ListingsID) ON tblCustomer.CustomerID =[/color]
> tblPageWatch.CustomerID[color=green]
> >WHERE ((([CID]) Is Null)) OR (((tblCustomer.CustomerID)=[/color]
> [CID]))[color=green]
> >ORDER BY tblCustomer.CustomerID, tblPageWatch.ListingsID ;
> >
> >What am I doing wrong?
> >
> >Many thanks
> >Jason
> >
> >
> >
> >
> >"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> >news:OIpaaPzjDHA.2512@TK2MSFTNGP09.phx.gbl...[color=darkred]
> >> 1. Create this saved query called qGetCustInv in your[/color][/color]
> Access database:[color=green][color=darkred]
> >> Select CustID,InventID FROM Cust-Invent
> >> ORDER BY CustID,InventID
> >>
> >> 2. In your asp page:
> >> <%
> >> dim cn, rs, ar, iRow, curCust, newCust
> >> 'open a connection using cn
> >> set rs = server.createobject("adodb.recordset")
> >> cn.qGetCustInv rs
> >> if not rs.eof then ar = rs.GetRows
> >> rs.close: set rs = nothing
> >> cn.close: set cn = nothing
> >> if isarray(ar) then
> >> curCust = ar(0,0)
> >> response.write "<table border=0>"
> >> For iRow = 0 to ubound(ar,2)
> >> newCust = ar(0,i)
> >> if newCust <> curCust then
> >> curCust = newCust
> >> response.write "<tr><td colspan=2>"
> >> response.write curCust
> >> response.write "</td></tr>"
> >> end if
> >> response.write "<tr><td> </td><td>"
> >> response.write ar(1,i)
> >> response.write "</td></tr>"
> >> next
> >> response.write "</table>"
> >> erase ar
> >> else
> >> response.write "No records"
> >> end if
> >>
> >> HTH,
> >> Bob Barrows
> >>
> >> jason wrote:
> >> > Access 2000:
> >> >
> >> > I have a customer-inventory table I need to loop[/color][/color]
> through and compile[color=green][color=darkred]
> >> > a list of all the inventory items the customer is[/color][/color]
> tracking. The[color=green][color=darkred]
> >> > problem I am finding is that a simple loop will pull[/color][/color]
> out the customer[color=green][color=darkred]
> >> > details each time their is an inventory item[/color][/color]
> listed....I need to get[color=green][color=darkred]
> >> > the customer out ONCE and list his items....is there[/color][/color]
> an elegant way[color=green][color=darkred]
> >> > to do this:
> >> >
> >> > Here is the table:
> >> > Cust-Invent Table
> >> >
> >> > CustID InvenID
> >> > 101 356
> >> > 101 222
> >> > 101 187
> >> > 55 34
> >> > 55 123
> >> >
> >> >
> >> > But I need to get the extract it to my ASP page like[/color][/color]
> this:[color=green][color=darkred]
> >> >
> >> > 101
> >> > ----
> >> > 356
> >> > 222
> >> > 187
> >> >
> >> > 55
> >> > ---
> >> > 34
> >> > 123
> >> >
> >> > If I simply loop the table I will repeat the customer[/color][/color]
> details which I[color=green][color=darkred]
> >> > do not want want. I just want to get the CustID and[/color][/color]
> then list the[color=green][color=darkred]
> >> > inventory items underneath (and send an email to him)[/color][/color]
> and then[color=green][color=darkred]
> >> > rs.movenext to the next record....
> >> >
> >> > I really appreciate any help here...
> >> > Jason
> >>
> >>[/color]
> >
> >
> >.
> >[/color][/color]
