HELP WITH Access QUERY PLEASE :)

[Shawna Nathman]

Hi...I am trying to configure my access database with mysql. I have a few
tables that are linked together, and for some reason my sql is not
understanding my query. Which is:

SELECT Contracts.Ship, Contracts.ContractID, Contacts.FirstName,
Contacts.LastName, [Turnaround Ports].UniqueDay, PortCodes.Port AS
Startport, PortCodes_1.Port AS Endport
FROM (((Contracts INNER JOIN [Turnaround Ports] ON Contracts.Ship =
[Turnaround Ports].ship) INNER JOIN Contacts ON Contracts.EmployeeNumber =
Contacts.ContactID) LEFT JOIN PortCodes ON Contracts.SignOnPlace =
PortCodes.[Port Code]) LEFT JOIN PortCodes AS PortCodes_1 ON
Contracts.SignOffPlace = PortCodes_1.[Port Code]
WHERE (((Contracts.SignOn)<[retdate]) AND ((Contracts.SignOff)>[date]) AND
(([Turnaround Ports].DAYS)="1"));

Where is my syntax wrong?

Any help would be awesome and thanks!

 

[John W. Vinson]

Hi...I am trying to configure my access database with mysql. I have a few
tables that are linked together, and for some reason my sql is not
understanding my query. Which is:

SELECT Contracts.Ship, Contracts.ContractID, Contacts.FirstName,
Contacts.LastName, [Turnaround Ports].UniqueDay, PortCodes.Port AS
Startport, PortCodes_1.Port AS Endport
FROM (((Contracts INNER JOIN [Turnaround Ports] ON Contracts.Ship =
[Turnaround Ports].ship) INNER JOIN Contacts ON Contracts.EmployeeNumber =
Contacts.ContactID) LEFT JOIN PortCodes ON Contracts.SignOnPlace =
PortCodes.[Port Code]) LEFT JOIN PortCodes AS PortCodes_1 ON
Contracts.SignOffPlace = PortCodes_1.[Port Code]
WHERE (((Contracts.SignOn)<[retdate]) AND ((Contracts.SignOff)>[date]) AND
(([Turnaround Ports].DAYS)="1"));

Where is my syntax wrong?

Any help would be awesome and thanks!

What error message are you getting? What are the datatypes of the relevant
fields? Where and what are [retdate] and [date]?

 

[Shawna Nathman]

Hi!


We are getting an error message of there being a syntax error. The problem
with our access database is that one table can pull from 5 others to get
information. Our ultimate goal is to connect our access database with our
website through Joomla so that we can update our cruise ship openings here
and have it pull automatically off the website.

The data types are combo boxes. In our access database their are several
combo boxes that are relational to pulling data from other tables. When
uploading the access table to mysql the combo box fields essentially turn to
ID numbers and not information and are no longer linked in another sense to
the other tables.

The error message is as follows....

SELECT Contracts.Ship, Contracts.ContractID, Contacts.FirstName,
Contacts.LastName, [Turnaround Ports].UniqueDay, PortCodes.Port AS
Startport, PortCodes_1.Port AS Endport
FROM (
(
(
Contracts
INNER JOIN [Turnaround Ports] ON Contracts.Ship = [Turnaround Ports].ship
)
INNER JOIN Contacts ON Contracts.EmployeeNumber = Contacts.ContactID
)
LEFT JOIN PortCodes ON Contracts.SignOnPlace = PortCodes.[Port Code]
)
LEFT JOIN PortCodes AS PortCodes_1 ON Contracts.SignOffPlace =
PortCodes_1.[Port Code]
WHERE (
(
(
Contracts.SignOn
) < [retdate]
)
AND (
(
Contracts.SignOff
) > [date]
)
AND (
(
[Turnaround Ports].DAYS
) = "1"
)
)
LIMIT 0 , 30
MySQL said:
<http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html>
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'[Turnaround Ports].UniqueDay, PortCodes.Port AS Startport, PortCodes_1.Port
AS E' at line 1


Thanks!
Shawna

 

[John W. Vinson]

Hi!


We are getting an error message of there being a syntax error. The problem
with our access database is that one table can pull from 5 others to get
information.

No. It can't.

A table is a repository for data. It cannot and will not "pull" data from
anywhere!

You can PUSH data into a table with an append or update query, or you can use
a five-table query joining multiple tables... but again, a table can't "pull".

Our ultimate goal is to connect our access database with our
website through Joomla so that we can update our cruise ship openings here
and have it pull automatically off the website.

That would require some VBA code to "scrape" data from the website and insert
it into the table.

The data types are combo boxes. In our access database their are several
combo boxes that are relational to pulling data from other tables. When
uploading the access table to mysql the combo box fields essentially turn to
ID numbers and not information and are no longer linked in another sense to
the other tables.

You're another victim of Microsoft's misleading, misdesigned, infuriating
Lookup Wizard. The table has never had anything BUT the ID numbers in it! That
simple fact was concealed from your view by the (stupid!) combo boxes.

The error message is as follows....

SELECT Contracts.Ship, Contracts.ContractID, Contacts.FirstName,
Contacts.LastName, [Turnaround Ports].UniqueDay, PortCodes.Port AS
Startport, PortCodes_1.Port AS Endport
FROM (
(
(
Contracts
INNER JOIN [Turnaround Ports] ON Contracts.Ship = [Turnaround Ports].ship
)
INNER JOIN Contacts ON Contracts.EmployeeNumber = Contacts.ContactID
)
LEFT JOIN PortCodes ON Contracts.SignOnPlace = PortCodes.[Port Code]
)
LEFT JOIN PortCodes AS PortCodes_1 ON Contracts.SignOffPlace =
PortCodes_1.[Port Code]
WHERE (
(
(
Contracts.SignOn
) < [retdate]
)
AND (
(
Contracts.SignOff
) > [date]
)
AND (
(
[Turnaround Ports].DAYS
) = "1"
)
)
LIMIT 0 , 30
MySQL said:
<http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html>
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'[Turnaround Ports].UniqueDay, PortCodes.Port AS Startport, PortCodes_1.Port
AS E' at line 1

Are you running this query in MySQL, or in Access using MySQL as the (ODBC
linked?) backend? It appears to be a MySQL error message, not an Access
message.

 

[Shawna Nathman]

I am running this query in Mysql and getting the error message there. The
query always worked and still does in Access which is linked to mysql via
ODBC.

Hi!


We are getting an error message of there being a syntax error. The problem
with our access database is that one table can pull from 5 others to get
information.

No. It can't.

A table is a repository for data. It cannot and will not "pull" data from
anywhere!

You can PUSH data into a table with an append or update query, or you can use
a five-table query joining multiple tables... but again, a table can't "pull".

Our ultimate goal is to connect our access database with our
website through Joomla so that we can update our cruise ship openings here
and have it pull automatically off the website.

That would require some VBA code to "scrape" data from the website and insert
it into the table.

The data types are combo boxes. In our access database their are several
combo boxes that are relational to pulling data from other tables. When
uploading the access table to mysql the combo box fields essentially turn to
ID numbers and not information and are no longer linked in another sense to
the other tables.

You're another victim of Microsoft's misleading, misdesigned, infuriating
Lookup Wizard. The table has never had anything BUT the ID numbers in it! That
simple fact was concealed from your view by the (stupid!) combo boxes.

The error message is as follows....

SELECT Contracts.Ship, Contracts.ContractID, Contacts.FirstName,
Contacts.LastName, [Turnaround Ports].UniqueDay, PortCodes.Port AS
Startport, PortCodes_1.Port AS Endport
FROM (
(
(
Contracts
INNER JOIN [Turnaround Ports] ON Contracts.Ship = [Turnaround Ports].ship
)
INNER JOIN Contacts ON Contracts.EmployeeNumber = Contacts.ContactID
)
LEFT JOIN PortCodes ON Contracts.SignOnPlace = PortCodes.[Port Code]
)
LEFT JOIN PortCodes AS PortCodes_1 ON Contracts.SignOffPlace =
PortCodes_1.[Port Code]
WHERE (
(
(
Contracts.SignOn
) < [retdate]
)
AND (
(
Contracts.SignOff
) > [date]
)
AND (
(
[Turnaround Ports].DAYS
) = "1"
)
)
LIMIT 0 , 30
MySQL said:
<http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html>
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'[Turnaround Ports].UniqueDay, PortCodes.Port AS Startport, PortCodes_1.Port
AS E' at line 1

Are you running this query in MySQL, or in Access using MySQL as the (ODBC
linked?) backend? It appears to be a MySQL error message, not an Access
message.

 

[John W. Vinson]

I am running this query in Mysql and getting the error message there. The
query always worked and still does in Access which is linked to mysql via
ODBC.

I'm not familiar enough with MySQL's dialect of SQL to advise, other than to
say that *it is different enough* to cause this kind of problem. You did not
say that you were doing this in a MySQL environment and my telepathy must have
been momentarily on the blink. I'd suggest you ask in a MySQL support forum.

 

[Marco Pagliero]

The error message is as follows....

SELECT Contracts.Ship, Contracts.ContractID, Contacts.FirstName,
Contacts.LastName, [Turnaround Ports].UniqueDay, PortCodes.Port AS
Startport, PortCodes_1.Port AS Endport
FROM (

#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'[Turnaround Ports].UniqueDay, PortCodes.Port AS Startport, PortCodes_1.Port
AS E' at line 1

Yes, you should check the MySql documentation.

But the problem in this case will be that the right way of quoting
table names with illegal characters is not the bracket [] but the
double quote resp. backtick depending on the setting of MySql:
Contacts.LastName, `Turnaround Ports`.UniqueDay

http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html
"One area of concern is quoting of table names; While Access uses
square brackets (i.e. SELECT myfield FROM [my table]), MySQL instead
uses back-ticks (i.e. SELECT myfield FROM `my table`). When possible
it is best to avoid using table names that require quoting. When this
is not possible you will need to change your queries accordingly."

 

[umberto greco]

HELP WITH Access QUERY PLEASE
"Shawna Nathman" <[email protected]> ha scritto nel messaggio Hi...I am trying to configure my access database with mysql. I have a few tables that are linked together, and for some reason my sql is not understanding my query. Which is:

SELECT Contracts.Ship, Contracts.ContractID, Contacts.FirstName, Contacts.LastName, [Turnaround Ports].UniqueDay, PortCodes.Port AS Startport, PortCodes_1.Port AS Endport
FROM (((Contracts INNER JOIN [Turnaround Ports] ON Contracts.Ship = [Turnaround Ports].ship) INNER JOIN Contacts ON Contracts.EmployeeNumber = Contacts.ContactID) LEFT JOIN PortCodes ON Contracts.SignOnPlace = PortCodes.[Port Code]) LEFT JOIN PortCodes AS PortCodes_1 ON Contracts.SignOffPlace = PortCodes_1.[Port Code]
WHERE (((Contracts.SignOn)<[retdate]) AND ((Contracts.SignOff)>[date]) AND (([Turnaround Ports].DAYS)="1"));

Where is my syntax wrong?

Any help would be awesome and thanks!

 

[Risse]

Hi!


We are getting an error message of there being a syntax error. The problem
with our access database is that one table can pull from 5 others to get
information. Our ultimate goal is to connect our access database with our
website through Joomla so that we can update our cruise ship openings here
and have it pull automatically off the website.

The data types are combo boxes. In our access database their are several
combo boxes that are relational to pulling data from other tables. When
uploading the access table to mysql the combo box fields essentially turn
to
ID numbers and not information and are no longer linked in another sense
to
the other tables.

The error message is as follows....

SELECT Contracts.Ship, Contracts.ContractID, Contacts.FirstName,
Contacts.LastName, [Turnaround Ports].UniqueDay, PortCodes.Port AS
Startport, PortCodes_1.Port AS Endport
FROM (
(
(
Contracts
INNER JOIN [Turnaround Ports] ON Contracts.Ship = [Turnaround Ports].ship
)
INNER JOIN Contacts ON Contracts.EmployeeNumber = Contacts.ContactID
)
LEFT JOIN PortCodes ON Contracts.SignOnPlace = PortCodes.[Port Code]
)
LEFT JOIN PortCodes AS PortCodes_1 ON Contracts.SignOffPlace =
PortCodes_1.[Port Code]
WHERE (
(
(
Contracts.SignOn
) < [retdate]
)
AND (
(
Contracts.SignOff
) > [date]
)
AND (
(
[Turnaround Ports].DAYS
) = "1"
)
)
LIMIT 0 , 30
MySQL said:
<http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html>
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'[Turnaround Ports].UniqueDay, PortCodes.Port AS Startport,
PortCodes_1.Port
AS E' at line 1


Thanks!
Shawna

What error message are you getting? What are the datatypes of the
relevant
fields? Where and what are [retdate] and [date]?