More Advanced Query Assistance

[rnorthcott]

Good morning,

I'm looking for some assistance with a couple what I'm thinking are slightly more advanced queries for the registration database I'm working on for a client.

Firstly, I have a table that tracks reservations made by visitors, which is linked to a table of information related to the people who are attending using that particular reservation. When I look at the parent table in table view, I can expand the reservation row to view the related information from the sub table, as seen in the screenshot below. Is it possible to reproduce this resulting view using a query?

Secondly, my client has asked that she be able to run a query that will report the total number of each entrance fee purchased. I can run a query that totals the number of visitors that purchased each type of entrance fee, but it doesn't take into consideration the length of time the visitor was in the park. For example, if I stay for 7 days and purchase a day pass for each day that should contribute 7 day pass purchases to the total not just one. My first thoughts would be to do something like this algorithm, just not sure exactly what it would look like.

Set a counter variable for each daily entry type to 0
Collect the list of visitors who entered the park
Step through each visitor and determine what entry fee type they paid, the length of their stay (by subtracting the Start Date from the End Date and adding 1), and if they purchased a daily entry fee, add the product of those two numbers to the correct counter variable
Display the results

Perhaps there is a better way to do it as well. The two tables involved are tblReservations that contains the ReservationNumber, Start Date and End Date and tblVisitors that links to the primary table via ReservationNumber and contains the fee type each visitor paid.

Let me know if you need any additional information, and I appreciate any help that can be provided.
Raymond

 

[LarryE]

Would you please post a screenshot of your table and relationship window so we can at least see the design as it is now. From there, someone can help you further.

 

[isladogs]

Yes you can add subdatasheets to queries as well as tables.
In fact you can have more than 1 level of subdatasheet using tables or queries as the data source
However, I would advise against doing so for several reasons:
1. Both the main table/query and all subdatasheets need to be loaded - this uses up resources and can drastically affect performance if there are many records
2. Users generally find them confusing
3. Users shouldn't be working directly with tables or queries but only with forms

Therefore I would strongly recommend using a form and subform arrangement instead

For the second part, I agree with @LarryE about seeing a screenshot of table relationships to give a definitive answer.
However, you can easily determine the number of days and therefore day passes by calculating the difference between the EndDate & StartDate in a query

SELECT ID, StartDate, EndDate, [EndDate] - [StartDate] AS Days
FROM Table1;

 

[rnorthcott]

Thanks for the additional information and suggestions about subdatasheets and forms/sub forms. I will definitely put some thought into that.

Here is the screenshot of the relationship window.

Let me know if there is any additional information I can provide.

Raymond

 

[DHookom]

Does each Group Member pay a fee or is the fee per group? If the start date is February 1st and the end date is February 2nd, is that one day or two? How does Fee Type affect the desired result?

 

[mike60smart]

Thanks for the additional information and suggestions about subdatasheets and forms/sub forms. I will definitely put some thought into that.

Here is the screenshot of the relationship window.

View attachment 123176

Let me know if there is any additional information I can provide.

Raymond

Hi Raymond
Could you upload a copy of the database with no confidential data?

 

[LarryE]

I think we have all we need to know and you are not the first person to have two tables of data that are not related and wonder how to get analytical information out of them. First, you do not have a relational database here. You have two tables with some information. That's all.

It appears you are wanting to develop some kind of camp site reservation system. Please describe what this is supposed to be and how the reservation system works right now. What is the process workflow? Once we know what you are attempting keep track of and how the process is supposed to work, then we can get some idea of how to help you develop the database design. The design of these systems always follows the workflow process. So please describe the process and we can certainly provide recommendations for design.

 

[rnorthcott]

Does each Group Member pay a fee or is the fee per group? If the start date is February 1st and the end date is February 2nd, is that one day or two? How does Fee Type affect the desired result?

Each group member pays the fee. Start date of February 1st and end date of February 2nd is two days. The fee types are either seasonal or daily, so it would only be the daily types that would need the calculation performed. Hopefully that answers your questions.

 

[rnorthcott]

Thanks for your help so far. I feel like there is going to be more time and effort involved in obtaining the specific information they are looking for than they are going to want to invest, especially since the existing system meets 98% of their needs. I will discuss this with them further and go from there.

Thanks again.

Raymond

 

[DHookom]

This is a very simple query once the specifics are made known.
Are you looking for a date range?
What are the potential value is the Fee Type field? Normally there would be a small table of Fee Types with a combo box to select the appropriate type. A simplified query with one amount of days would be:

SELECT Sum(DateDiff("d",[Start Date],[End Date])+1) AS Days
FROM tblReservations INNER JOIN tblGroupMembers
  ON tblReservations.ReservationNumber = tblGroupMembers.[Reservation Number]
WHERE tblGroupMembers.[Fee Type]=1;

This assumes the daily fees are Fee Type 1.

 

[LarryE]

Ran another one off with talk of doing it correctly from the beginning. It does take some time, some knowledge of relational models and knowledge of the entire work process. That is true.

 

[tvanstiphout]

Thanks for the additional information and suggestions about subdatasheets and forms/sub forms. I will definitely put some thought into that.

Here is the screenshot of the relationship window.

View attachment 123176

Let me know if there is any additional information I can provide.

Raymond

That relationship is no good. Right-click the line and select "Enforce". Only then will it actually do the job of making sure all GroupMembers have a valid ReservationNumber.

 

[Ken Sheridan]

Secondly, my client has asked that she be able to run a query that will report the total number of each entrance fee purchased. I can run a query that totals the number of visitors that purchased each type of entrance fee, but it doesn't take into consideration the length of time the visitor was in the park. For example, if I stay for 7 days and purchase a day pass for each day that should contribute 7 day pass purchases to the total not just one.

I've attached a little demo file for reserving hotel rooms. In this think of occupants as analogous to visitors in your case, rooms as analogous to type of booking. For each booking the total cost is returned with the following function:

Public Function TotalCost(intRoomNumber As Integer, dtmFrom As Date, dtmTo As Date) As Currency

    Dim dtmDate As Date
    Dim curCost As Currency
    Dim strCriteria
   
    For dtmDate = dtmFrom To dtmTo
        ' build criteria to get cost for date
        strCriteria = "RoomNumber = " & intRoomNumber & _
            " And #" & Format(dtmDate, "yyyy-mm-dd") & "#" & _
            " BETWEEN EntryDate AND DepartureDate"
        ' increment cost by cost for date
        curCost = curCost + DLookup("CostPerNight", "qryRoomCosts", strCriteria)
    Next dtmDate
   
    TotalCost = curCost

End Function

To return the total number of nights booked, and the total payment received over a date range entered at runtime the following query could be used:

PARAMETERS [Date From:] DATETIME,
[Date To:] DATETIME;
SELECT
    [Date From:] AS DateFrom,
    [Date To:] AS DateTo,
    RoomNumber,
    SUM(DepartureDate - EntryDate) AS NumberOfNights,
    SUM(Cost) AS TotalReceived
FROM
    RoomOccupations
WHERE
    DepartureDate >= [Date From:]
    AND EntryDate < [Date To:]
GROUP BY
    RoomNumber;

The physical model for the database is as below:

Note that Calendar and Calendar_1 are not separate tables, but two instances of the single table Calendar. This is simply a table of all dates over a period of years. As you'll see the model is made up of five related tables, each of which have relatively few columns. Yours, on the other hand, has only two tables, each of which has a large number of columns. To enable you to return the sort of results which your client requires you would need to decompose your tables into a set of correctly normalized tables. Each table should represent a distinct real world entity type, or a relationship between entity types, and the columns in each table should represent attributes of the entity type being modelled by the table, and, for the table to be correctly normalized to at least Third Normal Form, which are determined solely by the table's primary key. If you are unfamiliar with the process of database normalization, look it up online.