ASP/Access question

[flillis]

Hi,

I have two Ms Access tables:

table 1:
priceCodeTitle / TicketTypeID / price
-------------------------------------------------
adult / 1 / 20
adult / 2 / 50
kids / 1 / 10
kids / 2 / 20


etc

table 2:
TicketTypeID / TicketTypeName
----------------------------------------------
1 / halfday
2 / fullday


The ticketTypeID in table one relates to the field of the same name in table 2.

I need to produce a result from these tables that displays on my site as a html table of this structure:

halfday fullday
Adult 20 50
Kids 10 20


Simple so far… and I have a sql statement that joins the two tables something like this:

SELECT table1.*, table2. TicketTypeName FROM table1, table2 WHERE table1. TicketTypeID = table2. TicketTypeID

(forget the syntax, but you get the idea)

My problem when building the tables from these results is that the user may add any number of TicketTypes or any number of PriceCodes. They may also switch off any price for any particular entry (ie..Adult Fullday will display N/A)

I’m having trouble buiding this table in asp.. Someone did attempt to explain it to me by saying that I needed to build some sort of a matrix in memory, and check at each position on the table whether there is an associated value to fit in here. Which makes great sense in a general logic discussion – but when it comes down to actually building it, I am completely stumped as to where to start.

Can anyone help me out?

F

PS – I know it would be possible to create a similar table to the one needing to be produced in the database. But this would involve the site administrators adding and deleting rows from a table – which I cannot allow them to do.

[jhardman]

F,

Since the table to be displayed uses the ticketTypeName as the head of each column, rather than try to open them all with the same query, I would just open the ticketType table and dump all of that data into a large array such that type(1) = "halfday", type(2) = "fullday" etc. Then open the other table, and order by priceCodeTitle AND ticketTypeID. Then you should be able to scroll through this recordset, and put the correct prices in the correct columns etc. Does this make sense?

Jared