Save me from myself (1 Viewer)

[betheball]

This may be bad database design, but that ship has sailed. I have a table that stores reservations, such as for a hotel or restaurant. I decided the best route to go was create a new record for each room/table reserved. This works beautifully, until one person reserves several rooms or tables. Then I am stuck with several records that are identical save the room or table number. What I am hoping to do is design a query that can make those several records look like one. Meaning I want the query to return just one name, date, username, etc. along with all the room/table numbers. Any ideas on how I can achieve this?

 

[WayneRyan]

betheball,

Look at the Group by clause.

Select a,b,c
From SomeTable
Group by a;

Wayne

 

[betheball]

I am familiar with the GROUP BY clause, but if I try and also select the field that does have unique values, Access gives me an error because I didn't also include that field in the GROUP BY clause, however, if I add it, I then get the repeating values from all the other fields. I am hoping to come up with something that will look like one single record with the unique values showing perhaps as a comma-delimited string or something to that effect.

Here is an example of what I tried:

SELECT Reservations.txtName, Reservations.Email, Reservations.PhoneNumber, Reservations.Site
FROM Reservations
GROUP BY Reservations.txtName, Reservations.Email, Reservations.PhoneNumber

When I run it, I get an error that Reservation.Site is non part of an aggregate function. Site is the field that has unique values.

 

[WayneRyan]

betheball,

Oh, I get it now. There was a thread here a while ago where someone
wanted a report like that.

I'll go find it.

Wayne

 

[ddmcn]

Key value

What is the key field for the reservations table? Do you need to group by all three of the criteria?

 

[betheball]

There is a Reservation_ID field. For example, I may have four records that are identical except for the room reserved and the reservation ID. However, I want to get the User's name, email, and the rooms reserved. Currently, the only way to do that is to get the user's name, email etc. for each room reserved. Does that make sense?

I am trying to display the data on a ASP page and would like to display one instance of the user name, email and then each room reserved.

 

[ddmcn]

Are the users...

consistent...in other words are they repeat users that would be better kept in a separate table?

 

[betheball]

repeat users

Possible, but not likely. I agree that perhaps the separate table would be a better answer, although I'm not sure it would solve the underlying problem, which is how to return several room reservation as one record. Even if I take the repetitious data out, each room reserved is in a separate record so if I do a join with a users table I will still have the repetition problem, right? I thought about storing all rooms reserved in one field as a comma-delimited string, but that presented its own set of problems. I would love to see how hotels, airlines and other companies that deal with reservations handle this type of situation.

 

[ChrisO]

G’day betheball.

Not really sure if this is something like what you want: -

Option Explicit
Option Compare Text


[color=green]'  SELECT Concatenate() AS ResetValue,
'         tblNames.FirstName,
'         Last(Concatenate([RoomNumber])) AS ConcatenatedValue
'  FROM tblNames
'  GROUP BY Concatenate(),
'           tblNames.FirstName
'  HAVING tblNames.FirstName = "Fred"[/color]


Public Function Concatenate(Optional strCurrentValue As Variant) As String
    Static strRunningValue As String

    If IsMissing(strCurrentValue) Then
        strRunningValue = ""
    Else
        strRunningValue = strRunningValue & ", " & strCurrentValue
        If Left$(strRunningValue, 1) = "," Then
            strRunningValue = Right$(strRunningValue, Len(strRunningValue) - 2)
        End If
    End If
    
    Concatenate = strRunningValue

End Function

Probably not the most efficient way but hope it helps.

Simple A97 demo attached.

Regards,
Chris.

 

[ChrisO]

Explanation of above Query and code...

****
The Query is based on a subset of the Table that complies with True. (FirstName = "Fred")

When the query first runs it calls Concatenate() AS ResetValue but passes no argument, it therefore only runs once.
Function Concatenate() tests for the missing argument and resets Static strRunningValue to "", a ZLS.

On second and subsequent calls, it tests for a leading "," and removes it, if present.

Thereafter as the query goes through the Table it passes the Last(Concatenate([RoomNumber])) AS ConcatenatedValue.
Because it is passing an argument (RoomNumber) it runs for each record.

On each call to Function Concatenate() the argument is appended to
strRunningValue and is retained between calls because it's a Static variable.

strRunningValue is then returned to the query and saved in field ConcatenatedValue.

When done, only the Last field in the query is shown.
****

There are probably other ways to do this but I’m no good at SQL and I suspect never will be.

And don’t really care. It is far, far too late for me to get good at anything new.
Will simply polish that which I think I know, and this is not one of them.

Regards,
Chris.

 

[ddmcn]

Hmmm...another table

Possible, but not likely. I agree that perhaps the separate table would be a better answer, although I'm not sure it would solve the underlying problem, which is how to return several room reservation as one record. Even if I take the repetitious data out, each room reserved is in a separate record so if I do a join with a users table I will still have the repetition problem, right? I thought about storing all rooms reserved in one field as a comma-delimited string, but that presented its own set of problems. I would love to see how hotels, airlines and other companies that deal with reservations handle this type of situation.

might help. First, the table would identify users by a key field (userID) and that userID could be used to identify which records in the reservations table would be related to the user. A form or report could be used to display the user information with a sub form or sub report used to display specific reservation info.

In a query, the records would be repeated but on a report or form the user records could be set up to be displayed only once.

Make sense?

 

[betheball]

might help. First, the table would identify users by a key field (userID) and that userID could be used to identify which records in the reservations table would be related to the user. A form or report could be used to display the user information with a sub form or sub report used to display specific reservation info.

In a query, the records would be repeated but on a report or form the user records could be set up to be displayed only once.

Make sense?

Thanks Dennis, it does make sense. I will play with it a bit further. I am actually displaying the data via asp. What I may do is instead of trying to display each room, just display the number of rooms and have it hyperlinked to a separate page so that when the user clicks the link a popup displays the actual room numbers. That way, one query gets me the user information and a second is used to get the specific rooms.

Appreciate the input.