Results 1 to 9 of 9
  1. #1
    kieranharrison is offline True blue Aussie m8
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Location
    Queensland, Australia
    Posts
    28

    Question Help with SQL Query

    Hello,

    I am trying to write a query which displays multiple fields from different tables. I am having some issues executing the SQL code as it keeps coming up with " The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

    I have checked the spelling a few times and I believe it is right. I'm fairly new at this though so I'm sure it could be written/ done a better way.

    Code:
    SELECT [received_flight_files_table].Processing_Month AS ProcessingMonth, [received_flight_files_table].IATA_Code AS IATACode, [received_flight_files_table].Airline AS Airline, [received_flight_files_table].Received_Date AS ReceivedDate, [uploaded_flight_files_table].Uploaded_Date AS UploadedDate, [published_flight_table].Published_Date AS PublishedDate
    WHERE Format ([received_flight_files_table]Processing_Month, "mmyy") = [enter MMMYY];
    Many thanks

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't see a FROM clause.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    kieranharrison is offline True blue Aussie m8
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Location
    Queensland, Australia
    Posts
    28
    Apologies, I updated the code to the below but now have a new error - "The specified field 'Processing_Month' could refer to more than one table listed in the FROM clause"

    I'm confused as I thought the whole point of referring to the tables in the SELECT clause was to tell it specifically which table I want the field taken from?

    Code:
    SELECT [received_flight_files_table].Processing_Month AS ProcessingMonth, [received_flight_files_table].IATA_Code AS IATACode, [received_flight_files_table].Airline AS Airline, [received_flight_files_table].Received_Date AS ReceivedDate, [uploaded_flight_files_table].Uploaded_Date AS UploadedDate, [published_flight_table].Published_Date AS PublishedDate
    FROM received_flight_files_table, uploaded_flight_files_table, published_flight_table
    WHERE Format ([received_flight_files_table] (Processing_Month, "mmyy")) = [enter MMMYY];
    My end goal really is just to have the following in one query;

    - Processing_Month taken from the received_flight_files_table
    - IATA_Code taken from the received_flight_files_table
    - Airline taken from the received_flight_files_table
    - Received_Date taken from the received_flight_files_table
    - Uploaded_Date taken from the uploaded_flight_files_table
    - Published_Date taken from the published_flight_table

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why bother with alias field names?

    The WHERE clause syntax is wrong. Remove the table name. Or correctly prefix it to the field name.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    When you've fixed that issue you will have at least two others based on the sql in post #3
    1. Field Airline aliased by the same name which isn't allowed
    2. You have three tables with no linking fields AKA cartesian join. This will give you a record for each combination of records in the three tables
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    kieranharrison is offline True blue Aussie m8
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Location
    Queensland, Australia
    Posts
    28
    Good point June - i'll fix that up.

    Thanks isladogs, I think it's almost there now (see below).

    I have removed all of the alias field names, however, I think now in relation to your point 2 the result is not correct. I am getting way too many records returned than I am expecting.

    Code:
    SELECT [received_flight_files_table].Processing_Month, [received_flight_files_table].IATA_Code, [received_flight_files_table].Airline, [received_flight_files_table].Received_Date, [uploaded_flight_files_table].Uploaded_Date, [published_flight_table].Published_Date
    FROM received_flight_files_table, uploaded_flight_files_table, published_flight_table
    WHERE Format ([received_flight_files_table].Processing_Month, "mmyy") = [enter MMMYY]
    Obviously the statement is not correct yet but what I want to do is essentially use the received_flight_files_table as the base point, then add 2 other fields from 2 separate tables to that query. I am expecting there to be blanks in some of the fields. I think I am just missing some kind of match clause or something like that?

    Again, quite new to writing SQL statements as you can tell haha. Appreciate the help.

    Thanks,

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Are you not using query designer to help build correct query syntax?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    kieranharrison is offline True blue Aussie m8
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Location
    Queensland, Australia
    Posts
    28
    I've tried but got the same result as my post 6. Just found it a little easier to understand whats happening using SQL statement.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Do these tables have relationships with PK/FK links? There is no JOIN clause in that SQL. This will result in Cartesian output. Every record of each table will associate with every record of other tables. If each table had 3 records would mean 27 records in output.

    Again, use query designer then switch to SQLView to see statement.

    Provide sample data. If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums