Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Posts
    8

    Exclamation Unanswered: dynamic web query design req. Check this out!!!!!!!

    I want to create a query for 12 tables. coz all tables have comman fields and condition is also same.

    Againt one itemid there are 12 possible entries for diferrents states. one idea i had in my mind is nested table but i dono how to use that.
    column names are:

    itemid
    st_gb
    sb_gb
    rrno
    train_no
    ld_dt_stand
    uld_dt_stand
    ld_dt_subs
    uld_dt_subs
    rdst_dt
    rdsb_dt
    Remark
    noofdays
    entryby
    Now i am using 12 tables one for each state.....

    Wat should be the proper design and tell me the cursor mode to save my resources..

    See code below......

    '''''making array of tables
    arr_detail=split("delhi,kol,banglore,ahmd,chennai, hyd,mumbai,tvm,pun e,foreign,courier_national,courier_mumbai,courier_ delhi",",")


    if detail=1 then
    for i=lBound(arr_detail) to UBound(arr_detail)
    str_detail= str_detail & "select * from "& arr_detail(i) &" where itemid='" & itemid &"'^"
    next

    '''''''making array of Query
    arr_str = Split(str_detail,"^")


    for i=lBound(arr_str) to UBound(arr_str)-1
    ' Response.Write "
    "& arr_str(i)
    set rs_detail =Server.CreateObject("ADODB.RecordSet")
    rs_detail.Open arr_str(i),connview, adOpenStatic, adLockOptimistic
    %>
    <tr>
    <%

    do while not rs_detail.EOF
    %>
    <tr>
    <%
    for k=0 to rs_detail.Fields.count-1
    if isnull(rs_detail(k)) or trim(rs_detail(k))=""or trim(rs_detail(k))="--" then %>
    <td> </td>
    <%
    else
    %>
    <td><%=rs_detail(k)%></td>
    <% end if %>

    <%
    next
    %></tr><%
    rs_detail.MoveNext
    loop
    Response.End
    rs_detail.Close
    set rs_detail=nothing
    next

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Add a state field to your schema and use only one table.

    You didn't mention what database you're using. If it's SQL Server and you're worried about performance, create a stored procedure that accepts state as an argument and returns your filtered table. Any updates required could then also be handled via stored procedure. That allows you to only open a connection for extremely brief periods of time, perform all of the processing server-side and add a layer of security.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Why not use a union query that spans across all the tables??
    Code:
    select col1, col2 from table1
    union
    select col1, col2 from table2

  4. #4
    Join Date
    Dec 2005
    Posts
    8

    Exclamation how to do that

    Database is in MS-Access and i hav to do manage it ..
    Well if i add a field 'state' and import all data to one table then struct. of table will remain same? and if i want to stick with the database i m having right now then wat is the possible solution...

    the problem domain:

    User will select and issue and i have to get only those states where record is enterd and skip the states where no record found..

    Tell me another thing about how to import all 12 tables in to one table..


    Regards,

    Sanjay

  5. #5
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Um,.. Access can still do union queries... you just need to go into the sql view of the query....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •