Basic query ...

[hello]

Can anyone help a newbie .... I have to produce a list of films
currently available for loan. Members can borrow films, only one at a
time though. When a film has been borrow, a tick is put against that
member's field. Also, the details are stored in table 2. I need to be
able to print out all of the films where the film ID doesn't appear in
table 2.


Table 1
Mem_ID
Various fields for contact details
A field that says'Has taken out a film'

Table 2
Mem_ID
Film_ID
Date due back

Table 3
Film_ID
Fields about the film.

I think the easiest solution is to put an extra filed in the film table
that says 'Film_on_loan' and use that in a simple query. What I've been
trying to do is to produce a list of all film IDs that are not in table
2, and use that to produce all the film details in a query using table
3, but can't!

Any pointers?

Cheers

 

[Tom Lake]

Can anyone help a newbie .... I have to produce a list of films
currently available for loan. Members can borrow films, only one at a
time though. When a film has been borrow, a tick is put against that
member's field. Also, the details are stored in table 2. I need to be
able to print out all of the films where the film ID doesn't appear in
table 2.

This should do it. It's called an Unmatched Query.

SELECT Table3.Film_ID, Table3.Title, Table3.Director, Table3.YearProduced
FROM Table3 LEFT JOIN Table2 ON Table3.Film_ID = Table2.Film_ID
WHERE (((Table2.Film_ID) Is Null));

Tom Lake

 

[Douglas J. Steele]

Putting a field in Table 1 to indicate whether or not they've taken out a
film isn't actually that good an idea: if that field disagrees with what's
in Table 2, which are you going to believe?

To get a list of those members who currently have films check out, your SQL
would look like:

SELECT Mem_ID, Count(Film_ID) AS NumberCheckedOut
FROM [Table 1] LEFT JOIN [Table 2]
ON [Table 1].Mem_ID = [Table 2].Mem_ID

To get a list of those members who don't have any films checked out, your
SQL would look like:

SELECT Mem_ID
FROM [Table 1] LEFT JOIN [Table 2]
ON [Table 1].Mem_ID = [Table 2].Mem_ID
WHERE [Table 2].Film_ID IS NULL

In both cases, you can create the query through the graphical query builder
by dragging both Table 1 and Table 2 into the sections at the top of the
builder. If no line links the two tables (because you haven't created a
relationship between them), drag the Mem_ID column from Table 1 onto the
Mem_ID column in Table 2. Once you have a line between the two tables,
select that line, right-click the mouse and select Join Properties. Select
the radio button corresponding to "Include ALL records from 'Table 1' and
only those records from 'Table 2' where the joined fields are equal.' Doing
this should put an arrow at the end of the line connected to Table 2.

To get the first query, change your query to a Totals query, either by
clicking on the Sigma button on the button bar, or by selecting Totals on
the View menu. Drag Mem_ID from Table 1 and Film_ID from Table 2 into the
grid. Change the choice in the Total row under Film_ID from Group By to
Count. If you want to alias the field (as I've done in the SQL above), put
NumberCheckedOut: in front of Film_ID in the top row (DO NOT REPLACE WHAT'S
THERE: PUT IT IN FRONT!)

To get the second query, you don't need to change your query into a Totals
query. Drag Mem_ID from Table 1 and Film_ID from Table 2 into the grid, and
place IS NULL in the criteria field under Film_ID

To get a list of films NOT in Table 2, you do essentially the same as in the
second query above. Drag Table 2 and Table 3 into the top part of the
graphical query builder, and make sure there's a line connecting the two
tables. Change the property of the Join to "Include ALL records from 'Table
3' and only those records from 'Table 2' where the joined fields are equal.'
Doing this should again put an arrow at the end of the line connected to
Table 2. Drag whatever fields you want from Table 3 into the grid, and drag
either the Film_ID or Mem_ID field from Table 2 into the grid. Under
whichever field you dragged from Table 2, put IS NULL in the criteria field.

The SQL would look like

SELECT Film_ID, <other fields from Table 3>
FROM [Table 3] LEFT JOIN [Table 2]
ON [Table 3].Film_ID = [Table 2].Film_ID
WHERE [Table 2].Film_ID IS NULL