Help! Problem on the final stage of a restaurant database

[Paul T. RONG]

Hello,

I am making a restaurant database (it is much more complicated than I
thought before!), now it comes to the last stage and I come across a
problem. I will explain it in detail. In a restaurant, though rarely, but it
happens, that one guy from desk1 would like to pay for bills of both desk1
and desk2 (I avoid using Access term "table"), that means the dishes that
ordered seperately before by desk1 and desk2 now have to be commixed
together under desk1 or desk2.

for example, there are following dishes ordered, under desk1:

OrderID OrderDetailID DishID price quantity
discont
759 456 Pizza brocoli 219,00 1 0,00%
759 457 Pizza Napoli 129,00 2 0,00%
759 458 Pizza Ham 109,00 1 0,00%
759 459 Cigarette 65,00 1 0,00%
and under desk2:

OrderID OrderDetailID DishID price quantity
discont
760 460 Pizza brocoli 219,00 1 0,00%
760 461 Pizza Ham 109,00 1 0,00%
760 462 Cigarette 85,00 1 0,00%
760 463 Wine 250,00 1 0,00%
when they are commixed together, the tblOrderDetail which is the source of
the subform on the frmOrder could theoretically look like:
OrderID OrderDetailID DishID price quantity
discont
759 456 Pizza brocoli 219,00 1 0,00%
759 457 Pizza Napoli 129,00 2 0,00%
759 458 Pizza Ham 109,00 1 0,00%
759 459 Cigarette 65,00 1 0,00%
759 460 Pizza brocoli 219,00 1 0,00%
759 461 Pizza Ham 109,00 1 0,00%
759 462 Cigarette 85,00 1 0,00%
759 463 Wine 250,00 1 0,00%
I came to this step, however practically what the users like more is a
no-repeat-DishID version, and each dishs' quantity added together
respectively, note that Cigarette has different prices (65.00 and 85.00),
because the price of the cigarettes (so is the wine) is changing so often
that the cigarettes don't have their fixed prices:

OrderID OrderDetailID DishID price quantity
discont
759 456 Pizza brocoli 219,00 2
0,00%
759 457 Pizza Napoli 129,00 2
0,00%
759 458 Pizza Ham 109,00 2
0,00%
759 459 Cigarette 65,00 1
0,00%
759 462 Cigarette 85,00 1
0,00%
759 463 Wine 250,00 1
0,00%

thank you for your time and your patience, I have been working very hard for
several days, but still haven't found a good solution to the problem. Any of
your help will be greatly appreciated.

Paul

[Dave]

Have you tried using a summation query that groups by item ID (I suppose
Pizza Ham has ID 123 or something like that)?

"Paul T. RONG" <et***@hotmail.com> wrote in message
news:Ut*****************@news.chello.at...

Hello,

I am making a restaurant database (it is much more complicated than I
thought before!), now it comes to the last stage and I come across a
problem. I will explain it in detail. In a restaurant, though rarely, but it happens, that one guy from desk1 would like to pay for bills of both desk1
and desk2 (I avoid using Access term "table"), that means the dishes that
ordered seperately before by desk1 and desk2 now have to be commixed
together under desk1 or desk2.

for example, there are following dishes ordered, under desk1:

OrderID OrderDetailID DishID price quantity
discont
759 456 Pizza brocoli 219,00 1 0,00% 759 457 Pizza Napoli 129,00 2 0,00% 759 458 Pizza Ham 109,00 1 0,00% 759 459 Cigarette 65,00 1 0,00%

and under desk2:

OrderID OrderDetailID DishID price quantity
discont
760 460 Pizza brocoli 219,00 1 0,00% 760 461 Pizza Ham 109,00 1 0,00% 760 462 Cigarette 85,00 1 0,00% 760 463 Wine 250,00 1 0,00%

when they are commixed together, the tblOrderDetail which is the source of
the subform on the frmOrder could theoretically look like:
OrderID OrderDetailID DishID price quantity
discont
759 456 Pizza brocoli 219,00 1 0,00% 759 457 Pizza Napoli 129,00 2 0,00% 759 458 Pizza Ham 109,00 1 0,00% 759 459 Cigarette 65,00 1 0,00% 759 460 Pizza brocoli 219,00 1 0,00% 759 461 Pizza Ham 109,00 1 0,00% 759 462 Cigarette 85,00 1 0,00% 759 463 Wine 250,00 1 0,00%

I came to this step, however practically what the users like more is a
no-repeat-DishID version, and each dishs' quantity added together
respectively, note that Cigarette has different prices (65.00 and 85.00),
because the price of the cigarettes (so is the wine) is changing so often
that the cigarettes don't have their fixed prices:

OrderID OrderDetailID DishID price quantity
discont
759 456 Pizza brocoli 219,00 2
0,00%
759 457 Pizza Napoli 129,00 2
0,00%
759 458 Pizza Ham 109,00 2
0,00%
759 459 Cigarette 65,00 1
0,00%
759 462 Cigarette 85,00 1
0,00%
759 463 Wine 250,00 1
0,00%

thank you for your time and your patience, I have been working very hard for several days, but still haven't found a good solution to the problem. Any of your help will be greatly appreciated.

Paul

[Tom van Stiphout]

On Tue, 23 Nov 2004 23:12:52 GMT, "Paul T. RONG" <et***@hotmail.com>
wrote:

I might add a "Transfer" feature, where one or more OrderDetailIDs can
be transfered from one order to another. This way a customer can pay
for only the bottle of wine of the other Desk, or for the entire bill.
Transferring an OrderDetailID is as simple as giving it a different
OrderID.

<clip>

[Alan Webb]

Paul,
At our restaurant the cashier is trained to handle each tab as a single
transaction. So if Joey comes up to the cashier with his and his buddies
tab she does two transactions, one for each tab. You do get to a point in
systems analysis where the cost of the work to code a feature can't be
justified by the benefits. A good cashier should be able to gracefully
handle this without having to code anything.
Also, remember that a restaurant is essentially a factory in terms of the
business processes that an information system needs to support. In a
factory orders come in and resources get allocated to fill the orders. It
does happen that there are not enough components on hand to fill all the
orders. This means that it is possible for portions of an order to complete
production while other portions have to go on back-order until there is
sufficient inventory to complete the order. Customers will also call the
plant and ask if several orders can be put on one bill of lading and shipped
together. Your shipping people may recognize that a customer has several
orders due out of production on a given day that could go on the same truck
and thus should be on the same bill of lading. In these cases it is the
shipment that is the grouping upon which an invoice will be generated.
Were I your boss I'd say that this is one best left to the humans. You
could code a merge ticket feature as mentioned in another post. But I
wonder if the effort is worth it. Once a ticket is merged you lose the
ability to see what was ordered on the merged ticket and your chef and
sous-chef might find that irritating when it comes to analyzing how the
business is doing per table or per order.

"Paul T. RONG" <et***@hotmail.com> wrote in message
news:Ut*****************@news.chello.at...

Hello,

I am making a restaurant database (it is much more complicated than I
thought before!), now it comes to the last stage and I come across a
problem. I will explain it in detail. In a restaurant, though rarely, but
it
happens, that one guy from desk1 would like to pay for bills of both desk1
and desk2 (I avoid using Access term "table"), that means the dishes that
ordered seperately before by desk1 and desk2 now have to be commixed
together under desk1 or desk2.

for example, there are following dishes ordered, under desk1:

OrderID OrderDetailID DishID price quantity
discont
759 456 Pizza brocoli 219,00 1
0,00%
759 457 Pizza Napoli 129,00 2
0,00%
759 458 Pizza Ham 109,00 1
0,00%
759 459 Cigarette 65,00 1
0,00%
and under desk2:

OrderID OrderDetailID DishID price quantity
discont
760 460 Pizza brocoli 219,00 1
0,00%
760 461 Pizza Ham 109,00 1
0,00%
760 462 Cigarette 85,00 1
0,00%
760 463 Wine 250,00 1
0,00%
when they are commixed together, the tblOrderDetail which is the source of
the subform on the frmOrder could theoretically look like:
OrderID OrderDetailID DishID price quantity
discont
759 456 Pizza brocoli 219,00 1
0,00%
759 457 Pizza Napoli 129,00 2
0,00%
759 458 Pizza Ham 109,00 1
0,00%
759 459 Cigarette 65,00 1
0,00%
759 460 Pizza brocoli 219,00 1
0,00%
759 461 Pizza Ham 109,00 1
0,00%
759 462 Cigarette 85,00 1
0,00%
759 463 Wine 250,00 1
0,00%
I came to this step, however practically what the users like more is a
no-repeat-DishID version, and each dishs' quantity added together
respectively, note that Cigarette has different prices (65.00 and 85.00),
because the price of the cigarettes (so is the wine) is changing so often
that the cigarettes don't have their fixed prices:

OrderID OrderDetailID DishID price quantity
discont
759 456 Pizza brocoli 219,00 2
0,00%
759 457 Pizza Napoli 129,00 2
0,00%
759 458 Pizza Ham 109,00 2
0,00%
759 459 Cigarette 65,00 1
0,00%
759 462 Cigarette 85,00 1
0,00%
759 463 Wine 250,00 1
0,00%

thank you for your time and your patience, I have been working very hard
for
several days, but still haven't found a good solution to the problem. Any
of
your help will be greatly appreciated.

Paul

[Paul T. RONG]

Tom,

Exactly, but then it is the problem, say the customer pays one bottle of
Four Roses for the other Desk, while he himself drank one bottle of the same
brand, in the bill there should be two bottles of Four Roses:

OrderID OrderDetailID DishID price quantity
discont
759 472 Four Roses 750,00 2
0,00%

how to make the quantity automatically upgrade to 2 is the key problem.

Thank your for your concern and help,

Paul

"Tom van Stiphout" <no*************@cox.net>
??????:iu********************************@4ax.com. ..

On Tue, 23 Nov 2004 23:12:52 GMT, "Paul T. RONG" <et***@hotmail.com>
wrote:

I might add a "Transfer" feature, where one or more OrderDetailIDs can
be transfered from one order to another. This way a customer can pay
for only the bottle of wine of the other Desk, or for the entire bill.
Transferring an OrderDetailID is as simple as giving it a different
OrderID.

<clip>

[Paul T. RONG]

Alan,

Thank you for your commment. I fully agree with you. If I make a software
for myself I would be very happy to stop at this point and let the cashier
to handle two bill or three bill problem.

Unfortunately this database is for a friend who is generelly very nice but
stubborn enough to accept the idea we like.

So I have to trek further,

regards,

Paul


"Alan Webb" <kn*****@hotmail.com> дÈëÏûÏ¢ÐÂÎÅ
:Q-********************@comcast.com...

Paul,
At our restaurant the cashier is trained to handle each tab as a single
transaction. So if Joey comes up to the cashier with his and his buddies
tab she does two transactions, one for each tab. You do get to a point in
systems analysis where the cost of the work to code a feature can't be
justified by the benefits. A good cashier should be able to gracefully
handle this without having to code anything.
Also, remember that a restaurant is essentially a factory in terms of the
business processes that an information system needs to support. In a
factory orders come in and resources get allocated to fill the orders. It
does happen that there are not enough components on hand to fill all the
orders. This means that it is possible for portions of an order to complete production while other portions have to go on back-order until there is
sufficient inventory to complete the order. Customers will also call the
plant and ask if several orders can be put on one bill of lading and shipped together. Your shipping people may recognize that a customer has several
orders due out of production on a given day that could go on the same truck and thus should be on the same bill of lading. In these cases it is the
shipment that is the grouping upon which an invoice will be generated.
Were I your boss I'd say that this is one best left to the humans. You
could code a merge ticket feature as mentioned in another post. But I
wonder if the effort is worth it. Once a ticket is merged you lose the
ability to see what was ordered on the merged ticket and your chef and
sous-chef might find that irritating when it comes to analyzing how the
business is doing per table or per order.

"Paul T. RONG" <et***@hotmail.com> wrote in message
news:Ut*****************@news.chello.at...

Hello,

I am making a restaurant database (it is much more complicated than I
thought before!), now it comes to the last stage and I come across a
problem. I will explain it in detail. In a restaurant, though rarely, but it
happens, that one guy from desk1 would like to pay for bills of both desk1 and desk2 (I avoid using Access term "table"), that means the dishes that ordered seperately before by desk1 and desk2 now have to be commixed
together under desk1 or desk2.

for example, there are following dishes ordered, under desk1:

OrderID OrderDetailID DishID price quantity
discont
759 456 Pizza brocoli 219,00 1
0,00%
759 457 Pizza Napoli 129,00 2
0,00%
759 458 Pizza Ham 109,00 1
0,00%
759 459 Cigarette 65,00 1
0,00%
and under desk2:

OrderID OrderDetailID DishID price quantity
discont
760 460 Pizza brocoli 219,00 1
0,00%
760 461 Pizza Ham 109,00 1
0,00%
760 462 Cigarette 85,00 1
0,00%
760 463 Wine 250,00 1
0,00%
when they are commixed together, the tblOrderDetail which is the source of the subform on the frmOrder could theoretically look like:
OrderID OrderDetailID DishID price quantity
discont
759 456 Pizza brocoli 219,00 1
0,00%
759 457 Pizza Napoli 129,00 2
0,00%
759 458 Pizza Ham 109,00 1
0,00%
759 459 Cigarette 65,00 1
0,00%
759 460 Pizza brocoli 219,00 1
0,00%
759 461 Pizza Ham 109,00 1
0,00%
759 462 Cigarette 85,00 1
0,00%
759 463 Wine 250,00 1
0,00%
I came to this step, however practically what the users like more is a
no-repeat-DishID version, and each dishs' quantity added together
respectively, note that Cigarette has different prices (65.00 and 85.00), because the price of the cigarettes (so is the wine) is changing so often that the cigarettes don't have their fixed prices:

OrderID OrderDetailID DishID price quantity
discont
759 456 Pizza brocoli 219,00 2
0,00%
759 457 Pizza Napoli 129,00 2
0,00%
759 458 Pizza Ham 109,00 2
0,00%
759 459 Cigarette 65,00 1
0,00%
759 462 Cigarette 85,00 1
0,00%
759 463 Wine 250,00 1
0,00%

thank you for your time and your patience, I have been working very hard
for
several days, but still haven't found a good solution to the problem. Any of
your help will be greatly appreciated.

Paul

[Paul T. RONG]

Dave,

I tried to use a summation query that groups by DishID and price, but still
the table "tblOrderDetail" which is the source of
the subform on the form "frmOrder" remains untouched, and the problem
remains.

Thank you,

Paul
"Dave" <dm******@island.net> дÈëÏûÏ¢ÐÂÎÅ:0rQod.315622$nl.134188@pd7tw3no...

Have you tried using a summation query that groups by item ID (I suppose
Pizza Ham has ID 123 or something like that)?

"Paul T. RONG" <et***@hotmail.com> wrote in message
news:Ut*****************@news.chello.at...

Hello,

I am making a restaurant database (it is much more complicated than I
thought before!), now it comes to the last stage and I come across a
problem. I will explain it in detail. In a restaurant, though rarely, but

it

happens, that one guy from desk1 would like to pay for bills of both

desk1 and desk2 (I avoid using Access term "table"), that means the dishes that ordered seperately before by desk1 and desk2 now have to be commixed
together under desk1 or desk2.

for example, there are following dishes ordered, under desk1:

OrderID OrderDetailID DishID price quantity
discont
759 456 Pizza brocoli 219,00 1

0,00%

759 457 Pizza Napoli 129,00 2

0,00%

759 458 Pizza Ham 109,00 1

0,00%

759 459 Cigarette 65,00 1

0,00%

and under desk2:

OrderID OrderDetailID DishID price quantity
discont
760 460 Pizza brocoli 219,00 1

0,00%

760 461 Pizza Ham 109,00 1

0,00%

760 462 Cigarette 85,00 1

0,00%

760 463 Wine 250,00 1

0,00%

when they are commixed together, the tblOrderDetail which is the source of the subform on the frmOrder could theoretically look like:
OrderID OrderDetailID DishID price quantity
discont
759 456 Pizza brocoli 219,00 1

0,00%

759 457 Pizza Napoli 129,00 2

0,00%

759 458 Pizza Ham 109,00 1

0,00%

759 459 Cigarette 65,00 1

0,00%

759 460 Pizza brocoli 219,00 1

0,00%

759 461 Pizza Ham 109,00 1

0,00%

759 462 Cigarette 85,00 1

0,00%

759 463 Wine 250,00 1

0,00%

I came to this step, however practically what the users like more is a
no-repeat-DishID version, and each dishs' quantity added together
respectively, note that Cigarette has different prices (65.00 and 85.00), because the price of the cigarettes (so is the wine) is changing so often that the cigarettes don't have their fixed prices:

OrderID OrderDetailID DishID price quantity
discont
759 456 Pizza brocoli 219,00 2
0,00%
759 457 Pizza Napoli 129,00 2
0,00%
759 458 Pizza Ham 109,00 2
0,00%
759 459 Cigarette 65,00 1
0,00%
759 462 Cigarette 85,00 1
0,00%
759 463 Wine 250,00 1
0,00%

thank you for your time and your patience, I have been working very hard

for

several days, but still haven't found a good solution to the problem.

Any of

your help will be greatly appreciated.

Paul

[Salad]

Paul T. RONG wrote:

Alan,

Thank you for your commment. I fully agree with you. If I make a software
for myself I would be very happy to stop at this point and let the cashier
to handle two bill or three bill problem.

Unfortunately this database is for a friend who is generelly very nice but
stubborn enough to accept the idea we like.

So I have to trek further,

regards,

Paul

Maybe have a command button for the oddball situations. In your table,
maybe add a field in the items ordered that has a PaidBy flag. This
would contain the Order number. When an order is paid, all detail items
will have that order number. If you are mixing matching paid orders,
present a new form. This form would be called and the order selected
would be the primary order. A list box of all outstanding orders (not
paid) would then presented. The user could then multiselect the orders
that will be mixed (paid) along with the primary order. Then all items
for all orders selected are presented in another listbox. Tag all of
the items that are to be paid on this order. When complete, store the
Primary order number in that field. When the next person pays, only the
items that do not have a paid field flagged will be presented and totaled.

[Tom van Stiphout]

On Wed, 24 Nov 2004 09:17:29 GMT, "Paul T. RONG" <et***@hotmail.com>
wrote:

When transferring items, you could process them a record at a time, in
a recordset. Then it would be fairly simple to see if the same
OrderDetailID already exists in the destination recordset. If yes, add
the quantity to the existing row, if not, add a new row.
in pseudo code:
create rsFrom with orderdetailids of order to transfer from
create rsTo with orderdetailids of order to transfer to
while not rsfrom.eof
rsto.findfirst "orderdetailid=" & rsfrom.orderdetailid
if rsto.nomatch then
rsto.addnew
'etc
else
rsto.update
'etc
endif
rsfrom.movenext
wend

The same can also be achieved by running two action queries: only to
update matching rows, and one to append non-matching rows.

-Tom.

Tom,

Exactly, but then it is the problem, say the customer pays one bottle of
Four Roses for the other Desk, while he himself drank one bottle of the same
brand, in the bill there should be two bottles of Four Roses:

OrderID OrderDetailID DishID price quantity
discont
759 472 Four Roses 750,00 2
0,00%

how to make the quantity automatically upgrade to 2 is the key problem.

Thank your for your concern and help,

Paul

"Tom van Stiphout" <no*************@cox.net>
??????:iu********************************@4ax.com ...

On Tue, 23 Nov 2004 23:12:52 GMT, "Paul T. RONG" <et***@hotmail.com>
wrote:

I might add a "Transfer" feature, where one or more OrderDetailIDs can
be transfered from one order to another. This way a customer can pay
for only the bottle of wine of the other Desk, or for the entire bill.
Transferring an OrderDetailID is as simple as giving it a different
OrderID.

<clip>

[Paul T. RONG]

Salad,

I think your idea is very good and interesting, but my friend the boss of
the restaurant wants me to try Tom's idea. I will try Tom's idea first and
let you know if it works or not.

Thank you very much.

Paul

"Salad" <oi*@vinegar.com>
??????:pd*****************@newsread3.news.pas.eart hlink.net...

Paul T. RONG wrote:

Alan,

Thank you for your commment. I fully agree with you. If I make a software for myself I would be very happy to stop at this point and let the cashier to handle two bill or three bill problem.

Unfortunately this database is for a friend who is generelly very nice but stubborn enough to accept the idea we like.

So I have to trek further,

regards,

Paul

Maybe have a command button for the oddball situations. In your table,
maybe add a field in the items ordered that has a PaidBy flag. This
would contain the Order number. When an order is paid, all detail items
will have that order number. If you are mixing matching paid orders,
present a new form. This form would be called and the order selected
would be the primary order. A list box of all outstanding orders (not
paid) would then presented. The user could then multiselect the orders
that will be mixed (paid) along with the primary order. Then all items
for all orders selected are presented in another listbox. Tag all of
the items that are to be paid on this order. When complete, store the
Primary order number in that field. When the next person pays, only the
items that do not have a paid field flagged will be presented and totaled.

[Paul T. RONG]

Tom,

Sounds like a good idea. I will try it and let you know.

Thank you,

Paul

"Tom van Stiphout" <no*************@cox.net>
??????:n6********************************@4ax.com. ..

On Wed, 24 Nov 2004 09:17:29 GMT, "Paul T. RONG" <et***@hotmail.com>
wrote:

When transferring items, you could process them a record at a time, in
a recordset. Then it would be fairly simple to see if the same
OrderDetailID already exists in the destination recordset. If yes, add
the quantity to the existing row, if not, add a new row.
in pseudo code:
create rsFrom with orderdetailids of order to transfer from
create rsTo with orderdetailids of order to transfer to
while not rsfrom.eof
rsto.findfirst "orderdetailid=" & rsfrom.orderdetailid
if rsto.nomatch then
rsto.addnew
'etc
else
rsto.update
'etc
endif
rsfrom.movenext
wend

The same can also be achieved by running two action queries: only to
update matching rows, and one to append non-matching rows.

-Tom.

Tom,

Exactly, but then it is the problem, say the customer pays one bottle of
Four Roses for the other Desk, while he himself drank one bottle of the samebrand, in the bill there should be two bottles of Four Roses:

OrderID OrderDetailID DishID price quantity
discont
759 472 Four Roses 750,00 2
0,00%

how to make the quantity automatically upgrade to 2 is the key problem.

Thank your for your concern and help,

Paul

"Tom van Stiphout" <no*************@cox.net>
??????:iu********************************@4ax.com ...

On Tue, 23 Nov 2004 23:12:52 GMT, "Paul T. RONG" <et***@hotmail.com>
wrote:

I might add a "Transfer" feature, where one or more OrderDetailIDs can
be transfered from one order to another. This way a customer can pay
for only the bottle of wine of the other Desk, or for the entire bill.
Transferring an OrderDetailID is as simple as giving it a different
OrderID.

<clip>

[Alan Webb]

Paul,
Make sure your friend pays for the additional time it will take to code
this. In contract programming a change order is a recognized means of
altering the original scope to account for mid-project changes to the specs.
I'd write a change order and put a price tag on it. If your friend agrees
to the additional cost, great. If not, don't code it.

"Paul T. RONG" <et***@hotmail.com> wrote in message
news:wz******************@news.chello.at...

Alan,

Thank you for your commment. I fully agree with you. If I make a software
for myself I would be very happy to stop at this point and let the cashier
to handle two bill or three bill problem.

Unfortunately this database is for a friend who is generelly very nice but
stubborn enough to accept the idea we like.

So I have to trek further,

regards,

Paul


"Alan Webb" <kn*****@hotmail.com> дÈëÏûÏ¢ÐÂÎÅ
:Q-********************@comcast.com...

Paul,
At our restaurant the cashier is trained to handle each tab as a single
transaction. So if Joey comes up to the cashier with his and his buddies
tab she does two transactions, one for each tab. You do get to a point
in
systems analysis where the cost of the work to code a feature can't be
justified by the benefits. A good cashier should be able to gracefully
handle this without having to code anything.
Also, remember that a restaurant is essentially a factory in terms of the
business processes that an information system needs to support. In a
factory orders come in and resources get allocated to fill the orders.
It
does happen that there are not enough components on hand to fill all the
orders. This means that it is possible for portions of an order to

complete

production while other portions have to go on back-order until there is
sufficient inventory to complete the order. Customers will also call the
plant and ask if several orders can be put on one bill of lading and

shipped

together. Your shipping people may recognize that a customer has several
orders due out of production on a given day that could go on the same

truck

and thus should be on the same bill of lading. In these cases it is the
shipment that is the grouping upon which an invoice will be generated.
Were I your boss I'd say that this is one best left to the humans. You
could code a merge ticket feature as mentioned in another post. But I
wonder if the effort is worth it. Once a ticket is merged you lose the
ability to see what was ordered on the merged ticket and your chef and
sous-chef might find that irritating when it comes to analyzing how the
business is doing per table or per order.

"Paul T. RONG" <et***@hotmail.com> wrote in message
news:Ut*****************@news.chello.at...

> Hello,
>
> I am making a restaurant database (it is much more complicated than I
> thought before!), now it comes to the last stage and I come across a
> problem. I will explain it in detail. In a restaurant, though rarely, but > it
> happens, that one guy from desk1 would like to pay for bills of both desk1 > and desk2 (I avoid using Access term "table"), that means the dishes that > ordered seperately before by desk1 and desk2 now have to be commixed
> together under desk1 or desk2.
>
> for example, there are following dishes ordered, under desk1:
>
> OrderID OrderDetailID DishID price quantity
> discont
> 759 456 Pizza brocoli 219,00 1
> 0,00%
> 759 457 Pizza Napoli 129,00 2
> 0,00%
> 759 458 Pizza Ham 109,00 1
> 0,00%
> 759 459 Cigarette 65,00 1
> 0,00%
>
>
> and under desk2:
>
> OrderID OrderDetailID DishID price quantity
> discont
> 760 460 Pizza brocoli 219,00 1
> 0,00%
> 760 461 Pizza Ham 109,00 1
> 0,00%
> 760 462 Cigarette 85,00 1
> 0,00%
> 760 463 Wine 250,00 1
> 0,00%
>
>
> when they are commixed together, the tblOrderDetail which is the source of > the subform on the frmOrder could theoretically look like:
>
>
> OrderID OrderDetailID DishID price quantity
> discont
> 759 456 Pizza brocoli 219,00 1
> 0,00%
> 759 457 Pizza Napoli 129,00 2
> 0,00%
> 759 458 Pizza Ham 109,00 1
> 0,00%
> 759 459 Cigarette 65,00 1
> 0,00%
> 759 460 Pizza brocoli 219,00 1
> 0,00%
> 759 461 Pizza Ham 109,00 1
> 0,00%
> 759 462 Cigarette 85,00 1
> 0,00%
> 759 463 Wine 250,00 1
> 0,00%
>
>
> I came to this step, however practically what the users like more is a
> no-repeat-DishID version, and each dishs' quantity added together
> respectively, note that Cigarette has different prices (65.00 and 85.00), > because the price of the cigarettes (so is the wine) is changing so often > that the cigarettes don't have their fixed prices:
>
> OrderID OrderDetailID DishID price quantity
> discont
> 759 456 Pizza brocoli 219,00 2
> 0,00%
> 759 457 Pizza Napoli 129,00 2
> 0,00%
> 759 458 Pizza Ham 109,00 2
> 0,00%
> 759 459 Cigarette 65,00 1
> 0,00%
> 759 462 Cigarette 85,00 1
> 0,00%
> 759 463 Wine 250,00 1
> 0,00%
>
> thank you for your time and your patience, I have been working very
> hard
> for
> several days, but still haven't found a good solution to the problem. Any > of
> your help will be greatly appreciated.
>
> Paul
>
>

[Paul T. RONG]

Alan,

I didn't realize it at all. I will talk with him today if he agrees to the
additional cost. If not, I'd better not do it. Good advise. We should
respect ourself more. Thank you very much.

Paul
"Alan Webb" <kn*****@hotmail.com> дÈëÏûÏ¢ÐÂÎÅ
:c9********************@comcast.com...

Paul,
Make sure your friend pays for the additional time it will take to code
this. In contract programming a change order is a recognized means of
altering the original scope to account for mid-project changes to the specs. I'd write a change order and put a price tag on it. If your friend agrees
to the additional cost, great. If not, don't code it.

"Paul T. RONG" <et***@hotmail.com> wrote in message
news:wz******************@news.chello.at...

Alan,

Thank you for your commment. I fully agree with you. If I make a software for myself I would be very happy to stop at this point and let the cashier to handle two bill or three bill problem.

Unfortunately this database is for a friend who is generelly very nice but stubborn enough to accept the idea we like.

So I have to trek further,

regards,

Paul


"Alan Webb" <kn*****@hotmail.com> дÈëÏûÏ¢ÐÂÎÅ
:Q-********************@comcast.com...

Paul,
At our restaurant the cashier is trained to handle each tab as a single
transaction. So if Joey comes up to the cashier with his and his buddies tab she does two transactions, one for each tab. You do get to a point
in
systems analysis where the cost of the work to code a feature can't be
justified by the benefits. A good cashier should be able to gracefully
handle this without having to code anything.
Also, remember that a restaurant is essentially a factory in terms of the business processes that an information system needs to support. In a
factory orders come in and resources get allocated to fill the orders.
It
does happen that there are not enough components on hand to fill all the orders. This means that it is possible for portions of an order to

complete

production while other portions have to go on back-order until there is
sufficient inventory to complete the order. Customers will also call the plant and ask if several orders can be put on one bill of lading and

shipped

together. Your shipping people may recognize that a customer has several orders due out of production on a given day that could go on the same

truck

and thus should be on the same bill of lading. In these cases it is the shipment that is the grouping upon which an invoice will be generated.
Were I your boss I'd say that this is one best left to the humans. You
could code a merge ticket feature as mentioned in another post. But I
wonder if the effort is worth it. Once a ticket is merged you lose the
ability to see what was ordered on the merged ticket and your chef and
sous-chef might find that irritating when it comes to analyzing how the
business is doing per table or per order.

"Paul T. RONG" <et***@hotmail.com> wrote in message
news:Ut*****************@news.chello.at...
> Hello,
>
> I am making a restaurant database (it is much more complicated than I
> thought before!), now it comes to the last stage and I come across a
> problem. I will explain it in detail. In a restaurant, though rarely,

but

> it
> happens, that one guy from desk1 would like to pay for bills of both

desk1

> and desk2 (I avoid using Access term "table"), that means the dishes

that

> ordered seperately before by desk1 and desk2 now have to be commixed
> together under desk1 or desk2.
>
> for example, there are following dishes ordered, under desk1:
>
> OrderID OrderDetailID DishID price quantity
> discont
> 759 456 Pizza brocoli 219,00 1
> 0,00%
> 759 457 Pizza Napoli 129,00 2
> 0,00%
> 759 458 Pizza Ham 109,00 1
> 0,00%
> 759 459 Cigarette 65,00 1
> 0,00%
>
>
> and under desk2:
>
> OrderID OrderDetailID DishID price quantity
> discont
> 760 460 Pizza brocoli 219,00 1
> 0,00%
> 760 461 Pizza Ham 109,00 1
> 0,00%
> 760 462 Cigarette 85,00 1
> 0,00%
> 760 463 Wine 250,00 1
> 0,00%
>
>
> when they are commixed together, the tblOrderDetail which is the source

of

> the subform on the frmOrder could theoretically look like:
>
>
> OrderID OrderDetailID DishID price quantity
> discont
> 759 456 Pizza brocoli 219,00 1
> 0,00%
> 759 457 Pizza Napoli 129,00 2
> 0,00%
> 759 458 Pizza Ham 109,00 1
> 0,00%
> 759 459 Cigarette 65,00 1
> 0,00%
> 759 460 Pizza brocoli 219,00 1
> 0,00%
> 759 461 Pizza Ham 109,00 1
> 0,00%
> 759 462 Cigarette 85,00 1
> 0,00%
> 759 463 Wine 250,00 1
> 0,00%
>
>
> I came to this step, however practically what the users like more is

a > no-repeat-DishID version, and each dishs' quantity added together
> respectively, note that Cigarette has different prices (65.00 and

85.00),

> because the price of the cigarettes (so is the wine) is changing so

often

> that the cigarettes don't have their fixed prices:
>
> OrderID OrderDetailID DishID price quantity
> discont
> 759 456 Pizza brocoli 219,00 2
> 0,00%
> 759 457 Pizza Napoli 129,00 2
> 0,00%
> 759 458 Pizza Ham 109,00 2
> 0,00%
> 759 459 Cigarette 65,00 1
> 0,00%
> 759 462 Cigarette 85,00 1
> 0,00%
> 759 463 Wine 250,00 1
> 0,00%
>
> thank you for your time and your patience, I have been working very
> hard
> for
> several days, but still haven't found a good solution to the problem.

Any

> of
> your help will be greatly appreciated.
>
> Paul
>
>

[John Baker]

Salad wrote:

Maybe have a command button for the oddball situations.

I think there is a bit of wisdom here that should not be overlooked.

However the problem is solved, in no case should the solution for the
oddball situation add work or complexity (for the user) in processing
the "normal" situations.
--
To Email Me, ROT13 My Shown Email Address

[Paul T. RONG]

1. What we have: a table called "tblOrderDetail":

OrderID OrderDetailID DishID price quantity
-------------------------------------------------------------------
759 456 Pizza brocoli 219,00 1
759 457 Pizza Napoli 129,00 2
759 458 Pizza Ham 109,00 1
759 459 Cigarette 65,00 1
760 460 Pizza brocoli 219,00 1
760 461 Pizza Ham 109,00 1
760 462 Cigarette 85,00 1

2. What we need to achieve: How to manually update this table to:

OrderID OrderDetailID DishID price quantity
---------------------------------------------------------------------
759 456 Pizza brocoli 219,00 2
759 457 Pizza Napoli 129,00 2
759 458 Pizza Ham 109,00 2
759 459 Cigarette 65,00 1
759 462 Cigarette 85,00 1
3. Codes advised by Tom van Stiphout:
------------------------------------------------------------------------
Private Sub Command1_Click()

Dim rstfr, rstto As DAO.Recordset
Set rstfr = CurrentDb.OpenRecordset("SELECT tblOrderDetail.DishID,
tblOrderDetail.price FROM tblOrderDetail WHERE (((tblOrderDetail. DishID)
='" & Me.txt2.Value & "') );")
' This is the place it crashes
' Me.txt2.Value=760

Set rstto = CurrentDb.OpenRecordset("SELECT tblOrderDetail.DishID,
tblOrderDetail.price FROM tblOrderDetail WHERE (((tblOrderDetail. DishID)
='" & Me.txt1.Value & "') );")
' Me.txt1.Value=759

While Not rstfr.EOF

rstto.FindFirst "DishID='" & rstfr.DishID & "'"

If rstto.NoMatch Then

rstto.AddNew

Else

rstto.Update

End If

rstfr.MoveNext

Wend

End Sub

-----------------------------------------------------------------------

It doesn't work because my poor knowledge on DAO....

My idea is to use Recordset to read 3 dishes from the tblOrderDetail with
the OrderID=760, then start from first dish "Pizza brocoli
(OrderDetailID=460)" to look for match among the dishes with the
OrderID=759, if there is a match, then Update, otherwise AddNew, etc till
the last.

I don't know where I made mistake, should I use MoveFirst to make the search
process start from first Dish?

Your comment and help are greatly appreciated, thank you for your patience.

Paul

[Paul T. RONG]

Sorry, there is a mistake in the post:

Set rstfr = CurrentDb.OpenRecordset("SELECT tblOrderDetail.DishID,
tblOrderDetail.price FROM tblOrderDetail WHERE (((tblOrderDetail.OrderID)
='" & Me.txt2.Value & "') );")

Set rstto = CurrentDb.OpenRecordset("SELECT tblOrderDetail.DishID,
tblOrderDetail.price FROM tblOrderDetail WHERE (((tblOrderDetail.OrderID)
='" & Me.txt1.Value & "') );")