Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    2

    Question Unanswered: Multiplying fields from separate tables

    Hi there,

    I'm making a database to store bookings for an exhibition. Exhibitors are charged per square metre. The price per square metre is different depending on the location they want to book and I want to be able to calculate the total price for each booking.

    Currently I have a table called Registration which includes a field for the number of sq metres required and the location for each customer, and I have a table called Event Details which has fields for each location and data for their prices. I was hoping it would be possible to write some sort of expression that would be like, if location in Registration is A, total = sq.m*A Price.

    So far I've not even been able to write a working expression that multiplies across tables (notice the info for sq.m is in Registration and Price is in Event Details), that's before even trying to write the 'if' part. Eek! I'm not sure if I'm making this too complicated or if I'm just misunderstanding something fundamental.

    Any advice or direction is greatly appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    providing your table design is correct it shoudl be failry trivial to calculate a customer end price. the knack is to have a spiece of data that joins table A to table B (sometimes that piece of data may link Table A to another table (table C), which in turn links to table. do a google on using the join clause in a query. if you have designed your tables with realtionshps access will even suggests joins for you in the query designer.

    however it comes down to your table design.

    persoabnlly for this sort of thing I'd expect the price per sq metre to be in the same table as the actual booking as I would expect prices to vary with area required and what discount a customer can negotiate. you may well have a target (desired) price per mtre but that may not actually be the rate charged to all customers in the same area.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2011
    Posts
    2
    Thanks very much for the response. Once I got the idea of joins things went much more smoothly and I've been able to proceed with my project. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •