1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

MS ACCESS: A7 Building Queries in Microsoft Access

Discussion in 'Business Applications' started by Lanso, Nov 6, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. Lanso

    Lanso Thread Starter

    Joined:
    Nov 6, 2011
    Messages:
    2
    Hello, I'm having problems with creating a database from this questions. I was able to do get to a point but won't show or missing something. Can anyone help on these questions:

    2. Display the CustomerNum, CustomerName, OwnerName, Phone, RestaurantType, and FirstContact of customers who were first contacted in 1992 and 1993. The list should be sorted by the FirstContact date (with the most recent at the top) within RestaurantType in ascending order.

    SQL Code I have, but won't show anything.

    SELECT Customer.[CustomerNum], Customer.[CustomerName], Customer.[OwnerName], Customer.[Phone], Customer.[Restaurant Type], Customer.[FirstContact]
    FROM Customer
    WHERE FirstContact >1/1/1992 AND FirstContact < 12/31/1993
    ORDER BY Customer.[Restaurant Type];





    3. Write this query in SQL. It should be your SQL code and not the one generated by Microsoft Access. Show the CustomerNum, OrderNum, and InvoiceAmt for orders that were placed by customers with CustomerNum in the 500s (that is numbers that fall between 500 and 599) or valued at $2,000 or more. Sort the list by CustomerNum in ascending order.

    SQL Code that I have but missing the value at 2,000 or more:

    SELECT CustomerNum, OrderNum, InvoiceAmt
    FROM [Order]
    WHERE CustomerNum > '500' AND CustomerNum < '599'
    ORDER BY CustomerNum;


    4.Display CustomerNum, Paid, InvoiceAmt, and Loyalty Reward for customers who have paid their bills and had order values of $1,800 or more. The reward is calculated using the formula (InvoiceAmt x 1%) - $5. The column displaying the reward should carry the label "Loyalty Reward"and the reward values should be formatted to two decimal places. Sort the list by CustomerNum in ascending order.


    5. Show the CustomerNum, OwnerName, Kitchen Area, InvoiceAmt, and ZipCode for customers who are located in ZipCodes starting with 43 and have InvoiceAmt between $1,200 and $2,000. The list should be sorted by CustomerNum.

    SQL Code that I used(not in the file) but missing the starting zip code but still won't show anything:

    SELECT [Customer Query].CustomerNum, [Customer Query].OwnerName, Customer.ZipCode, [Restaurant Category].[Kitchen Area (sft)], Order.InvoiceAmt
    FROM ([Customer Query] INNER JOIN ([Restaurant Category] INNER JOIN Customer ON [Restaurant Category].[Restaurant Type] = Customer.[Restaurant Type]) ON [Customer Query].[CustomerNum] = Customer.[CustomerNum]) INNER JOIN [Order] ON Customer.[CustomerNum] = Order.[CustomerNum]
    WHERE InvoiceAmt BETWEEN 1200 AND 2000
    ORDER BY [Customer Query].CustomerNum;


    Attached is the File on a zip as access file is not accepted.
    The queries I'm working from that I made for some of these are Customer Query, Restaurant Type, Order Query 1.
    Thanks this will be a lot of help as I've been trying to do this for the last 3 days.
     

    Attached Files:

  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Is this for a school assignment?
     
  3. Lanso

    Lanso Thread Starter

    Joined:
    Nov 6, 2011
    Messages:
    2
    yes
     
  4. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Sorry, can't help with school work. The whole point of going to school is learning something on your own and getting a grade based on YOUR efforts.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1025708

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice