sql ms access select by date and genre

Question

I have two tables:

fID  Enamn   Fnamn bdate
1 Ronge Paus 49-05-14
2 Nesser Håk  50-07-26
3 Bods Thomas 51-05-02

And

ISBN     cathegory
123    Prosa
456    Poesi
789    Thriller

I am trying to select by date and cathegory. The writer shoult be born before year "1950" and the cathegory should be "Poesi"

The result should be:

ISBN cathegory  fID  Enamn   Fnamn bdate
456   poesi      1   Ronge   Paus  49-05-14

I have tried the followin:

SELECT * 
FROM bok, författare
WHERE bdate BETWEEN #01/01/40# AND #01/01/50# AND kategori = 'poesi'

But it didn't work :(

Answer 1

You need a method to match a record from the first table to it's category in the second table. I assumed ISBN is the primary key in the second table, so used it as a foreign key in the first table.

I am unclear about the table names, so I substituted my own names. Notice I also assumed bdate is Date/Time data type. Here's what my versions of the tables look like:

tblA:

fID  Enamn   Fnamn  bdate     fkey_ISBN
1    Ronge   Paus   49-05-14  456
2    Nesser  Håk    50-07-26  123
3    Bods    Thomas 51-05-02  789

tblB:

ISBN   cathegory
123    Prosa
456    Poesi
789    Thriller

Then, to get writers born before year 1950 for cathegory "Poesi", use this query:

SELECT
    a.fkey_ISBN,
    b.cathegory,
    a.fID,
    a.Enamn,
    a.Fnamn,
    a.bdate
FROM
    tblA AS a
    INNER JOIN tblB AS b
    ON a.fkey_ISBN = b.ISBN
WHERE
    a.bdate < #1950/01/01#
    AND b.kategori = 'poesi';

Edit: If ISBN is in fact the primary key of tblB, you may get better performance by using ISBN rather than kategori in your WHERE clause:

WHERE
    a.bdate < #1950/01/01#
    AND b.ISBN = 456

or if IBSN is text rather than numeric data type:

WHERE
    a.bdate < #1950/01/01#
    AND b.ISBN = '456'