Thanks very much John! Your advice was very useful. Best wishes,
Grace --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > 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/
