Auction House Help... (1 Viewer)

[mcclunyboy]

Hi,

I am creating a sample app to mimic an Auction House. It is basically an invoicing application with some product(lot) tracking included. I am happy with the customer/lot section having split buyers from sellers to make things a bit more simpler. I now need two sections - seller_invoices and buyer_orders and this is where I am getting stuck.

I have my tables:
-> Tbl_seller_invoice (seller_invoice_id)
-> tbl_seller_invoice_details (seller_invoice_id, seller_invoice_details_id, lot_id)
-> tbl_lots (lot_id)

I basically want the invoice subform (order line) to prepopulate a few of the details from the lot table when a user selects the lot for the invoice. I am able to select the lot just now using a combo box/query but I am unable to pull across the sale price/reserve price etc from the tbl_lots to calculate commission.

Could you check my DB Diagram and let me know where the hell I am going wrong?


EDIT -> I originally did it by assigning all the price/costs/values into the tbl_lots table and then considered just having a total on the seller_invoice_details form? Would this have been correct?

Apologies its been a long time since I have had to work in access this much. I have a lot of what I have set out to do done but I would really like some help.
Thanks,
MC

 

[plog]

The first issue that sticks out is your spider-web structure. There should only be one path between two data sources. From tbl_Sellers to tbl_Lots I see 2 paths, from tbl_employees to tbl_Lots I see 4 paths. Those are issues.

Looking at your tables I see more issues, mostly steeming from the fact that you seperated buyers and sellers. Since tbl_Sellers and tbl_Buyers have the same structure, their data should be in the same table. The same looks true for tbl_seller_invoice_details and tbl_buyer_order_details. It also seems tbl_seller_invoices and tbl_buyer_orders have a lot of similar/redundant fields.

Then from a simple understanding of auctions, I believe lots and auctions should be directly related.

 

[mcclunyboy]

The auction one makes sense, I will change that immediately.

The spider web aspect is because I seperated the two types of auction and they use the same lookups (employee and whatever it is)... Basically the two types of invoices do have certain differences, for example sellers will have a sellers_commission_charge which is differnet than buyers_commission_charge, sellers also have listing/reserve fee's which buyers don't pay. Is it better to use one table with all the above fields and simply a lookup to determine the invoice type.

The buyers/sellers are the same just now - I was going to extend buyers to include bill_to_details/ship_to_details whereas sellers don't need this information.

Thanks for the auction help - not sure how I missed that, it would make my queries easier (i was thinking the invoices are assigned to the auction, not the lots...STUPID).

 

[plog]

Step back and omit the prefixes on those fields:

sellers_commission_charge and buyers_commission_charge = commission charges

If a buyer and a seller each get an invoice, its essentially the same thing. Also, aren't listing/reserve fee's assigned at the Lot level not at the Invoice level? That means it should be moved there.

 

[mcclunyboy]

Step back and omit the prefixes on those fields:

sellers_commission_charge and buyers_commission_charge = commission charges

If a buyer and a seller each get an invoice, its essentially the same thing. Also, aren't listing/reserve fee's assigned at the Lot level not at the Invoice level? That means it should be moved there.

Sounds like what I had...ok thanks...I will re-do the work and see if i can get it to work this evening. I am now struggling to reference a control from a sub-form (using tab pages with a sub-form on each one - referencing one of those controls to produce a report...grrr).

Thanks again - will reply back in a bit as to how I get on

 

[mcclunyboy]

Ok see re-attached for my redesign. I have already corrected the database for most of the changes..however I do have a further query.

The lot table contains the listing_fee, reserve_fee and the reserve_price and hammer_price. I basically want the invoice_detail to contain the following from the lot_table:

- invoices of type "seller"
lot_id_web
lot_name
listing_fee
reserve_fee
hammer_price (if it exceeded reserve_price)

- invoices of type "buyer"
lot_id_web
lot_name
hammer_price

the following will be calculated in the tbl_invoice_details table:
commission_percent
commission_charge

Does that make sense (ignore the selling_price) in the attached file, as it is the same as hammer price I have removed it from that table.

 

[jdraw]

Here is a free data model that may be helpful to you.
http://www.databaseanswers.org/data_models/auction/


Note: See the Facts link on the data model (top) for more info

 

[plog]

That looks better. I have a few questions about the tbl_invoice_details table. I think it has too many fields.

Correct me if I am wrong, but my understanding is that if a seller sells (or buyer buys) 6 lots those could all be on 1 invoice. So tbl_invoice_details is essentially a way to keep track of what lots go to what invoices, correct?

If so, I believe selling_price, commission_percent and commission_charge should be in tbl_lot.

 

[mcclunyboy]

Thanks but as the actual bidding is completed outside the access database (i will just update with final bid to create invoices for some customers) some of it is unnecessary.

It is interesting to note that they seperated bidders/sellers but I guess the column names are different so they are storing different information.

My main problem is now getting the invoice and invoice_details part to work like I hope. I want the sub-form to display the invoice_details alongside more information from tbl_lots (listing fee's etc). I created a basic query which does exactly this but it doesn't work, it only displays the lot name in the sub-form and actually creates a new record in tbl_lots if I save it. (deleted query now). Is it even possible with the diagram I attached (second one).?

Edit - > thanks for everything

Edit -> Plogg you posted ad I almost it, sorry. Your theory is right I will move those columns (incidentally that makes it exactly like I had it originally - no confidence in my work)

 

[mcclunyboy]

I have managed to get what I need using two sub-forms. One as the invoice details, the user selects the lot and a second displaying the lot information based on the sub-forms control value for invoice_id.

It does what I hoped when the user exists the first sub-form and i requery the second with an event.

Is there a better way?!

 

[plog]

Not the best with forms, I'd post any questions you have concerning them in that section of this forum. However, I'd advise you make sure your table structure is sound before you start building forms and reports on top of it.