Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132

    "AND" or "&" in the FROM clause of a query

    I need to modify a copy of a query to add an additional table.

    The original "top part" of the query is below.



    Code:
    SELECT OrganizationsT.*, ContactsT.ContactFullName, ContactsT.PrimaryContact, ContactsT.PrimaryContactText, ContactsT.ContactPhone, ContactsT.ContactEmail, ContactsT.ContactAddress1, ContactsT.ContactAddress2, ContactsT.ContactCity, ContactsT.ContactState, ContactsT.ContactZip, ContactsT.ContactFirstLast
    FROM OrganizationsT LEFT JOIN ContactsT ON OrganizationsT.OrgName = ContactsT.ContactOrg
    It's allowing me to add the table to the SELECT portion: YPsT.*

    However, it's not letting me put an "&" or "and" clause in the FROM portion. I need it to be FROM (what is in the code now) and also FROM the YPsT.

    How would I get that in there?

    Thanks!!!

    --ak

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Try using the query builder to help you with the query.

  3. #3
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    I couldn't get the query builder to work on the original query because it doesn't support this join, so I can't add to it with the query builder, either.

    Many thanks, though, Rural Guy!!!!!!

  4. #4
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    Rural Guy,

    I tried it, just in case, and it won't let me add this particular table to the Query. Its "not in the list." I don't understand why I couldn't add any table I want to to a query, but I don't know all that much, either ....

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I'm not sure what is going on. It should allow you to add any table in your system to the query. What is different about this table?

  6. #6
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    Nothing -- it is the most basic table of them all. I can only think there's something about the LEFT JOIN that makes it beef about adding an additional table (that is not part of the join) --

    the FROM should be two parts:

    YPsT & [OrganizationsT LEFT JOIN ContactsT ON OrganizationsT.OrgName = ContactsT.ContactOrg], but it's like it doesn't want two parts.

    Or something!!!

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It should add a table even if there is no relationship with anything else in the query.

  8. #8
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    Okay, I am an idiot. I've got the YPsT in there, but now it's saying it doesn't support the join.

    And now I am getting a more explanatory message:

    The SQL statement could not be executed because it contains ambiguous outer joins. To force one fo the joisn to be performed first, create a separate query that performs the first join and then include that query in your SQL statement.

    So, if I used my original query (with the join) as the inserted query, would I do it like this:

    FROM YPsT, OriginalQueryQ

    Or is it inserted in another way????

    Thanks!

    --ak

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Does the YPsT table have any relationship with the other tables in the query? ie: does it have a field in common?

  10. #10
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    Hi, Rural Guy!

    The YPsT has no relationship with any of the other tables -- no fields in common.

    What goes on is this. The original report that is drawn from an existing query -- with the SELECT and FROM clauses (without the YPsT stuff) is called based on a simple WHERE statement -- WHERE the field in 'this' combo box on 'this' form = OrganizationsT.OrgName. It pulls a report for a single organization.

    I am trying to call the same form, but for several selected organizations. The selection of organizations is based upon a new WHERE clause that is dependent on YPsT. It is where, for example, they have the same choice in different fields (YPArts = True AND OrgArts = True).

    I was thinking of making a query of the original SELECT and FROM clauses (without the original no-longer-applicable WHERE) and add it to the SELECT? FROM? clause of a new query, along with adding the YPsT.* to SELECT and YPsT to FROM, and add the new WHERE clause.

    I'm not sure if I need the YPsT.* in the SELECT, because no YPsT info is on the report, but it has fields necessary to the WHERE clause.

    I hope that is helpful, and thank you so much for your help!

    --ak

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Since there is no field in common, I don't see how you expect Access to select some fields in the YPsT table. How is it supposed to line up the records?

  12. #12
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    It lines up the fields by comparing the value of a field in the YPsT to a value of field in the OrganizationsT. The report all of this sits on is a report that shows a YP and then lists all of the Orgs where there is a value match. For example, ((YPArts = True AND OrgArts = True) OR (YPBusiness = True AND OrgBusiness = TRUE).

    Beside each organization that satisfies the criteria, and is, thus, listed in the report, there is a button that opens an OrgReport that contains more detailed information about that Org. It is based upon the query that has the LEFT JOIN on the ContactsT. If you want to see the report for any organization, press the button. Currently, if you want to see the report for EVERY organzation, you must press the button, one at a time, beside each one.

    I want a button that will open that OrgReport for each organization listed -- making one aggregate report.

    So, the query I need a combination of the query that opens the matching report (OrgInterest = True AND YPInterest = True) and the query that creates the individual OrgReports (which is ContactsT & OrganizationsT, with a LEFT JOIN on the ContactsT).

    Access didn't like me combining them and adding "YPsT," to the LEFT JOIN statement in the FROM clause -- it said to make a query and insert it into the new query.

    Maybe there is a better way of doing it -- perhaps even a simple way -- that will make a button to aggregate those reports.

    Is that helpful?? I do appreciate your help very much!

    --ak

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I'm afraid I will not be of much further help in this thread. You've already exceeded my understanding of queries.

  14. #14
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    I've exceeded mine, also!!!!

    I really appreciate your time and consideration, Rural Guy! If I can figure out a solution, I'll post it.

    Many thanks!

  15. #15
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    "AND" or "&" in the FROM clause of a query
    Well, first off, you cannot use "and" or "or" in the "FROM" clause of a query. It is improper syntax.

    I don't really follow what you are trying to do, so this might be way out in left field...

    Two queries
    -----------
    You could create a query based on one or more joined tables. Call it "Qry1". Then create another query based on "Qry1". Call the second query "Qry2".
    So "Qry1" is the record source for "Qry2".

    Sub-query
    ----------
    You can create a query, "Qry2" that is based on "Qry1", but instead of using "Qry1", you use the SQL of "Qry1" in the FROM clause....
    SELECT Field1, Field2,.. FROM (SELECT * FROM Table1 INNER JOIN Table2 ON Table1.F1_PK = Table2.F1_FK WHERE SomeField = something)

    For me, since I don't use them every month, sub-queries are harder to create.

    ---------------
    You could use VBA to open the report and build up a where string for the where clause of the OpenReport command. The WHERE clause could be built using a multi-select list box (or an "ALL" button).

    My $0.02 worth...

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export "Query or Report" to a "Delimited Text File"
    By hawzmolly in forum Import/Export Data
    Replies: 3
    Last Post: 08-31-2012, 08:00 AM
  2. Replies: 1
    Last Post: 08-23-2012, 08:32 AM
  3. Replies: 11
    Last Post: 03-29-2012, 02:32 PM
  4. Custum Functions in Query "WHERE" Clause
    By trb5016 in forum Queries
    Replies: 1
    Last Post: 02-15-2012, 03:30 PM
  5. Replies: 16
    Last Post: 07-22-2011, 09:23 AM

Tags for this Thread

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