database for a restaurant

[gleber]

Hi there,



I’m new here on the forum and on Access and facing a problem that’s look like impossible for mes.

I work for a restaurant and every day the waiter staff generates quite a few paper “guest check”. These checks have:
Check number (#####)
Date (LONG)
Time
Server (Server 1, Server2, …)
Table (01, 02, 03,…)
Payment method (CASH, VISA, MC, AMEX, GIFT CERTIFICATE, OTHER)
Discount (0% - 100%)
Item Quantity
Item
Unit Price
Sub total (before tax)
Total (after tax)

So, I have all this data in excel but I really can’t do what I need.

I need to be able to:
Generate reports with the most sold items by date (day / month / period)
Find the totals for each waiter/waitress (Server)
Generate the totals for the day / month / period

I’m reading MS Access 2007 Bible about normalization and I still cant understand the best way to design my tables and forms.

My first challenge is “how can I have one line in my table AND in the form for a ticket with many items?”

Attached is a practical example of the data we collect



Notice we are using many rows for the same ticket, repeating date, time, table, server, discount and payment method!
The fields for subtotal are calculated ([Item Quantity*Unit Price]-[discount]) and the total (Subtotal+TAX).
Also, we are not getting the total for the ticket!


Please, any help is really welcomed!

Best regards

[RuralGuy]

You have a Ticket table and a LineItem table. The LineItem table has the PrimaryKey of the Ticket table in a ForeignKey field so that there can be many LineItems for each Ticket and therefore a 1:m relationship with the Ticket table. Each table has an AutoNumber field as the PrimaryKey field and the ForeignKey field in the LineItem table will be a LongInteger. You use a Form/SubForm to display the Ticket/LineItems and set the LinkChild/MasterFields to the TicketID PK and FK and Access will fill that in for each LineItem record. Post back if you need more assistance.

[gleber]

Hey RuralGuy,
Thank you for the fast and right to point the answer. BEAUTIFUL!!!
It worked just as I wanted.
I can’t believe 3 nights have being resolved in 3 minutes
Thank you so much!
Happy New Year

[RuralGuy]

Glad I could help and Happy New Year back at you.

[gleber]

Here is the situation now:
I have a table called t_Ticket where the main info for the ticket will be stored. There is a field called ticket_total where I need to store the total for that ticket (on the subform fsub_LineItem I have quantity and unit price) so here is my formula for that field.
TICKET_TOTAL = [t_LineItem]![QUANTITY]*[t_LineItem]![PRICE] – [t_Ticket]![DISCOUNT]

SITUATION 1 – how can I have the subform to show on the ITEM column as a combobox with the values from the table t_MenuItem?
SITUATION 2 – how to get the [PRICE] values from t_MenuItem table on the subform fsub_LineItem when I select the [ITEM]
SITUATION 3 – how to fix the TICKET_TOTAL formula to be capable to calculate several rows since the TICKET_TOTAL is the total of all items for the ticket minus the DISCOUNT. Actually I wish to have this total on the form every time I add or delete an item on the subform fsub_LineItem in realtime and as a label so the user can’t change it manually. Oh! Before somebody comment about not to store this field, I need to because Menu Items may change prices and I’ll need keep it for further reports.

My database is attached.

Please, I appreciate any help

Gleber

[RuralGuy]

One of the main reasons I am on this forum is because I like to help people learn and watch the process. Sometimes you can cajole me into doing a particular fix because it is easier than trying to talk someone through it in the forum. If you supply us with your effort so far we will usually offer suggestions on how to get further into the project. When you supply a laundry list of things you want *your* system to accomplish it shouts something else.

[gleber]

Hi RuralGuy,
Sorry if it looks like I want you guys to do the whole job for me. I’m reading this Access 2007 Bible book but I can’t find on it solutions for this problem. Before I ask for help I search all over the net and on this forum. The only reason I attached the DB file is just to give you guys a better idea of what I’m trying to accomplish here.
Acctually, after my last post finally I found a way to calculate a field called TOTALS on a query
TOTALS: ([T_LINEITEM].[QUANTITY]*[T_LINEITEM].[PRICE])-([t_ticket].[discount])
So this give me the total for each row of but that minus -([t_ticket].[discount]) is going wrong.
So, I’m just asking for the right path.
Thanks again
Best regards
Gleber

[RuralGuy]

Try putting the LineItem SubTotal in a query of the LineItem table so the each line has a SubTotal.

[gleber]

Allan,

Thanks for the input... I still lost in the dark but trying to understand what that means.

I can create a query to calculate the subtotal like the picture attached show but how can I store a calculated value in a table OR how can I take the results and do a new calculation like:

(subtotal1+subtotal2+subtotal3+...+subtotalN)-t_Ticket.DISCOUNT

Thanks again and sorry if I sound like a dumb

Gleber

[RuralGuy]

This is my query for the SubForm:
SELECT t_LineItem.LineItemID, t_LineItem.TicketID, t_LineItem.QUANTITY, t_LineItem.ITEM, t_LineItem.PRICE, [Price]*[Quantity] AS SubTot
FROM t_LineItem;
Why do you have criteria in your query?

[gleber]

This is my query for the SubForm:
SELECT t_LineItem.LineItemID, t_LineItem.TicketID, t_LineItem.QUANTITY, t_LineItem.ITEM, t_LineItem.PRICE, [Price]*[Quantity] AS SubTot
FROM t_LineItem;
Why do you have criteria in your query?

Because the idea was to find in the subform the rows with the same Ticket_num.

Let's say I have the inputs on the subform:

1 | 0993 | 2 | coke | $1.5

so in the t_LineItem:
1 - is the actual [LineItemID]
0993 - is the [Ticket_num]
2 - [QUANTITY]
coke - [ITEM]
$1.5 - [PRICE]

from that I have several inputs in the t_LineItem:
1 | 0993 | 2 | coke | $1.50
2 | 0993 | 1 | chickenwings (count 20) | $10.00
3 | 0993 | 1 | KeyLime Pie | $2.90
4 | 0995 | 1 | Pizza Cheese (slice) | $3.00
5 | 0995 | 1 | Fanta | $1.50

So from the [ticket_num] "0993" I need to select all the rows with the same [ticket_num] and calculate my subtotal for each row than sum the subtotal for each row. Thats my ACTUAL SUB TOTAL. from that I still to have to find on the table t_Ticket the same [ticket_num], find the [DISCOUNT] to subtract than I have to add [t_texes].[tax] to show on the form f_Ticket the GRANDTOTAL and storage that value.

so... that's the whole idea

[RuralGuy]

I'm afraid I'm out of poop. I need to hit the hay and recharge my batteries. I'll pick it up again tomorrow.

[RuralGuy]

Are you aware of the fact that SubForm's do most of the work for you? The SubFormControl has LinkChild/MasterFields properties that keep the SubForm in sync with the MainForm. In addition, when you add new records to the SubForm it will fill in the LinkChildField for you. This means that done properly, the SubForm displays *only* the LineItems of the Ticket of the MainForm.

[gleber]

Hi Allan,
Thank you again for all the time you have helped with my DB. Yesterday I toke the day to study more about and read the book I have about access.
I got some calculation going on the subform fsub_ItemLine such as the subtotal and total. I still don’t get it; how to select an item and bring the price automatically to the next column.



My DB is attached so maybe you can take a look if you got the time.

Thanks in advance again.

Gleber

[RuralGuy]

I downloaded the db but I will be busy until tomorrow night. Sorry.