problem in extract records from different tables

[Nebil]

I've a problem in getting a record from 3 tables:
There are two conditions for getting the records,'project number' and 'current month'.I used this statement
to get the records but it results in 3 times the actual record.
Any help would be appreciated.

"INSERT INTO patempTable SELECT [pay_roll].[project number],[pay_roll].[employee number],[pay_roll].[current month],[pay_roll].[net pay]," & _
            "[allowance].[net allowance],[per_diem_accomodation].[net] FROM [pay_roll],[allowance],[per_diem_accomodation] " & _
            "WHERE [pay_roll].[project number]='P07' AND [allowance].[project number]='P07' AND [per_diem_accomodation].[project number]='P07' AND " & _
            "MONTH([pay_roll].[current month])='6' AND MONTH([allowance].[current month])='6' AND MONTH([per_diem_accomodation].[current month])='6' AND " & _
            "YEAR([pay_roll].[current month])='2014' AND YEAR([allowance].[current month])='2014' AND YEAR([per_diem_accomodation].[current month])='2014'"

[Begginnerdev]

You can condense your query a little by giving your select tables an alias.

For example:

"INSERT INTO patempTable SELECT pr.[Project Number],pr.[Employee Number],pr.[Current Month],pr.[Net Pay],al.[Net Allowance], pd.[Net] FROM [Pay_roll] pr, [Allowance] al, [Per_diem_accomodation]"

Now for tieing the tables together, my question would be: Are there multiple 'P' codes or are you just wanting to select where everything matches?

If you are just wanting to select where everything matches you can simply do something like this:

" WHERE (pd.[Project Number] = al.[Project Number] AND al.[Project Number] = pr.[Project Number])"

The ambiguity comes in with the selection by month/year.

You can try to wrap these statements with parenthesis for qualification.

For example (When added to strings above):

" AND (pr.[Current Month] = '6' AND al.[Current Month]='6' AND pd.[Current Month]='6')"

[Nebil]

Yes there are multiple 'P' codes in the table and i wanted a specific record to be extracted.Plus i wanted to pick the records based on a given month and year.
Must i not use the month and year function rather than giving it a plain value,since 'current month' is a date field.

[Begginnerdev]

If you wish to only select the month portion of a date field, you must do the following:

" WHERE (MONTH(pr.[Current Month]) = 6 AND MONTH(al.[Current Month]) = 6 AND MONTH(pd.[Current Month]) = 6"

Here is a the white paper for the MONTH function.

Please note that this applies to SQL 2005 +

[Nebil]

i still get the same result.any modification in way of wirting the code?

[Begginnerdev]

Have you copied/pasted the query directly to your dbms?

Are you sure that it is not returning any rows?

When possible, I liked to write the query in the dbms then rewrite it in code.

[Nebil]

I tried using join statement.And it only worked for the first join when it raised an exception->Syntax error (missing operator) in query expression ''.

I removed the second join statement and it works fine.And BTW i forgot one condition to put in the query then put it and it gave me the right result.

But what i want to know is why it is not working for the second join statement.Any ideas Begginnerdev?

"INSERT INTO patempTable SELECT pr.[project number],pr.[employee number],pr.[current month],pr.[net pay]," & _
        "al.[net allowance] FROM [pay_roll] pr INNER JOIN [allowance] al ON pr.[project number]=al.[project number] AND " & _
        "pr.[employee number]=al.[employee number] AND MONTH(pr.[current month])=MONTH(al.[current month]) AND YEAR(pr.[current month])=YEAR(al.[current month])"

The above statement joins the two tables [pay roll] and [allowance]

"JOIN [per_diem_accomodation] pda ON pr.[project number]=pda.[project number] AND " & _
        "pr.[employee number]=pda.[employee number] AND MONTH(pr.[current month])= MONTH(pda.[current month]) AND YEAR(pr.[current month])=YEAR(pd.[current month])"

When i add this statement to the previous code it raises an exception

[Eng.William]

am also having the same problem of outputting values from different tables on one crystal report