Help Required in creating queries in access 2016

[shahidnc]

Hi Guys,
I am working on an assignment and stuck on a query. Assignment is "DECORATIVE CONCRETE DATABASE" from the book "Problem Solving Cases in MS Access & Excel"

I am stuck at following queries.

screenshots are attached for the forums I have generated as well as DB file in a ZIP.

Query 2
Create a query called Number of Jobs. Specify a type of concrete and count the number of jobs for that type.
Your query should prompt for the type of concrete so that the owners can run the query for different types.
Note the column heading change from the default setting provided by the query generator. If you enter “Basic” as the type of concrete.




Query 3
Create a query called Most Lucrative Product that specifies each type of concrete and then shows the total square footage and total cost for all jobs that use each type of concrete. (Hint: The Total Cost field is a calculated field with aggregated data.) Note the column heading change from the default setting provided by the query generator. Sort your output to show the most lucrative product first.


Thanks in Advance

[alansidman]

At this link, scroll down to Group By Features to learn how to do these queries.

http://www.techonthenet.com/access/queries/index.php

Since this appears to be a homework assignment, I feel that learning by doing is more beneficial than providing you with the correct solution.

[shahidnc]

Dear alansidman
Thanks for your reply. This is something I am trying since yesterday. Nothing seems to be working. If you can point me out to right direction I'll be thankful.

[alansidman]

Have you looked at the tutorial I provided you? What does your query look like now. Post your current SQL Statement for analysis.

[shahidnc]

I just have added a new picture. :/ not working

[alansidman]

I would suggest you try this for the first query:

SELECT [Jobs Data].Type, Count([Jobs Data].Type) AS CountOfType
FROM [Jobs Data]
GROUP BY [Jobs Data].Type
HAVING ((([Jobs Data].Type)=[What type of Job?]));


and this for the second query:

SELECT [Jobs Data].Type, Sum([Jobs Data].[Square Footage]) AS [SumOfSquare Footage], Sum(([Concrete Types&Prices].[Price/Sq ft])*([Jobs Data].[Square Footage])) AS Total
FROM [Concrete Types&Prices] INNER JOIN [Jobs Data] ON [Concrete Types&Prices].Type = [Jobs Data].Type
GROUP BY [Jobs Data].Type;


By giving you this, I am sure it will help with you completing your assignment, but I am unsure if it will benefit you in learning about grouping queries. Read the tutorial again and see if you can understand what I have presented to you.

As a side note: When naming fields, it is dangerous to use spacing and non text or non numerical characters. I prefer the Camel Syntex when creating fields, ie. FirstName, LastName, etc.

[shahidnc]

Hi,
thanks a lot for your suggestions but things aren't working.. I couldn't figure it out that which formula where to put. I tried with so different conbinations but getting errors. screenshot attached.
I am a business student and I took Database course from higher semester. and it is getting hard to understand as I have very basic knowledge for writing codes.

[alansidman]

See your database attached with the query properly shown.

[shahidnc]

Thanks a lot. can you give me some links to easy understanding of SQL basics? Thanks

[alansidman]

Look here---->http://www.w3schools.com/sql/default.asp

Nearly all databases use SQL of some sort. In Access you have a user interface that lets you build your queries in a grid, and simultaneously builds the query in SQL so that the database can run the query.