creating sql queries

we have our accounts database in MS Access 2007. There are different tables.
i want to learn to create a SQL query so that i can fetch desired data from these tables.

please suggest
amit singlaAsked:
Who is Participating?
Dale FyeCommented:
Writing queries in SQL can be daunting, and sometimes frustrating, but in order to develop queries you also need to understand joins.  There are basically two types:

INNER JOIN: this type of join connects data in two tables based on a common field.  In the table where this field is defined, it is referred to as the "primary key", in the other table, it is referred to as the "foreign key".  This is the default join you get when you click on one field in the query designer and drag it to the associated field in the other table.

As an example, suppose you have a table of clients, in that table you would normally have a ClientID field (probably an autonumber), and because you don't want to repeat that ClientID, you make it the primary key of the table (which guarantees it cannot be used again in that table).  Then, you might have a Sales table, and in that sales table, instead of storing the Clients name, you would store this ClientID field (as the foreign key); you do this to save space in the database.

If you wanted to get a list of sales with the names of all the clients, you would use a query like:
SELECT Sales.*, Clients.ClientName
FROM Sales INNER JOIN Clients on Sales.ClientID = Clients.ClientID

Open in new window

OUTER JOIN:  Outer joins are designed to get all of the records from one table, and only those which match on the PK-FK relationship in another table.  To create an outer join in the query designer, click and drag on the ClientID field in one table, then drag and release on the ClientID in the other table;  this will actually create an INNER JOIN.  Then right click on the line between the tables and select JOIN Properties.  This will display a dialog box which gives you three options:
1. Only include rows where the joined fields from both tables are equal  (INNER JOIN)
2.  Include all records from Clients and only those from Sales which match
3.  Include all records from Sales and only those from Clients which match

Open in new window

Select #2

So, with the same example as above, lets say you want a list of all of your clients, and whether they have any sales.  You could write that query like:
SELECT Clients.ClientID, Clients.ClientName, Sales.*
FROM Clients LEFT OUTER JOIN Sales on Clients.ClientID = Sales.ClientID

Open in new window

This query would return a list of all your clients and will include each record in the Sales table with each record in the Clients table.  So, if a particular client has 10 sales, then that client will show up 10 times in this query.  But if a particular client has no sales records, then you would still see that clients ClientID and ClientName, but there would be nothing (NULL) values in all of the fields associated with the Sales table.

If you wanted a list of all of your clients, and the number of records associated with each client in your sales table, then you would use:

SELECT ClientID, ClientName, Count(Sales.SalesID)
FROM Clients LEFT OUTER JOIN Sales on Clients.ClientID = Sales.ClientID
GROUP BY ClientID, ClientName

Open in new window

Use the query designer, then change the view to SQL view.
Bill PrewCommented:
Here is a great tutorial on understanding and building Access queries.  There are versions of the tutorial for both Access 2007 and 2016,  Hope it helps.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.