Order Database Design - does this look reasonable

[SHimmer45]

Hi all,


attempting to design a simple order database and having some issues with reporting in Access 2010 before i start pulling the underlying structure about i needed a second opinion on the design as it been a long time since i actually designed a structure for a database.



Does this look reasonable?

The problem with the reporting is certain items appear grouped together which means i cant change how the report looks easily. (for example building the report with DEPT - ORDER - ITEM doesnt look correct as the order total appears before the item detail with the break down of the items within the order.

might it be that i have got an unseen "many to many" relationship?

regards

[pbaldy]

I wouldn't store the order total (or subtotal, whatever that is). I would calculate it from the line items. Presumably that's why you're seeing it before them. I've made that mistake, and I ended up regretting it. Users have a magical ability to find ways around whatever methods you might employ to keep the calculated value in balance with the line items.

[AccessMSSQL]

You need to group your report by Order_Num with an Order_Num header and footer and then add an unbound textbox to the footer with control source = "=Sum([Item_Qt]*[Item_Cost])". Your items table fields would go in the detail section of the report.

[SHimmer45]

ok thanks for the help, going to set about getting head around reporting again...

[orange]

You may get some ideas here

http://www.databaseanswers.org/data_...ucts/index.htm

[ssanfu]

I would not use a text field to link tables. I would use "Dept_ID" and "Supplier_ID" to be the FK in the "Order" table.

See http://www.accessmvp.com/Strive4Peac...Chapter_03.pdf , scroll down to "Data Type for Key Fields"

My $0.02 worth