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;

Recommended Answers

All 14 Replies

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

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.

commented: my thoughts exactly +3

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.

Ok now post your current code so we can see the updated version

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;

You still have an '!' in your group by clause: DaySchedule![Shift Code Monday]

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.