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

peelola · #1 (**permalink**) 12-29-05, 05:10

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

rokslide · #2 (**permalink**) 12-29-05, 17:47

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??

Bullschmidt · #3 (**permalink**) 01-02-06, 19:42

Quote:

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.

rokslide · #4 (**permalink**) 01-02-06, 20:21

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...

peelola · #5 (**permalink**) 01-04-06, 08:18

Will post in different forum.
Neil

fredservillon · #6 (**permalink**) 01-15-06, 10:20

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

fredservillon · #7 (**permalink**) 01-15-06, 16:36

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>

Internet Services

Web Development

Digital Marketing

Consumer Tech