How would i change this so it will work.
I need to link two tables.
but i get an error that says "Join expression not supported".

case 'N'://Get Jobs
                    //long n = Int64.Parse(Del);
                    SEL = "SELECT Employee.[Employee Number], Employee.[Last Name], " +
                            "[DaySchedule]![Shift Code Monday] AS Monday, " +
                            "[DaySchedule]![Shift Code Tuesday] AS Tuesday , " +
                            "[DaySchedule]![Shift Code Wednesday] AS Wednesday, " +
                            "[DaySchedule]![Shift Code Thursday] AS Thursday, " +
                            "[DaySchedule]![Shift Code Friday] AS Friday, " +
                            "[DaySchedule]![Shift Code Saturday] AS Saturday, " +
                            "[DaySchedule]![Shift Code Sunday] AS Sunday, " +
                            "DaySchedule.[Week Number] FROM Employee LEFT JOIN DaySchedule ON Employee.[Employee Number] " +
                            "WHERE Employee.[Employee Number] = '" + Del + "'";
                    break;

You forgot the '=' part after your left join on. You didn't not specify the key you want the field you want to join on both table.

SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.FirstName, Customers.LastName

You forgot the '=' part after your left join on. You didn't not specify the key you want the field you want to join on both table.

SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.FirstName, Customers.LastName

Does this look right.

case 'N'://Get Jobs
                    //long n = Int64.Parse(Del);
                    long n = Int64.Parse(Del);
                    SEL = "SELECT Employee.[Employee Number], Employee.[Last Name], DaySchedule![Shift Code Monday] AS Monday, " +
                            "DaySchedule![Shift Code Tuesday] AS Tuesday, " +
                            "DaySchedule![Shift Code Wednesday] AS Wednesday, " +
                            "DaySchedule![Shift Code Thursday] AS Thursday, " +
                            "DaySchedule![Shift Code Friday] AS Friday, " +
                            "DaySchedule![Shift Code Saturday] AS Saturday, " +
                            "DaySchedule![Shift Code Sunday] AS Sunday, " +
                            "DaySchedule.[Week Number], " +
                            "FROM (Employee LEFT JOIN DaySchedule ON Employee.[Employee Number] = DaySchedule.[Employee Number]) " +
                            "RIGHT JOIN Rules ON Employee.[Default Shift Code] = Rules.[Rule Name] " +
                            "WHERE (((Rules.[Rule Name])= " + n.ToString() +
                            "GROUP BY Employee.[Employee Number], Employee.[Last Name], DaySchedule![Shift Code Monday], " +
                            "DaySchedule.[Shift Code Tuesday], DaySchedule.[Shift Code Wednesday], DaySchedule.[Shift Code Thursday], " +
                            "DaySchedule.[Shift Code Friday], DaySchedule.[Shift Code Saturday], DaySchedule.[Week Number]";

i'm not any expert in sql, however it looks fine

Though now i get an error that says "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

If you're using SQL Server then your statement is wrong, not sire what the use of the ! is for

[DaySchedule]![Shift Code Monday] AS Monday,

That should look like this (if using SQL Server)

[DaySchedule].[Shift Code Monday] AS Monday,

Your entire statement is riddled with those.

Edited 6 Years Ago by PsychoCoder: n/a

Comments
my thoughts exactly

If you're using SQL Server then your statement is wrong, not sire what the use of the ! is for

[DaySchedule]![Shift Code Monday] AS Monday,

That should look like this (if using SQL Server)

[DaySchedule].[Shift Code Monday] AS Monday,

Your entire statement is riddled with those.

I thought i changed those. but those aside i get another error that says " data type mismatch in criteria expression".
but i cant find anything that is wrong with the statement.

case 'N'://Get Jobs
                SEL = "SELECT Employee.[Employee Number], Employee.[Last Name], " +
                            "DaySchedule.[Shift Code Monday] AS Monday " +
                            "DaySchedule![Shift Code Tuesday] AS Tuesday, " +
                            "DaySchedule![Shift Code Wednesday] AS Wednesday, " +
                            "DaySchedule![Shift Code Thursday] AS Thursday, " +
                            "DaySchedule![Shift Code Friday] AS Friday, " +
                            "DaySchedule![Shift Code Saturday] AS Saturday, " +
                            "DaySchedule![Shift Code Sunday] AS Sunday, " +
                            "DaySchedule.[Week Number] " +
"FROM (Employee LEFT JOIN DaySchedule ON Employee.[Employee Number] = DaySchedule.[Employee Number]) " +
"RIGHT JOIN Rules ON Employee.[Default Shift Code] = Rules.[Rule Name] " +
"WHERE Rules.[Rule Name] = '" + Del + "'";
"GROUP BY Employee.[Employee Number], Employee.[Last Name], DaySchedule![Shift Code Monday], " +
"DaySchedule.[Shift Code Tuesday], DaySchedule.[Shift Code Wednesday], DaySchedule.[Shift Code Thursday], " +
"DaySchedule.[Shift Code Friday], DaySchedule.[Shift Code Saturday], DaySchedule.[Shift Code Sunday], DaySchedule.[Week Number]";
                    break;

Ok you still have the ! in your query.

In this line

"WHERE Rules.[Rule Name] = '" + Del + "'"

What data type is Del and what type is Rules.[Rule Name] in your table.

For future reference, you really shouldn't have spaces in your column and table names.

Ok you still have the ! in your query.

In this line

"WHERE Rules.[Rule Name] = '" + Del + "'"

What data type is Del and what type is Rules.[Rule Name] in your table.

For future reference, you really shouldn't have spaces in your column and table names.

Ha ah, sorry ive been trying to figure this out all day. Got so caught up in it that i forgot within seconds to take them out.

But both Del and Rules.[Rule Name] are strings.

Okay with this code right here (see below), i get this error. that is with the changes psycho gave me.

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

case 'N'://Get Jobs
        SEL = "SELECT Employee.[Employee Number], Employee.[Last Name], " +
                            "DaySchedule.[Shift Code Monday] AS Monday " +
                            "DaySchedule.[Shift Code Tuesday] AS Tuesday, " +
                            "DaySchedule.[Shift Code Wednesday] AS Wednesday, " +
                            "DaySchedule.[Shift Code Thursday] AS Thursday, " +
                            "DaySchedule.[Shift Code Friday] AS Friday, " +
                            "DaySchedule.[Shift Code Saturday] AS Saturday, " +
                            "DaySchedule.[Shift Code Sunday] AS Sunday, " +
                            "DaySchedule.[Week Number] " +
                            "FROM (Employee LEFT JOIN DaySchedule ON Employee.[Employee Number] = DaySchedule.[Employee Number]) " +
"RIGHT JOIN Rules ON Employee.[Default Shift Code] = Rules.[Rule Name] " +
"WHERE Rules.[Rule Name] = '" + Del + "'" +
"GROUP BY Employee.[Employee Number], Employee.[Last Name], DaySchedule![Shift Code Monday], " +
"DaySchedule.[Shift Code Tuesday], DaySchedule.[Shift Code Wednesday], DaySchedule.[Shift Code Thursday], " +
"DaySchedule.[Shift Code Friday], DaySchedule.[Shift Code Saturday], DaySchedule.[Shift Code Sunday], DaySchedule.[Week Number]";
                    break;

You're missing a comma at the end of this line

"DaySchedule.[Shift Code Monday] AS Monday " +

It should be

"DaySchedule.[Shift Code Monday] AS Monday, " +

You're missing a comma at the end of this line

"DaySchedule.[Shift Code Monday] AS Monday " +

It should be

"DaySchedule.[Shift Code Monday] AS Monday, " +

Okay with this code right here (see below), i get this error. that is with the changes you gave me.

i still get "data type mismatch in criteria expression".

case 'N'://Get Jobs
        SEL = "SELECT Employee.[Employee Number], Employee.[Last Name], " +
                            "DaySchedule.[Shift Code Monday] AS Monday, " +
                            "DaySchedule.[Shift Code Tuesday] AS Tuesday, " +
                            "DaySchedule.[Shift Code Wednesday] AS Wednesday, " +
                            "DaySchedule.[Shift Code Thursday] AS Thursday, " +
                            "DaySchedule.[Shift Code Friday] AS Friday, " +
                            "DaySchedule.[Shift Code Saturday] AS Saturday, " +
                            "DaySchedule.[Shift Code Sunday] AS Sunday, " +
                            "DaySchedule.[Week Number] " +
                            "FROM (Employee LEFT JOIN DaySchedule ON Employee.[Employee Number] = DaySchedule.[Employee Number]) " +
"RIGHT JOIN Rules ON Employee.[Default Shift Code] = Rules.[Rule Name] " +
"WHERE Rules.[Rule Name] = '" + Del + "'" +
"GROUP BY Employee.[Employee Number], Employee.[Last Name], DaySchedule![Shift Code Monday], " +
"DaySchedule.[Shift Code Tuesday], DaySchedule.[Shift Code Wednesday], DaySchedule.[Shift Code Thursday], " +
"DaySchedule.[Shift Code Friday], DaySchedule.[Shift Code Saturday], DaySchedule.[Shift Code Sunday], DaySchedule.[Week Number]";
                    break;
This article has been dead for over six months. Start a new discussion instead.