Question Restuarant Database

[sammy204]

Hi I am trying to create a restaurant's database. I have 2 main tables menu (food) and then another table for order.

I have built a relationship so that on the order data base there is a lookup box which accepts multiple values, and so I just click the items being ordered. However how can I make the lookup box accept different quantities e.g. if the order has 2 of the same item of coca cola.

Thanks for any ideas. Please tell me where to put any new fields etc.

I have had a few tries but no luck

Thanks in advance

 

[masterkale]

Why dont you add another field in called quantity and default the value to 1, so then if they have more then 1 "Coke" they can just rverse tab back or go back and change it to the amount the cusomter is buying.

I dont know exactly how you have set this out.
But i would most likely do the table structure like this.
TblFood
Food_ID
Food_Name
Food_Description
Food_Cost

TblTransaction
Transaction_ID
Transaction_Table_Number
Transaction_Total
Transaction_Paid
etc

TblTransactionFood
Transaction_Food_ID
Transaction_ID
Quantity
Food_ID
Cost
Discount
Total

Hope that helps.
Tristan

 

[sammy204]

Hi, Thanks for the help

Another Question:

How can I make it so if i select a particular food in the transaction , it will fill in a second field for Price of that food, by looking it up in the food table.

 

[masterkale]

Simply make the Combo box have more then one column and set the column widths to 0. Ounce you have done that click on the combo box go to afterupdate event. go into the vba then you would type something like this depending on your db.

txtcost.value = cmbfoodsale.column(2)

Cheers
Tristan

 

[sammy204]

Hi, Thanks again for your help

I have one last question (probably the last )

If I have a table for the lookup box, when I write the code you gave me, it works fine. If I use a Query for the lookup box it does not work though. I dont know if it is something to do with selecting the second column in the lookup?

It is a lookup for the food and the price, with price as second column. I want the food to show up in the field, which it is, and I want to be able to select the price by using the VBA code, which it is not. This column is a query lookup. Thanks

P.S. It needs to be a query lookup, unless there is a way make a lookup only select a certain selection of fields from a table.


Many Thanks for your help, It has been very useful and helped me greatly

 

[masterkale]

Hi, Sammy

You lost me there with that.

You should be able to set the combo box to a query like this.

Select foodID, FoodName, FoodPrice from food

Well something like that anyway.
In VBA columns start off at 0 not 1.
So if the combo box actually has 3 columns like above, it is actually column 2 you will be taking the foodprice from.

Cheers
Tristan F

 

[sammy204]

Hi thanks I have made good progress now.

Now i have decided to make a Booking system for customers at the different tables. I have a tables table and a bookings table.

Tables tbl: Table number, Table Status
Bookings tbl: Surname, Booking ID, Table Number (a lookup box with table number and status)


I want this to make it so if i select a table with the status as "In use" it does not let me choose this table, could maybe do this with a simple query lookup box.
I Then want it to change the status on the tables tbl if i select a table which is free by updating the status to In Use.

How can i do this.

Thanks