Grace- See comments below...
John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of grace_smith8788 Sent: Tuesday, November 22, 2005 11:29 PM To: [email protected] Subject: [ms_access] Some advice about table mapping/relationships needed! Hi all, I have a few questions in relation to a database intended for a company that sells antiques for commission on EBay, acquiring them from the public. If you have the patience to read them and even answer them, that would be fantastic. [1] Part of this database would require a CLIENTS table, which would list the contact details for people from whom antiques were acquired. I had orginally intended to link this, via *ConsignmentID*, to a CONSIGNMENT table, which would incorporate different consignment rates for different categories (e.g trade, personal, public etc). But sometimes individual repeat clients have special consignment rates permanently allocated. For example, Bob Smith from the antique trade might have a consignment rate of 10% because he is such a frequent customer. Would it then be best to link from CLIENTS to CONSIGNMENT via *ClientID*, or via *ConsignmentID*? The complicating factor is that many clients are simply one-offs, and therefore would just have a general public consignment rate. >> You could do both. The rate in the client record is the client's usual rate, and you can override it on particular consignments. All "one off" customers get the standard rate in the client record. Good customers get a lower rate - also in the client record. And when you create a consignment record, propogate the "current" rate from the customer record to the individual consignment. If the consignment is special, you can override the rate up or down for that particular consignment. << [2] Each individual listing has a different postage rate according to the destination. For example, each listing will say that US postage is $15.00, AU postage is $5.00, etc. Sometimes the currency is listed as US dollars (depending on whether it is listed on the US site) and sometimes in AU, for Australia-orientated items. I had intended to have the following structure for listings: ITEMS table links to LISTINGS via *ItemID* LISTINGS table links to SALES via *EbayItemNo* How would you suggest structuring the postage aspect (needed for record-keeping), especially with the currency issue? I had intended to have a field in LISTINGS called *Currency*, to select AU or US. Should I then use a *PostageID* field to link to a POSTAGE table? Or should I just include those fields in the LISTINGS table? (This would cater only for quoted postage, but a different approach would be needed for actual postage costs once the item has been sold). >> Hmmm. Thinking about a listing on eBay, each item might have anywhere from one to a half-dozen shipping entries. You might have a master "postage" table that lists the current rates for a variety of destinations, weights, and sizes. A Listing might need a linking table to allow you to pick several candidate rates for the listing. It's a many-many because each rate might apply to many listings and each listing might have several rates. << [3] There are various calculations that need to occur post-sale. I have the following tables linked: LISTINGS table, to SALES (via *EbayItemNo*) SALES table to BUYERS (via *BuyerID*) SALES includes a *FinalPrice* field. The calculations that need to happen include: a) Calculating GST payable based on the *FinalPrice* field in SALES (e.g SALES.*FinalPrice*/11) b) Calculating international insurance (where applicable) based on SALES.*FinalPrice* and BUYERS.*Country* (where insurance is $7.50 for the first $100 of value, and $2 for every $100 increase thereafter) c) Calculating ebay fees, based on: - Insertion fees - Gallery fees - Reserve Price fees - Final Value fees - PayPal fees d) Calculating various commission amounts, including: gross commission, commission payable to client (less consignment rate), gross commission to company (based on consignment amount less ebay fees). What would be the best approach for such calculations? Should I create a new table for this kind of data, or use fields? If so, into which of the following tables would I be best to insert the fields? CLIENTS CONSIGNMENT ITEMS ITEM_CATEGORIES LISTINGS SALES BUYERS POSTAGE >> This sounds like a one-many table related to Sales. Maybe "SalesDetails" - with a code field that indicates the component charge, and an amount field (+ or -) that indicates the amount related to the sale. One of the entries would be, of course, the sale or final bid price. << Any suggestions/redevelopment ideas would be much appreciated. Thank you! Yahoo! Groups Links ------------------------ Yahoo! Groups Sponsor --------------------~--> Get fast access to your favorite Yahoo! Groups. Make Yahoo! your home page http://us.click.yahoo.com/dpRU5A/wUILAA/yQLSAA/q7folB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
