Any SQL masters out there

This seems to be a very difficult sql statement to construct and any ideas or
help is appreciated. I have a table in Access that has a checkin date, checkout
date, room number, last name and first name, and yes/no checkbox for whether
the guest is a patient. I need to make an HTML table that displays the number
of guests checked in by floor per day and the number of patients per floor by
day. The first two digits of the room number designate the floor number(i.e.
2617 means the 26th floor). The output needs to look like this<BR><table border
= '1'> <tr><td colspan='3' align='center'>26th floor</td> <td colspan='2'>27th
floor</td> <td colspan='2'>28th floor</td> </tr> <tr> <th>Day</th>
<th>patients</th> <th>guests</th> <th>patients</th> <th>guests</th>
<th>patients</th> <th>guests</th> </tr> <tr> <td>1</td> <td>25</td> <td>43</td>
<td>21</td> <td>56</td> <td>19</td> <td>27</td></tr><tr> <td>2</td> <td>26</td>
<td>21</td> <td>68</td> <td>26</td> <td>26</td> <td>19</td> </tr> </table> I
thought about a recordcount for each floor, but I am not sure how to display
the day and then do it all in the same row for each floor. Thank you for help.

Question: Why Access?

From what I understand there is an issue using Access. That issue is that if
Access is used in an environment where there are more than 6-8 users at a
time the system will faulter.

Most Web hosting companies provide MySQL, which is similiar to SQL in many
respects--(I've only worked with MySQL so as to the differences I can't
say).

What you are trying to do is fairly simple--once you get a handle on
building databases. The thing to do is to normalize the tables (if you
haven't already). DW will assist you in creating the inquiries.

If you happen to be using PHP, I'd suggest getting a copy of "PHP Web
Development, Dreamweaver 2004," by Allan Kent and David Powers. It will give
you an excellent foundation to do what you are doing.


"Tulsa" <webforumsuser (AT) macromedia (DOT) com> wrote

This seems to be a very difficult sql statement to construct and any ideas
or
help is appreciated. I have a table in Access that has a checkin date,
checkout
date, room number, last name and first name, and yes/no checkbox for
whether
the guest is a patient. I need to make an HTML table that displays the
number
of guests checked in by floor per day and the number of patients per floor
by
day. The first two digits of the room number designate the floor
number(i.e.
2617 means the 26th floor). The output needs to look like this<BR><table
border
= '1'> <tr><td colspan='3' align='center'>26th floor</td> <td
colspan='2'>27th
floor</td> <td colspan='2'>28th floor</td> </tr> <tr> <th>Day</th
th>patients</th> <th>guests</th> <th>patients</th> <th>guests</th
th>patients</th> <th>guests</th> </tr> <tr> <td>1</td> <td>25</td
td>43</td
td>21</td> <td>56</td> <td>19</td> <td>27</td></tr><tr> <td>2</td
td>26</td
td>21</td> <td>68</td> <td>26</td> <td>26</td> <td>19</td> </tr> </table
I
thought about a recordcount for each floor, but I am not sure how to
display
the day and then do it all in the same row for each floor. Thank you for
help.

It's made a little more complex because you want to display two days per
row.
Use a nested repeat region and a horizontal looper.
The primary repeat region retrieves the floors and the nested repeat region
displays the information pertaining to the floor (i.e. patients and guests).
This would be simple if you didn't have to display two days of data in the
same TR.

"Tulsa" <webforumsuser (AT) macromedia (DOT) com> wrote

This seems to be a very difficult sql statement to construct and any ideas
or
help is appreciated. I have a table in Access that has a checkin date,
checkout
date, room number, last name and first name, and yes/no checkbox for
whether
the guest is a patient. I need to make an HTML table that displays the
number
of guests checked in by floor per day and the number of patients per floor
by
day. The first two digits of the room number designate the floor
number(i.e.
2617 means the 26th floor). The output needs to look like this<BR><table
border
= '1'> <tr><td colspan='3' align='center'>26th floor</td> <td
colspan='2'>27th
floor</td> <td colspan='2'>28th floor</td> </tr> <tr> <th>Day</th
th>patients</th> <th>guests</th> <th>patients</th> <th>guests</th
th>patients</th> <th>guests</th> </tr> <tr> <td>1</td> <td>25</td
td>43</td
td>21</td> <td>56</td> <td>19</td> <td>27</td></tr><tr> <td>2</td
td>26</td
td>21</td> <td>68</td> <td>26</td> <td>26</td> <td>19</td> </tr> </table
I
thought about a recordcount for each floor, but I am not sure how to
display
the day and then do it all in the same row for each floor. Thank you for
help.

I might have made the table wrong. There is only one day per row, with each floor's guest count on that row. What did you have in mind for this? Thanks

A nested repeat region will give you output like below.

26th Floor
Day 1 26 patients 12 guests
Day 2 17 patients 11 guests
Day 3 20 patients 9 guests
27th Floor
Day 1 12 patients 6 guests
Day 2 11 patients 12 guests
Day 3 9 patients 7 guests
28th Floor
....

I don't know what server model you are using, but here are some instructions
on how to do it with ASP:
http://www.dmxzone.com/showDetail.asp?TypeId=2&NewsId=394


"Tulsa" <webforumsuser (AT) macromedia (DOT) com> wrote

I might have made the table wrong. There is only one day per row, with
each floor's guest count on that row. What did you have in mind for this?
Thanks