Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2005
    Posts
    6

    Unanswered: Displaying data from an access db via ASP, some direction needed

    OK, here's the problem/issue.

    I have a database (access) which records details and displays as follows

    -----------A----B----C----D
    Apples-----1----2----------
    Bananas---3----4---------6
    Carrots--------------3----1
    Kiwi------------1---------2

    Now all is fine as long as there is something in all the columns. But say if there were no D's for Kiwi

    I would get the following in the DB

    -----------A----B----C----
    Apples-----1----2
    Bananas-------3----5
    Carrots--------6
    Kiwi------1

    But it would error in the asp as there is nothing in D

    What I really need is something that will create a crosstab in Access like this

    -----------A----B----C----D
    Apples-----1-----2-----0-----0
    Bananas---0-----3-----5-----0
    Carrots----6-----0-----0-----0
    Kiwi-------1-----0-----0-----0

    So that there is always something in the fields.

    I have got around it by having a test row, which always ensures that there is something for all values

    Which looks like this

    -----------A----B----C----D
    Apples-----1-----2------------
    Bananas---------3-----5------
    Carrots----6------------------
    Kiwi-------1------------------
    Test------1-----1-----1-----1

    But obviously there is a row on the ASP page with the test data, and I can't find a way of hiding this and making it all work.

    I cant work out to do it either by the ASP of in Access...

    Please help if you can, this is driving me up the wall.

    Many thanks
    Neil

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    so it sounds like you have three tables something like fruit, category, categoryfruit

    The fruit table has the following records
    Code:
    FruitId     FruitName
    1            Apples
    2            Bananas
    3            Carrots
    4            Kiwi
    The category table has the following records
    Code:
    CategoryCode 
    A
    B
    C
    D
    The categoryfruit table has the following records
    Code:
    CategoryCode   FruitId   FruitCount
    A                       1           1
    B                       1           2
    A                       2           3
    B                       2           4
    D                       2           6
    C                       3           3
    D                       3           1
    B                       4           1
    D                       4           2
    Is that close to right??

  3. #3
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    What I really need is something that will create a crosstab in Access like this
    It seems like I've done something like that with a bunch of IIF()'s in the SQL statement (which is allowable with an Access database) and then it didn't even need to be a crosstab query.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    I think there is a cross join query that you can do or something similar,.. I had a similar issue but that was an MS SQL database and I'm not sure how Access handles it....

    I would have an ask in the Database forums rather then the ASP forum...

  5. #5
    Join Date
    Dec 2005
    Posts
    6

    Cheers for your help

    Will post in different forum.
    Neil

  6. #6
    Join Date
    Oct 2005
    Posts
    178

    Wink

    Neil,
    If you are still working on this, sHow me your Access Structure and Iwill give the solution in ASP, which means I will test it in my Localhost before I will give you the code. Don't go away from this forum. Email me when you have it. I did not see your posting until today.

    Fred

  7. #7
    Join Date
    Oct 2005
    Posts
    178
    I created a table table1 with fields ID, Fruit, Location, and Qty
    I created a DSN in my IIS webserver and called it "Fruits"
    and here's the code. Copy it to your webserver and save it as .ASP
    .and I attached the resulting browse view
    This application does not restrict you for adding Locations or fruits, I made it somewhat universal.
    I hope this will keep your trust with DBForums

    Code:
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
    <script language="VBScript">
    <% 
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adComdText = 1
    dim Fruit, TotalFruit, Location, TotalLocation
    dim Qty()
    dim LocationName()
    dim FruitName()
    Fruit= 0
    Location = 0
    set  SR = Server.CreateObject("ADODB.Recordset")
    SR.Open "Select Distinct Fruit From Table1 order by Fruit", "DSN=Fruits", adOpenStatic, adLockOptimistic, adCmdText
    
    ' Count Number Of FruitName
    Do while Not SR.eof 
    TotalFruit = TotalFruit + 1
    Redim Preserve FruitName(TotalFruit)
    FruitName(TotalFruit) = SR("Fruit")
    SR.movenext
    Loop
    SR.Close
    
    ' Count Number Of TotalLocation
    SR.Open "Select Distinct Location From Table1 order by Location", "DSN=Fruits", adOpenStatic, adLockOptimistic, adCmdText
    Do while Not SR.eof 
    TotalLocation = TotalLocation + 1
    Redim Preserve LocationName(TotalLocation)
    LocationName(TotalLocation) = SR("Location")
    SR.movenext
    Loop
    SR.Close
    
    Redim Preserve Qty(TotalFruit,TotalLocation)
     
    ' Establish Qty numbers
    For x = 1 to TotalFruit
    For y = 1 to TotalLocation
    Qty(x,y) = "0"
    next
    next
    
    
    SR.Open "Select * From Table1 order by Fruit, Location", "DSN=Fruits", adOpenStatic, adLockOptimistic, adCmdText
    do while NOT SR.EOF 
    
    For x = 1 to TotalFruit
    For y = 1 to TotalLocation
    if FruitName(x) = SR("Fruit") and LocationName(y) = SR("Location") then
     Qty(x,y) = SR("Qty")
    end if
    next
    next
    
    
    SR.MoveNext
    Loop
    SR.Close
    %>
    
    </script>
    <HTML>
    
    <HEAD>
    	<TITLE>Untitled</TITLE>
    </HEAD>
    
    <BODY>
    <table cellspacing="2" cellpadding="2">
    <tr>
        <td>&nbsp;Description</td>
    	<% for y = 1 to TotalLocation %>
        <td><%= LocationName(y) %></td>
    	<% Next %>
    </tr>
    <% for x = 1 to TotalFruit %>
    <tr>
    	<td><%= FruitName(x) %></td><% for y = 1 to TotalLocation %>
    	<td><%= Qty(x,y) %></td><% next %>
    <% next %>
    </table>
    
    
    </BODY>
    </HTML>
    Attached Thumbnails Attached Thumbnails Fruits.bmp  
    Last edited by fredservillon; 01-15-06 at 18:09.

Posting Permissions

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