Control table

  • Thread starter Thread starter Ed Jordan
  • Start date Start date
E

Ed Jordan

i am trying to make an event regestion data base and i want to know is there
any way to make a price table and use it to caculate costs on a form?
Eg.
I enter on a form:
Adults = 2
Kids = 3

than using data from the costs table
Adult price = 50
Kid Price = 25

it genertates the cost
Cost = Adults * Adult Price + Kids * Kids Price
....
Cost = 2 * 50 + 3 * 25
....
Cost = 175
So it displays 175 in a box at the bottom of the form

Thanks
Ed
 
To do this in a relational database if you use one-to-many relationships.

One sale (order? invoice?) can have multiple tickets at different prices. It
therefore needs to be modelled as 2 tables:
- the SaleHeader table, with fields such as:
SaleID AutoNumber primary key
ClientID Number relates to the client table (if you
have one)
SaleDate DateTime when this sale took place
Comments Memo any notes about this sale.
- the SaleDetail table, with fields such as:
SaleDetailID AutoNumber primary key
SaleID Number relates to SaleHeader table.
Quantity Number how many tickets
TicketTypeID Text a value like 'kids' or 'adult'
UnitPrice Currency how much each

To interface this, you will have a main form bound to the SaleHeader table,
and a subform bound to the SaleDetail table. The subform will be in
Continuous Form view, so you can add as many rows as you need to the sale.

Create a query to use as the source for the subform.
In query design view, type this expression into the Field row:
Amount: [Quantity] * [UnitPrice]
This gives the calculated amount.

Now in the Form Footer section of the subform, you can get the total with a
text box that has this in the Control Source:
=Sum([Amount])

You must *not* have a single table with repeating fields, such as QtyKids,
QtyAdults and then AmountKids, AmountAdults. Although it is possible to sum
them across in a query, that's how you design a spreadsheet, not a database.

If that does not make sense, open the Northwind sample database that comes
with Access. In the Relationships window, see how Microsoft put the tables
together. The Orders and Order Details illustrates how to design it. Then
the Orders form with its subform illustrates how to interface it.
 
Back
Top