calculation to get information from related records

[ggh]

Don't know if this belongs in relational design or here, but here goes...

I have a student table related to an orders table for hot lunch. This orders table is simple with just a few fields:

FoodDay Item Quantity Fee StudentID

Related records might look like this:

Domino's Day Cheese Pizza 2 $4.00

Domino's Day Pepperoni Pizza 1 $2.00

Subway Day Italian 1 $3.50

What I want to do is create a field for each day that codes the order (to be used on an ID tag). In the new Domino's field I'd like a calculation to generate "2C 1P". For Subway I'd like it to generate "1I". I've played around with some aggregate functions with no luck. Seems like this should be doable.

[Mike Williamson]

OK, you've got a Student table and a Orders table. Next, you will need an Items table where the food options will be. That is the piece you are missing.

When designing the structure behind a db a good practice is to ask yourself "What constitutes a single record?" In your case, Students is obvious. a single Order record is one item for a student for a day. The Items table would have a record for each possible item that they might order. And, it is in the Items table where you would have the value of 2C 1P, or 1l. But instead of calcs, they will be text fields that auto-enter as you select and Item ID.

Does that help?