query over two tables

[kittyyo]

Hi -
i'm bad in describing problems but I'll try it that way:

One table contains names such as
123 || Bush | William
124 || Megan | Jude
125 || Rowe | John
The other table is related to the above in 1:n and contains dates:
1 || 2002/02/14 | Date1 | 123
2 || 2002/02/12 | Date2 | 123
3 || 2002/02/13 | Date3 | 124
4 || 2002/04/13 | Date4 | 125

What I want is a query which delivers for a specific month, say 2 (February) and a specific year, say 2002, all names that have at least one date in this month, so in this example it would deliver:
123 | Bush | William
124 | Megan | Jude.

I just don't have an idea how to do this! Could someone help me?

 

[BNPMike]

kittyo

You can get the 123.. Bush William easily enough as I'm sure you realise by just joining the two tables. To get rid of 'duplicates' eg the two 123 items for February, add the keyword Distinct to the select statement.

 

[KyleS]

Hi,
Just replace "Table2" with the name of your first table and "Table3" with the name of your second table, and the field names as well...

SELECT Table2.ID, Table2.FName, Table2.LName
FROM Table2 INNER JOIN Table3 ON Table2.ID = Table3.FK
WHERE ((DatePart("yyyy",table3.date)=2002 And DatePart("m",table3.date)=2));


Also, I'm not sure how you want the dates to be selected, if they're from a form then it needs to look like this:

SELECT Table2.ID, Table2.FName, Table2.LName
FROM Table2 INNER JOIN Table3 ON Table2.ID = Table3.FK
WHERE ((DatePart("yyyy",table3.date)= DatePart("yyyy",Forms!YOURFORMNAME!YOURFIELDNAME) And DatePart("m",table3.date)=DatePart("m",Forms!YOURFORMNAME!YOURFIELDNAME) ));


Let me know how you're asking your users for the information and I can give you the exact SQL...

Hope this helps,
Kyle :

 

[shiningblade]

Here is the SQL that I came up with:

SELECT DISTINCT Table1.ID, Table1.last, Table1.first
FROM Table2 LEFT JOIN Table1 ON Table2.nameID = Table1.ID
WHERE (((DatePart("m",[Table2].[date1]))=2) AND ((DatePart("yyyy",[Table2].[date1]))=2002));

The field names for the tables are:

Table 1
ID|| last | first

Table 2
tID|| date1 | value | nameID

I populated the tables with the data you provided and this worked.

HTH,

Jude

 

[kittyyo]

Hi Mike, Kyle, and Jude,

thanks to you all for the answers and the code! Now I know that it was the "Distinct" I didn't know about. In fact I'm providing the parameters (month and year) by two combo boxes, but now I know how to do it all.

Thanks again,
Anne