trouble displaying query results in a form

[crazyrat25]

Hi, I am working on a project with my students and I am struggling with one issue in our system. We have a (fake) database for a camping company. We have got the booking system for campsites working great but we have to create an additional section to the system whereby guests can 'logon' to the system using a password that is stored in the customers table with their personal information to book a meal and meal time slot. I have tried to do this with a parameter query on the member password field but



The meal information is stored in a separate table. Ultimately we have to create an invoice for the camping stay for each guest so need the meal selections added to the booking table I think?

Everytime I create a query that will load a form with the customer information linked to the meals, I get no results which I assume is because the meal information is blank/not associated with the customers? I have tried Is Null for the blank fields and "" but I cannot get it to work!

I have screenshots of the system if that helps.



Thanks in advance!

[orange]

I would recommend that you do not have embedded spaces in field or object names. It will cause syntax issues at some point.

Is Date of Booking when the "reservation was made", or is it the Date on which the "Stay" starts? You may consider a field for each of these.

Where is your Meals table? Why is it not in your relationships diagram?

[crazyrat25]

The Date of booking is for when the stay begins. As this is school project There is no requirement for the date the reservation was made (although I understand in reality this would be near essential). The Meals table is actually the menu table. This is given by the exam board. The description field contains the different menu items.

[orange]

OK. We don't do homework ( none of the forums do), but if you ask specific questions and show that you are putting in an honest effort, people here will help.

What is the specific issue at this time?
What is an example (or 2) of the Description field?

[crazyrat25]

Thanks - not looking for someone to do my work, just help with this issue. I have tried to run the query in the screenshot to 'search' by password for a user. My hope would be to generate a form that would have the customer details on and allow me to select a meal option for that customer, feeding into the booking table so that I can generate the overall invoice. I am fine with booking the campsite - that's already done and I'm fine with making an invoice - I've done it before but cannot get this query to work! In my head it makes sense but obviously in Access it doesn't work the same way!

I've also screenshot the menu table

[orange]

It appears that your current table structure only allows 1 meal/menu per Customer. Could you please describe/explain what would actually happen if a Customer was on a 3 day stay -assume 3 meals per day. How exactly is that handled in your set up?

[crazyrat25]

Ah, does this mean I need some sort of 'meals' table to record the meal orders? I assume this would allow me to have a many to many relationship?

[orange]

Do you understand Many to Many?

1 customer could select many Meals
1 meal could be selected by many Customers

Resolving Many to Many

[crazyrat25]

Now I'm confused again! Maybe I don't understand many to many properly! I've just created a 'meals' table to store orders from the menu and customers but I'm not sure if this will work?

[orange]

I was thinking you could consider a table such as:

tblCustomerHasMeal
CustomerID---PK of Customer
MenuId --------PK of Menu
mealDate (must be within current booking period)
whichMeal (Breakfast /Lunch /Dinner)

From a validation perspective you might check that each meal for each Day of Stay has been considered.
You might have a separate PK say CustomerHasMealID in tblCustomerHasMeal.
Then you may wish to review composite unique index to see if it is relevant to your situation.

It is advisable to create some test data and ensure your model meets your requirement. If there are any issues, review and review to ensure every issue is resolved --could be bad data, missing table, bad relationship...... solve the issue before moving on.

Another important approach, and we may have overlooked it in this discussion, is:

Write a clear description of the business issue/opportunity in plain English. Start at the 30,000 ft level and gradually add details. Bounce it off some co-workers to ensure all aspects and facts are consistent and truly represent the business issue to be resolved. This can/should become a reference specification for what you are trying to do/support. Too often people start development and don't have a clear statement of what exactly they are trying to resolve.