Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    PRINCE SWAGG is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2011
    Posts
    17

    Question Database advice

    Hey guys i need some quick assistance. I got a presentation to do on wednesday.

    i am doing a database for a catering company. the database has to be able to take orders, add customers and items to it. I used the entities Customer, Order, Order Line and Product. Order to Product is many a many so i created a link entity which is OrderLine. I am currently creating a form to take orders and i need the Unit Price to be calculated when an employee puts in the product item and then the quantity. Can anyone please assist me in doing this, i tried the expression builder but i am not getting through.



    Also how can i make it possible that an order can take more than one product because obviously a customer would order more than one item.

    ANY ASSISTANCE WOULD BE THANKFUL AND I AM PATIENTLY WAITING FOR A RESPONSE.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    UnitPrice is an attribute of Product and should be a field in Product table. What you want to calculate is the extended amount - Quantity x UnitPrice.

    Conventional practice with database is to use forms to enter raw data and present calculations of data on reports. Tables and queries are the RecordSources for forms and reports. Calculations are done in queries or in textboxes on forms/reports.

    You need to understand table relationships and building queries that retrieve fields from related tables for viewing/data entry/edit in forms and reports. You probably need a form/subform arrangement for order entry. Main form would have RecordSource of the order table and subform RecordSource would be the OrderLine table with a join to the Product table.

    If haven't yet, you might look at some Microsoft Access database templates for ideas. Here is link to the Northwind database. Lots of tutorials are based on it http://office.microsoft.com/en-us/te...x?qu=northwind
    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.

  3. #3
    PRINCE SWAGG is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2011
    Posts
    17
    I sort of understand what you are saying but still a little confused.
    I attached the Relationship i did. Please leave any advice or assistance.
    THANKS IN ADVANCE

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Compound key of CustomerID and Date might work but I think compound keys are cumbersome and confusing, especially with a date component. So I avoid them.

    tblCustomers
    CustomerID (primary key)
    CustomerName
    Address
    TelNo

    tblProducts
    ProductID (primary key)
    ProductName
    UnitPrice

    tblOrders
    OrderID (primary key)
    CustomerID (foreign key)
    OrderDate
    Notes

    tblOrderLine
    OrderID (foreign key)
    ProductID (foreign key)
    Quantity

    The primary keys can be generated by an Autonumber datatype field.

    Subtotal and Ordertotal are calculations that would be accomplished in a report output.
    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. #5
    PRINCE SWAGG is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2011
    Posts
    17
    I will definitely try your way. But would i be able to allow a order to take many products eg like showing the multiple fields on the form for products. In other words having Product 1, Product 2, Product 3 etc and a Quantity field by each one?

    Ok so when i am creating the Order Form for the employees to use, would it be a combination of the Order Table, OrderLine and Product Table? So i can get Order Date, Product/s, Quantity/ies and an Order Total?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    tblOrderLine will hold records for each product for each order. Each record in the table will be a single product.

    The order form I suggest is a form with a RecordSource of tblOrders with a join to tblCustomers. On this form would be a subform with a RecordSource of tblOrderLine with a join to tblProducts. Records of the two forms would be synchronized with the Master/Child properties of the subform container control. Do some research on web and in Access Help on building forms and subforms.
    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.

  7. #7
    PRINCE SWAGG is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2011
    Posts
    17
    Ok thanks very much, i am going to see if i can get a break through today. Thanks again.

  8. #8
    PRINCE SWAGG is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2011
    Posts
    17
    Well i tried my best to follow but i am getting a little problem in the Sub form. I got it to show the Combo Box of the ProductID and then the column for Quantity, but i am not getting others rows, so the employee and add other Products to the customers orders.

    Any assistance?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Can you attach project to post for download? Be sure to remove confidential data and run Compact & Repair on the file first.
    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.

  10. #10
    PRINCE SWAGG is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2011
    Posts
    17
    i have not populated the database really as yet, all it has is the products and unit cost.
    Thanks in advance and i hope you can really help me.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I am attaching a revision of your project. Things I addressed:

    The subform controls were not bound. Changed RecordSource and RowSource sqls. Changed textbox to combobox or the reverse as needed. Formatted comboboxes as multi-column.

    I had to zip the file because I am working in 2010. If ran C&R to reduce file size you would not be able to open it in 2007.

    If want the extended price to show the TT prefix, format the textbox to do that.

    I did not include the Customer table in the RecordSource for the main form. But you could do that if you want to show more customer info, such as address, in locked textboxes. This is how I show the UnitPrice in the subform, by joining OrderLine with Product.

    I will remove the file after you respond or in a few days.
    EDIT: Purpose served, file removed.
    Last edited by June7; 06-19-2011 at 10:54 PM.
    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.

  12. #12
    PRINCE SWAGG is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2011
    Posts
    17
    THANKS SO MUCH, i am going to check it out and make sure i understand, because i really want to learn this. I will let you know if i was fully successful in finishing it or if i need any more assistance.
    Thanks again.

  13. #13
    PRINCE SWAGG is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2011
    Posts
    17
    Ok, here is where i am not understanding what to do. I saw you had customer in the form, but it was a combobox and you could only select cust names which you created. I saw u ran a query wit the custid and custname.

    But i need the employee to be able to put in the customers name in the order. It has to be new because it will be new customers more less making an order. I was trying to get CustomerName instead so when saving the order, the order details will have the customers name. Is it possible you can help me out with that?

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    This can be handled several ways. One way is a form/subform/subsubform arrangement. I have one project that does this. Main form would be bound to Customer table, subform would be Order form (eliminate the CustomerID combobox), subsubform would be OrderLine form. This is simple and easy to accomplish as no code is required (use the form/subform I set up as a guide). Searching/filtering/record navigation relies on the built-in Access functionality for these behaviors. But most users don't find this framework very friendly. Getting away from the intrinsic Access tools requires customization with code (VBA or macros). You would have a menu (switchboard) of command buttons (New Customer, New Order, Find Customer, Run Report, etc.). You could have command buttons on the data entry forms that will open other data entry forms or run reports. All this requires complex code and the project has a more Windows-like GUI look and feel. This takes time and knowledge of db principles, programming concepts, VBA language or Access macros structure.

    You say you are doing this for a catering company. Are you an employee of this company? Will you be one of the regular users? How many people will be using this database? How familiar are these users with Access and its built-in tools? What is your knowledge level on the items I listed?
    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.

  15. #15
    PRINCE SWAGG is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2011
    Posts
    17
    Ok well, i made a break through but i need you to check some stuff for me please. I got through with the form and subforms but i need you to check the ORDER FORM. Which includes the ORDERLINE FORM:
    1. I have it set to Continous form but i need it to stop and not keep continuing.
    2. I am getting problems with the Unit Total, when filling out the form and putting the quantity it does not generate but when u check in the Orderline form it is generated and maybe when u check back in the form u see it. But its not tallying immediately on the screen.
    3. Below the Ordeline form i have a text box "Total Order Cost" is it possible to get it to sum all of the Product total so the employee can see the cost of the total order?

    The db is to be used by one user who is not really an access expert, more less an amateur.
    No i dont work in the company. Yes its a catering company.

    Thanks in advance and i am patiently waiting to get a response cause i really need to finish this asap. I what to stay up now and finish it more less.
    Last edited by PRINCE SWAGG; 06-19-2011 at 11:54 PM. Reason: Removed Attachment. Reason has been completed

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Security Advice
    By jpkeller55 in forum Access
    Replies: 6
    Last Post: 02-18-2011, 08:22 AM
  2. Table creation advice
    By Padawan in forum Access
    Replies: 6
    Last Post: 01-27-2011, 06:16 PM
  3. Form advice
    By rvangend in forum Forms
    Replies: 4
    Last Post: 01-17-2011, 01:59 PM
  4. design advice
    By Sarge, USMC in forum Database Design
    Replies: 4
    Last Post: 10-06-2010, 07:53 AM
  5. Need advice on what I have so far
    By rumplestiltskin in forum Database Design
    Replies: 2
    Last Post: 05-25-2006, 12:48 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