Problem integrating tables into query

[BettyG]

I'm doing a POS database for a restaurant, and I'm stuck! I really need some
help!!!

I have Table1 with the orders and their costs and Table2 with all the extra
ingredientes for each platter ordered. Table1 and Table2 are related through
a unique code. I'm integrating the information in one query which is the base
for my receipt, but here is where I have the problem, because I would need to
have each extra ingredient for each unique code in Table2 as a column in
Table1, is it possible to do this? Let me put an example to explain myself
better.

Table1
IDOrder PlatterNo. Platter BreadType Category Quantity Cost
UniqueCode
1 1 ManhattanSub Normal Sub 1
$3.5 P1N1
1 2 ScalaSub Normal Sub 1
$3.5 P1N2
1 3 OlivaSub Normal Sub 1
$3.5 P1N3
1 4 DietCoke Drinks
1 $1.5 P1N4
2 1 ManhattanSub Normal Sub 1
$3.5 P2N1
2 2 RegularCoke Drinks
1 $1.5 P2N2
3 1 ScalaSub Normal Sub 1
$3.5 P3N1
3 1 DietCoke Drinks
1 $1.5 P3N2
And so on...

Table2
UniqueCode ExtraIngredient Cost
P1N1 ExtraCheese $1.0
P1N1 ExtraHam $0.5
P3N1 ExtraCheese $1.0

If I integrate this information in a single query, it duplicates te
information and eliminates the orders that don't have extra ingredients, this
way

Query
IDOrder PlatterNo. Platter BreadType Quant. Cost
UniqueCode ExtraIngredient Cost
1 1 ManhattanSub Normal 1 $3.5
P1N1 ExtraCheese $1.0
1 1 ManhattanSub Normal 1 $3.5
P1N1 ExtraHam $0.5
3 1 ScalaSub Normal 1 $3.5
P3N1 ExtraMeat $1.5


What I'm lookin for is to have the following results:
IDOrder PlatterNo. Platter Quant. Cost Code ExtraIngr.1
Cost ExtraIngr.2 Cost
1 1 ManhattanSub 1 $3.5 P1N1 ExtraCheese
$1.0 ExtraHam $0.5
1 2 ScalaSub 1 $3.5 P1N2
1 3 OlivaSub 1 $3.5 P1N3
1 4 DietCoke 1 $1.5 P1N4
2 1 ManhattanSub 1 $3.5 P2N1
2 2 RegularCoke 1 $1.5 P2N2
3 1 ScalaSub 1 $3.5 P3N1
ExtraMeat $1.5
3 1 DietCoke 1 $1.5 P3N2

Is it possible to obtain the results I'm looking for?

 

[Jeff Boyce]

I can't be sure from your description, but it seems possible your table
structure is more like what you'd need to use if you were limited to using a
spreadsheet. You won't get the best use of Access' features and functions
if you aren't using well-normalized data.

First the "things" (entities), then the relationships, finally the queries,
forms and reports.

What is the entity that your Table1 stores information about? ?Orders?
Why do you have some ordered items (e.g., Oliva Sub, Manhatten Sub, Diet
Coke) in one table and other ordered items (e.g., Extra Cheese) in another
table?

Granted, I'm not there, so I know nothing about your unique situation, but
it seems like another, more-normalized view of the data might be something
like:

tblServer
ServerID (unique identifier)
LastName
FirstName
... other facts specifically about servers

tblItem
ItemID (a unique identifier for EACH/EVERY item)
ItemName
ItemDescription
... other facts specifically about items

tblTable (sorry, couldn't resist <g>)
TableID (unique identifier)
TableNumber (your Table1, or Table2, or ...)
... other facts specific to Tables (e.g., seating capacity?)

trelOrder
OrderID (unique identifier)
OrderDateTime (when was order entered?)
TableID (for what table?)
ServerID (by which Server?)
Station (whatever this is from your description)

trelOrderItem
OrderItemID
OrderID (which order does this detail belong to?)
ItemID (one ordered item -- for multi-item order, use multiple rows
in this table)
... other facts specific to ordered item -- e.g. SpecialHandling =
"Extra Hot"

It almost seems to me that this is analogous to an order/orderdetail setup
for a retail purchase on-line.

Or maybe I've just read way too much into your description...

Best of luck

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[BettyG via AccessMonster.com]

Jeff

I truly appreciate your response. In fact my tables are pretty similar to
your proposal. The reason for dividing the order items into 2 tables is to be
able to associate in the same order, which platter has extra items (like
extra cheese) . How do I relate the platter with the extra items added in the
table trelOrderItem you propose?

I can't be sure from your description, but it seems possible your table
structure is more like what you'd need to use if you were limited to using a
spreadsheet. You won't get the best use of Access' features and functions
if you aren't using well-normalized data.

First the "things" (entities), then the relationships, finally the queries,
forms and reports.

What is the entity that your Table1 stores information about? ?Orders?
Why do you have some ordered items (e.g., Oliva Sub, Manhatten Sub, Diet
Coke) in one table and other ordered items (e.g., Extra Cheese) in another
table?

Granted, I'm not there, so I know nothing about your unique situation, but
it seems like another, more-normalized view of the data might be something
like:

tblServer
ServerID (unique identifier)
LastName
FirstName
... other facts specifically about servers

tblItem
ItemID (a unique identifier for EACH/EVERY item)
ItemName
ItemDescription
... other facts specifically about items

tblTable (sorry, couldn't resist <g>)
TableID (unique identifier)
TableNumber (your Table1, or Table2, or ...)
... other facts specific to Tables (e.g., seating capacity?)

trelOrder
OrderID (unique identifier)
OrderDateTime (when was order entered?)
TableID (for what table?)
ServerID (by which Server?)
Station (whatever this is from your description)

trelOrderItem
OrderItemID
OrderID (which order does this detail belong to?)
ItemID (one ordered item -- for multi-item order, use multiple rows
in this table)
... other facts specific to ordered item -- e.g. SpecialHandling =
"Extra Hot"

It almost seems to me that this is analogous to an order/orderdetail setup
for a retail purchase on-line.

Or maybe I've just read way too much into your description...

Best of luck

I'm doing a POS database for a restaurant, and I'm stuck! I really need some
help!!!

[quoted text clipped - 63 lines]

Is it possible to obtain the results I'm looking for?

 

[Jeff Boyce]

If you are saying that a platter has one or more items, then the platter is
the "order" (parent table) and the detail list of items (child table)
relates to the platter.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Jeff

I truly appreciate your response. In fact my tables are pretty similar to
your proposal. The reason for dividing the order items into 2 tables is to be
able to associate in the same order, which platter has extra items (like
extra cheese) . How do I relate the platter with the extra items added in the
table trelOrderItem you propose?

I can't be sure from your description, but it seems possible your table
structure is more like what you'd need to use if you were limited to using a
spreadsheet. You won't get the best use of Access' features and functions
if you aren't using well-normalized data.

First the "things" (entities), then the relationships, finally the queries,
forms and reports.

What is the entity that your Table1 stores information about? ?Orders?
Why do you have some ordered items (e.g., Oliva Sub, Manhatten Sub, Diet
Coke) in one table and other ordered items (e.g., Extra Cheese) in another
table?

Granted, I'm not there, so I know nothing about your unique situation, but
it seems like another, more-normalized view of the data might be something
like:

tblServer
ServerID (unique identifier)
LastName
FirstName
... other facts specifically about servers

tblItem
ItemID (a unique identifier for EACH/EVERY item)
ItemName
ItemDescription
... other facts specifically about items

tblTable (sorry, couldn't resist <g>)
TableID (unique identifier)
TableNumber (your Table1, or Table2, or ...)
... other facts specific to Tables (e.g., seating capacity?)

trelOrder
OrderID (unique identifier)
OrderDateTime (when was order entered?)
TableID (for what table?)
ServerID (by which Server?)
Station (whatever this is from your description)

trelOrderItem
OrderItemID
OrderID (which order does this detail belong to?)
ItemID (one ordered item -- for multi-item order, use multiple rows
in this table)
... other facts specific to ordered item -- e.g. SpecialHandling =
"Extra Hot"

It almost seems to me that this is analogous to an order/orderdetail setup
for a retail purchase on-line.

Or maybe I've just read way too much into your description...

Best of luck

I'm doing a POS database for a restaurant, and I'm stuck! I really need some
help!!!

[quoted text clipped - 63 lines]

Is it possible to obtain the results I'm looking for?

 

[BettyG via AccessMonster.com]

But at the same time, in one order I have several platters, which may have
several items (or not, depending on each customer)

If you are saying that a platter has one or more items, then the platter is
the "order" (parent table) and the detail list of items (child table)
relates to the platter.

Jeff

[quoted text clipped - 65 lines]

 

[Chris2]

But at the same time, in one order I have several platters, which may have
several items (or not, depending on each customer)

BettyG,

The following is really a set of business-rule type statements. They can be used to
expaned into a model of your business, which in turn becomes a map of tables in your
database, each with the appropriate attributes (columns).


Each Table has one Ticket

Tickets have Orders

Orders have MenuItems

MenuItems have Components

MenuItems might have Additions (or maybe its called Adjustments)

Additions can be Sides
Additions can be MenuItemComponentAdditions (as allowed)
Additions can be MenuItemComponentSubtractions (always allowed)
Additions can be MenuItemComponentSubstitutions (as allowed)
Additions can be Instructions (for Cooking or Preparation of Components)

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

By having the Ticket layer added, you can have as many separately accounted Orders per
table as the customer(s) request.

It also allows for easy subtotalled billing, something large parties frequently desire.
(Party of 8: Um, the first two want one bill, the next three want another, and the last
three all are on their own bill.)

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

A Server is assigned to a Ticket

A Server may change mid-Ticket (goes on break/lunch, shift ends, emergency departure,
customer demand).


Sincerely,

Chris O.

 

[Jeff Boyce]

Betty

A difference in our definitions of the word "Order"?

I'll return to my earlier idea, perhaps not well-stated, that normalization
(identification of entities and relationships) is a crucial first step.

The response by Chris2 appears to offer a more-normalized view, but you will
still need to decide what model best fits your situation.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

But at the same time, in one order I have several platters, which may have
several items (or not, depending on each customer)

If you are saying that a platter has one or more items, then the platter is
the "order" (parent table) and the detail list of items (child table)
relates to the platter.

Jeff

[quoted text clipped - 65 lines]

Is it possible to obtain the results I'm looking for?

 

[Chris2]

Betty

A difference in our definitions of the word "Order"?

I'll return to my earlier idea, perhaps not well-stated, that normalization
(identification of entities and relationships) is a crucial first step.

The response by Chris2 appears to offer a more-normalized view, but you will
still need to decide what model best fits your situation.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

BettyG,

I'll throw in some links for research on normalization.

Basics:

About.com
http://databases.about.com/od/specificproducts/a/normalization.htm

Tips to Get You Going
http://home.att.net/~california.db/tips.html#aTip1

Microsoft: Description of database normalization basics in Access 2000 (not significantly
changed by Access 2007, see the article's own references at the end to material from the
early 1990s).
http://support.microsoft.com/support/kb/articles/q209/5/34.asp


Intermediate:

MySQL's website:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html


Advanced:

Wikipedia:
http://en.wikipedia.org/wiki/Database_normalization


Very Advanced:

University of Texas:
I like this whole site, since it has a handy menu on the right describing many important
aspects of the database world:
http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html


Sincerely,

Chris O.

 

[BettyG via AccessMonster.com]

Chris

Actually I understand normalization, but I don't know how to handle the
Additions for each MenuItem. Because I have one table with the information
following information:
IDTable IDTicket(unique) IDOrder(unique) IDMenuItem Quantity

I understand I must have another table with each/every Menu Item and its
information (description, cost, etc.) But I don't know how to handle the
Additions made to each IDMenuItem of a specific Order, do I have to prepare
additional tables or I have to handle it through VBA programation, I'm pretty
lost at this point.

 

[Chris2]

Chris

Actually I understand normalization, but I don't know how to handle the
Additions for each MenuItem. Because I have one table with the information
following information:
IDTable IDTicket(unique) IDOrder(unique) IDMenuItem Quantity

I understand I must have another table with each/every Menu Item and its
information (description, cost, etc.) But I don't know how to handle the
Additions made to each IDMenuItem of a specific Order, do I have to prepare
additional tables or I have to handle it through VBA programation, I'm pretty
lost at this point.

BettyG,

At this point, I need to explain database desgin. It's a subject that takes up whole
libraries of books, but you need to start somewhere.

Books: General: Beginner

Database Design for Mere Mortals by Michael J. Hernandez

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

Notes:

Exactly how you want to handle MenuItems and their associated Components and customer
requests for Additions, Subtractions, Substitutions, Sides, etc., is dependent on how the
business operates.

I don't know how your client's business operates, so I can only make guesses. Please be
aware that this is what the following is, guesses.

Controlling all of this would involve programming, undoubtedly. If it were me, I would be
making classes to control all access to tables (per entity, in an Object-Oriented based
approach) via recordsets inside of the classes. I would use unbound Forms. On the
unbound Forms I would be instantiating objects based on the classes I had created, and
would use the properties and methods of those objects to obtain and update the data (both
from and to Tables and Forms). This has the advantage of centralizing all data-access
code into one place, each class. There is no going back to every Form to update every
single piece of data access code whenever a change is necessary. Only the code that hands
data back and forth between GUI controls on the Forms and the object's properties and
methods would need to be changed. (An excellent overview can be found in Beginning VB6
Objects, Peter Wright, Wrox. It applies to MS Access.)


Table-wise, essentially you would have:

EmployeeTypes
(Manager, Server, Cook, Cleaner, etc.)

Employees
EmployeeID

MenuItems
MenuItemID
MenuItemName

(The price of a MenuItem is calculated by SUMing the price of its Components, and applying
any discounts, promotions, coupons, vouchers, frequent traveller progam membershipos,
management compensation, state sales taxes (must be handled legally!), etc. That is tied
in with the part of the POS that controls charges and payments)

Components
ComponentID
MenuItemID
ComponentName
ComponentDescription
ComponentPrice

A separate Prices table might be needed, this expands into the need to handle promotional
pricing (also mentioned above), which is a necessity for virtually every business.


Tables
TableID
Seats

Tickets
TicketID
TableID

TicketEmployees
TicketID
EmployeeID

Orders
OrderID
TicketID

OrderMenuItems
OrderID
MenuItemID

Adjustments (I decided to call it this after all)
AdjustmentID

OrderAdjustments
OrderID
MenuItemID
AdjustmentID

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

That's just a sampling.

Cash register operations, payments, were not even touched.

Integration with inventory, ordering, and accounting were not mentioned.

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

If you still have questions, I recommend posting a new thread in
microsoft.public.access.tablesdbdesign.

Title it "POS DB Architecture Questions". This title is most likely to attract the
attention of those who know the most about POS design.

Outline, point-by-point, exactly what your design questions are.

Narrow and specific questions are the ones that are most likely to get usable answers.

Broad questions are more likely to get broad answers (that aren't very usable).

Provide your applicable business rules, your table structures (or ideas for table
structures), etc.

If you don't get any answers there, repost it in a 2-3 days, and if you still get no
answers in another 2-3 days, repost to microsoft.public.access itself.

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

You should also strongly consider obtaining an existing POS system.

Are you sure you want to reinvent a wheel that has already been reinvented several
thousand times?

http://www.google.com/search?num=100&hl=en&q=customizable+pos+systems

The Open Directory Project lists lots of links at:

http://dmoz.org/Business/Retail_Trade/Technology/Point_of_Sale/
http://dmoz.org/Business/Retail_Trade/Technology/Software/

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

More specific help (for POS) might be located at:

http://www.tek-tips.com/threadminder.cfm?pid=693

.. . . and similar online communities.


Sincerely,

Chris O.

 

[BettyG via AccessMonster.com]

Chris:

Thank you very much for your reply , I truly appreciate you took the time to
answer me. I will start working with your suggestions and hope to solve my
problem. I have already been reading the sites you send me on your past
reply, and they have been very useful. If I can't get this to work, I willl
truly evaluate the possiblity to get an existing POS.

I'm willing to learn as far as I can about Access, buy must of what I know so
far has been mostly reading access help and internet sites. I started with
the book Access Step by Step, but I would like to read a little bit more. I
don't want to abuse of your good intentions, but which books do you recommend
me? Would Database Design for Mere Mortals by Michael J. Hernandez be a good
start? And then?

Thank you very much for your patience and your help. I will let you know if I
could make this work.

[quoted text clipped - 53 lines]

additional tables or I have to handle it through VBA programation, I'm pretty
lost at this point.

BettyG,

At this point, I need to explain database desgin. It's a subject that takes up whole
libraries of books, but you need to start somewhere.

Books: General: Beginner

Database Design for Mere Mortals by Michael J. Hernandez

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

Notes:

Exactly how you want to handle MenuItems and their associated Components and customer
requests for Additions, Subtractions, Substitutions, Sides, etc., is dependent on how the
business operates.

I don't know how your client's business operates, so I can only make guesses. Please be
aware that this is what the following is, guesses.

Controlling all of this would involve programming, undoubtedly. If it were me, I would be
making classes to control all access to tables (per entity, in an Object-Oriented based
approach) via recordsets inside of the classes. I would use unbound Forms. On the
unbound Forms I would be instantiating objects based on the classes I had created, and
would use the properties and methods of those objects to obtain and update the data (both
from and to Tables and Forms). This has the advantage of centralizing all data-access
code into one place, each class. There is no going back to every Form to update every
single piece of data access code whenever a change is necessary. Only the code that hands
data back and forth between GUI controls on the Forms and the object's properties and
methods would need to be changed. (An excellent overview can be found in Beginning VB6
Objects, Peter Wright, Wrox. It applies to MS Access.)

Table-wise, essentially you would have:

EmployeeTypes
(Manager, Server, Cook, Cleaner, etc.)

Employees
EmployeeID

MenuItems
MenuItemID
MenuItemName

(The price of a MenuItem is calculated by SUMing the price of its Components, and applying
any discounts, promotions, coupons, vouchers, frequent traveller progam membershipos,
management compensation, state sales taxes (must be handled legally!), etc. That is tied
in with the part of the POS that controls charges and payments)

Components
ComponentID
MenuItemID
ComponentName
ComponentDescription
ComponentPrice

A separate Prices table might be needed, this expands into the need to handle promotional
pricing (also mentioned above), which is a necessity for virtually every business.

Tables
TableID
Seats

Tickets
TicketID
TableID

TicketEmployees
TicketID
EmployeeID

Orders
OrderID
TicketID

OrderMenuItems
OrderID
MenuItemID

Adjustments (I decided to call it this after all)
AdjustmentID

OrderAdjustments
OrderID
MenuItemID
AdjustmentID

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

That's just a sampling.

Cash register operations, payments, were not even touched.

Integration with inventory, ordering, and accounting were not mentioned.

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

If you still have questions, I recommend posting a new thread in
microsoft.public.access.tablesdbdesign.

Title it "POS DB Architecture Questions". This title is most likely to attract the
attention of those who know the most about POS design.

Outline, point-by-point, exactly what your design questions are.

Narrow and specific questions are the ones that are most likely to get usable answers.

Broad questions are more likely to get broad answers (that aren't very usable).

Provide your applicable business rules, your table structures (or ideas for table
structures), etc.

If you don't get any answers there, repost it in a 2-3 days, and if you still get no
answers in another 2-3 days, repost to microsoft.public.access itself.

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

You should also strongly consider obtaining an existing POS system.

Are you sure you want to reinvent a wheel that has already been reinvented several
thousand times?

http://www.google.com/search?num=100&hl=en&q=customizable+pos+systems

The Open Directory Project lists lots of links at:

http://dmoz.org/Business/Retail_Trade/Technology/Point_of_Sale/
http://dmoz.org/Business/Retail_Trade/Technology/Software/

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

More specific help (for POS) might be located at:

http://www.tek-tips.com/threadminder.cfm?pid=693

. . . and similar online communities.

Sincerely,

Chris O.

 

[Chris2]

Chris:

I'm willing to learn as far as I can about Access, buy must of what I know so
far has been mostly reading access help and internet sites. I started with
the book Access Step by Step, but I would like to read a little bit more. I
don't want to abuse of your good intentions, but which books do you recommend
me? Would Database Design for Mere Mortals by Michael J. Hernandez be a good
start? And then?

I would stop by a local Borders or Barnes and Noble, find a copy, and page through it to
see if is "speaks" to you. Not every book is for every person.

Other books are:


Books: General: Intermediate/Advanced

Advanced SQL Programming, For Smarties, 3rd Edition, by Joe Celko

SQL Puzzles and Answers, by Joe Celko


Books: General: Advanced

The Data Modeling Handbook, Michael C. Reingruber and William W. Gregroy


Books: Access : Intermediate

Access Cookbook by Getz, Litwin, and Baron
(Compilation of solutions, listed by task-category)


Books: Access: Advanced

Access Database Design & Programming by Steven Roman (2nd or 3rd Edition)

Access Developer's Handbook (for your version of Access)


Websites:

http://www.mvps.org/access
http://allenbrowne.com/
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Top
http://www.rogersaccesslibrary.com/
http://www.accessmvp.com/djsteele/SmartAccess.html


Free Stuff:

The following is true for MS Access 2000 (and I *think* also for 2002 and 2003). As for
Access 2007, I don't know.

You should open the Microsoft Access Help.

Switch to the Contents tab if you aren't already there.

Look down the list for a book-icon.

Programming Information

Open it, browse through it.

Basic Programming Concepts would be a good place to start.

The book-icons, Microsoft Jet SQL Reference, Visual Basic Conceptual Topics, and Visual
Basic Language Reference, can be good places to locate the answers to specific questions
(none are guides).

I wouldn't call these files the most friendly possible. However, they are right there on
your computer.

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

You can also search on your harddrive for a file named DNJET.chm. If you have it, this is
the Microsoft Jet Database Engine Programmer's Guide. It is unfortunate, but it seems to
have been written for JET 3.x (I have never located an updated version). Still, a great
deal of it applies to JET 4.0, and more of it is general in nature about how to use SQL
(where the Microsoft Jet SQL Reference just splats out syntax with some rules and
restrictions).

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

The Microsoft Developer's Network.

A lot of information to be found here.

http://msdn2.microsoft.com/en-us/library/ms400535.aspx.

You can scroll down the menu to find Office 2003, XP, and 2000. There are sections under
each for MS Access.


Sincerely,

Chris O.

 

[BettyG via AccessMonster.com]

Chris:

Once more, thank you very much for your response, I really appreciate your
help and the information you are giving me. I will take it into consideration
to continue studying and profundizing in Access.

Betty