Page 2 of 5 FirstFirst 12345 LastLast
Results 16 to 30 of 73
  1. #16
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    You still need the Orders table, I hope you did not eliminate it. Orders table has general info about the order - customerID, order date, ship date, paymentID. The OrderDetails would have a record for each book for each order - 5 books for OrderID 218 means 5 records. Each record would have the OrderID and BookID.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  2. #17
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As I said in post #9

    As June7 mentioned, you will have OrderDetails (LineItems), and it will be related to Books.
    Orders --->OrderDetails<----Books


    say Order #6
    is related to OrderDetail#1 with Book ABC's of Database
    OrderDetail#2 with Book DataBase For Professionals
    OrderDetail#3 with Book Chinese Cooking

    where each of these Books is uniquely identified in Table Books.


    OOPs: I see June7 and I were posting/responding at the same time

  3. #18
    ilikeshinythings is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    37
    I think i get it now!!

    for example the table would be like this:

    Order details
    ORDER_ID-------- BOOK_ID------------BOOK_TITLE
    1-----------------4 ------------------ Harry Potter
    1-----------------6------------------- Bible
    1 --------------- 12 ------------------ Databases For Dummies


    ???

    and then the ORDERS table would be like:

    Orders
    ORDER_ID------ORDER_DATE------SHIPPING_DATE------AMOUNT_DUE-----PAYMENT_ID-------CUSTOMER_ID
    1---------------22/3/2012--------25/3/2012-----------£50-------------- 4------------------2
    2-------------- 19/3/2012--------21/3/2012-----------£8.50------------- 6----------------- 4


    ??

    Also is there any way that the AMOUNT_DUE field in the ORDERS table could automatically calculate the price of all of the books included in the order? can this be done with SQL? because otherwise the user would have to keep a price list and add up the prices of the books.


    And also how would I go about creating a form for entering this data? what table would it be based around and how could i make the IDs be entered automatically ( for example if i were to create a New Order Form what fields would be needed and if it needed a CUSTOMER_ID to be associated with the order how would I go about adding the customer to the order if its a new customer?)

    Thanks for the help and sorry for all the questions
    Last edited by ilikeshinythings; 03-22-2012 at 05:21 PM. Reason: Formatting was wrong

  4. #19
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    Do not save book title in OrderDetails. That info can be retrieved in queries that join the related tables.

    Use form/subform arrangements to enter records. Main form bound to Orders table, subform bound to OrderDetails. Master/Child links of the subform container control will synchronize display of related records. The OrderID will automatically save to the foreign key field of OrderDetails as soon as data entered in another field.

    Order total can be calculated on form and report. Do not save this to Orders table.

    I suggest you work through some tutorials and examine template databases to get familiar with these Access basics and get comfortable with relational database concepts. We could go on forever with these questions dealing with the minutiae of database design.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #20
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As for OrderDetails, you don't need Book Title, you can get that from Books using the BookID from OrderDetails record. Howev er, you could have the Cost/Amount for this Book in the OrderDetails table. That would indicate the price of that book on the date of the Order. The Book price can change with time, so keeping the selling price with the OrderDetail reflects the proice at the time of the sale.
    You would use a query to sum up the Prices of the Books in the OrderDetails to get the Total cost for the Order.
    We don't store calculated values (note the Book price in the OrderDetails is "sort of an exception" but it handles changes in the Book price.

    Look up Autonumber in Access regarding automatic numbering.

    Again....OK... June7 is a faster typer than me, so I'm going to back off for a while.

  6. #21
    ilikeshinythings is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    37
    Ok guys thank you, looks like I need to learn more about Queries.

    So I shouldn't have total price in orders table I should use a query to add up all of the book prices with the same order_id and display that where?


    Also if i do not have book title in the form how would the user know what book it is that they are adding to the order? they wont have all of the books and book_ids memorized? thats why i added book title in there so they could just add the title but is there an alternative?

  7. #22
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    All that is handled through design of forms and reports. Explore multi-column combobox for display of book name as alias to the bookID on OrderDetails subform.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #23
    ilikeshinythings is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    37
    Oh right you can use alias'? that's pretty cool ok sorry to be a pain by asking yet another question but for every order there needs to be a new payment entry in the payments table so how would I go about incorporating that into my form so that the user didnt have to click back to the main page after entering the order and order details?

    This is kind of confusing but atleast I have a good working ERD now

  9. #24
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  10. #25
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    Since you have it set up that each order has one payment, simplest is to just include the payment info in the Orders table (assumes every order will always be paid for), otherwise, another subform is called for.

    This site has some interesting tutorials http://datapigtechnologies.com/AccessMain.htm
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #26
    ilikeshinythings is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    37
    Ok but I am supposed to have my database to 3rd normal form and having payment information in the order table doesnt conform to 3NF does it?? and ill have a look at those tutorials about queries and forms thanks.


    and Orange I would but I currently have it in post it notes on my desk haha I'll quickly russle something up and post it though

  12. #27
    ilikeshinythings is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    37
    Here is the current ERD I think everything is correct on it that we have discussed
    Click image for larger version. 

Name:	ERD.jpg 
Views:	14 
Size:	42.4 KB 
ID:	6848

  13. #28
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    If you have repeat customers then this is still not in 3rd normal. Payments table would have to repeat the card number, security number, expiry date for each order if same card used. To allow for repeat customers put card info with the customer table or in another child table to allow multiple card records per customer (in case they use AMEX one time and MC another). As long as each order and payment are 1-to-1 and each order will always be paid, only one table needed.

    None of this deals with the other side of business operation - buying books for inventory and inventory tracking for multiple copies of book (think Amazon inventory).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #29
    ilikeshinythings is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    37
    Yeah the scenario only says its for a 1 employee book shop so I dont think it needs stuff like suppliers and tracking inventories etc. just simple stuff but yeah I think im going to just add the payment info (minus the amount and payment ID) to the customers table

  15. #30
    ilikeshinythings is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    37
    Here's my new ERD with the payment information within the customers table. Although this means customers can only pay with one type of payment I don't think it will matter.
    Click image for larger version. 

Name:	ERD V2.jpg 
Views:	12 
Size:	32.8 KB 
ID:	6851



    Does this mean I will need Orders, Order Details and Customers all on one form to enter all of the data without keep swapping forms?

Page 2 of 5 FirstFirst 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-20-2012, 11:09 PM
  2. Weather Database Ideas
    By bigroo in forum Database Design
    Replies: 4
    Last Post: 01-19-2012, 09:43 AM
  3. Ideas for database creation
    By randolphoralph in forum Access
    Replies: 1
    Last Post: 08-31-2011, 02:08 PM
  4. Any ideas?
    By eripsni in forum Access
    Replies: 9
    Last Post: 08-25-2011, 08:33 AM
  5. Piano Shop Database
    By tomself1 in forum Database Design
    Replies: 1
    Last Post: 02-19-2010, 08:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums