473,322 Members | 1,480 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

query not working when i add an alias

a query is working at first,,then when i add an aliase to a table, it
doesnt work..this is using the SQL view for querying in MS Access 2002
(XP)........

for example

SELECT invoice_id, companies.name AS billing_company, total, date
FROM invoices INNER JOIN companies
ON invoices.billingcompany_id=companies.company_id

is working..but the following is not

SELECT invoice_id, companies.name AS billing_company, total, date
FROM invoices INNER JOIN companies AS C1
ON invoices.billingcompany_id=C1.company_id

access is giving me this error: Syntax error in JOIN operation. I am
new to SQL and even newer to Access.. can someone help?

Dec 25 '05 #1
1 9820
"sdot" <fl********@gmail.com> wrote in news:1135550670.035329.141090
@g43g2000cwa.googlegroups.com:
a query is working at first,,then when i add an aliase to a table, it
doesnt work..this is using the SQL view for querying in MS Access 2002
(XP)........

for example

SELECT invoice_id, companies.name AS billing_company, total, date
FROM invoices INNER JOIN companies
ON invoices.billingcompany_id=companies.company_id

is working..but the following is not

SELECT invoice_id, companies.name AS billing_company, total, date
FROM invoices INNER JOIN companies AS C1
ON invoices.billingcompany_id=C1.company_id

access is giving me this error: Syntax error in JOIN operation. I am
new to SQL and even newer to Access.. can someone help?


I think you'll need to alias the table all the places it's used, also in
the select clause

SELECT invoice_id, C1.name AS billing_company, total, date
FROM invoices INNER JOIN companies AS C1
ON invoices.billingcompany_id=C1.company_id

I would also recommend avoiding reserved words as names of fields (name,
date) ...
--
Roy-Vidar
Dec 25 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

20
by: | last post by:
If I need to check if a certain value does exist in a field, and return either "yes" or "not" which query would be the most effestive?
0
by: B. Fongo | last post by:
I learned MySQL last year without putting it into action; that is why I face trouble in formulating my queries. Were it a test, then you would have passed it, because your queries did help me...
6
by: mo | last post by:
I need to bring the ssn's into UniqueSups (supervisors) from tblNonNormalized. My inherited DB is not normalized and I find it extremely irritating due to the workarounds needed. I created...
2
by: Craig | last post by:
Acces 2000: I have several 'lookup' type tables with a standard field naming convention of id(pk) and name in a medium sized database. When I create a select query involving 3 or 4 of these tables...
2
by: Lyn | last post by:
Hi, How do you bind the output columns from a UNION query when the fields from the two tables have different names? Consider this query (WHERE clauses omitted)... SELECT SurnameBirth,...
3
by: Rodríguez Rodríguez, Pere | last post by:
Hello, I think I have found a query problem when the query has an alias for a table and use alias item and table name. I ilustrate the problem with a simple table and query. prr=# create...
6
by: sghi | last post by:
Hi All, I'm new to this group and quite new to access/vba. So, shortly after beginning to write a simple application for my wife, I came across a blocking problem: I need to intercept the sql...
0
by: Mahesh K. Anajni | last post by:
Hi We are working on a multilingual website. it has five URL (one original and four alias) for all the five languages. we are using RewriterRule in web.config to rewrite the Url. All this...
18
by: BurtonBach | last post by:
I am working to create some reports that find and calculate round times and costs related to trucking. The data is gathered from a tracking system and I cannot control the structure of the table. ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shćllîpôpď 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.