I have a database with two tables, the first is called 'auction' and is a reference to an order I made on ebay. It contains the auction details and, importantly, the total amount I paid for that auction. It looks like:

id title total cost
1 some auction £10 

Then I have another table for all the items within that auction (typically it'll be a bundle of items) like so:

id title percentage of auction
1 book 40%
2 small book 20%
3 bookmark 40%

I would like, although I don't think it's possible, to add a calculated field to the item table to work out auction.total_cost * item.percentage_of_auction. So in the above example the small book would have a calculated value of £2 (20% * £10).

From what I can see it's not possible to reference another table in a calculated field, so my question is how should I go about this?

I'm thinking adding in an extra field to the item table that contains the auction.totalcost. It's not ideal, but it would be functional and this isn't going to be an enormous database so I can live with a little bloat. But how can I pull the total cost in automatically when creating an item, and will it update?

Or alternatively is there another way of doing this.

I would like to add further calculated fields based on the 'percentage of auction' field within the item table so actually having an entry I can run calculations on would my life a lot easier than creating a query to do this, and then having to create several more queries based on that rather than a few simple calculated fields.

This is only a personal project so I'm not too worried about normalising data, and can live with something a bit hacky.

1

1 Answer

OK so you have two tables:

• Auction
• Items

Here are my suggestions

#1 rename the id fields to reflect that of the table names

so the first column in each table would be renamed as

AuctionID and ItemID

respectively

#2 in the Items table, add one more field at the end to hold the related AuctionID

This is called a "foreign key" and used to link tables together relationally

You can name this field exactly the same i.e. AuctionID

But I prefer to name it close-but-slightly-different for uniqueness, to make it simpler when creating complex queries (by not requiring the tablename.fieldname when there are two identically named fieldnames)

So lets name this new field at the end of the Item table as AuctID

The data type for this new AuctID field should be Number and Long Integer (and definitely NOT autonumber)

Now fill in the columns to match the auction ID and your Items table will now look like this

id title percentage of auction AuctID
1 book 40% 1
2 small book 20% 1
3 bookmark 40% 1 

Now create a Query

Add both the Auction and Items tables to this query

Drag a line between the specific join field in each table i.e. AuctionID and AuctID

Now in the bottom of the Query-By-Example grid, you can drop the required fields from both tables in there.

And your desired calculated field can be entered as this

ItemValue: [percentage of auction] * [total cost]

P.S. To avoid the need for [square brackets] around fields, I prefer to remove all spaces field names and table names,

So I would rename fields like "percentage of auction" as either percentage_of_auction or PercentageOfAuction

Then your calculated field in the query could be typed as

ItemValue: PercentageOfAuction * TotalCost

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy