Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: One query - TWO Tables!

    I have a query that executes using two tables.

    The query starts as . . .

    SELECT C.OfcName,

    runs some calculations, etc.

    and ends as . . .

    FROM COMMAK AS C, SALES AS S
    GROUP BY C.Ofcrep
    ORDER BY 10 DESC;

    This query works perfectly as it is, using the two tables.

    They are not joined and don't need to be.

    I have a pre designed report that executes the query and places the data from one table in the body of the report and the calculated fields results in a sub report on the last page. It all works great!

    However, I would like to allow the user to select the table names on the fly? The field structures never change.

    Thanks . . . Rick

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Rick Schreiber
    This query works perfectly as it is, using the two tables.
    i find this very hard to believe

    first of all, the GROUP BY is invalid, and Access won't run the query

    secondly, it's unlikely that the cross join (for such it is), is really what you want

    but more importantly, why would you want to pick the table names on the fly?

    if all the tables have the same structure, why are they separate tables?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    It was the query that you suggested about 6 or 8 months ago Rudy!

    Me, not understanding proper design parameters, I'm now trying to build my application from back to front.

    Isn't there a simple way to pass table names to a query?

    For almost a year no one has been able to get me past this point.

    I have the 1,400 page Access 2003 Bible and a VBA book also. But most of it is Greek to me.

    I'm joining the OCAUG (Orange County Access User Group soon).

    Thanks Rudy . . . Rick

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, sure, a likely story

    would you mind showing the complete query? and do you still have a link to the previous thread?

    as for "a simple way to pass table names to a query" the answer is no

    you shouldn't need to do this anyway, but instead, design your tables so that it is a column value that changes, not the table name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Complete Query

    Suggested by you in another forum board, Utter Access, Tek-Tips, Dev-shed or here.

    This is the completed working query.
    Code:
    SELECT C.OfcName, 
    Sum(IIf(S.ListName Like C.OfcName & "*" And S.SellName Like C.OfcName & "*",1,0))*2 AS WSO, Sum(IIf(S.ListName Like C.OfcName & "*" And S.SellName Like C.OfcName & "*",S.SalePrice,0))*2 AS WSODV, 
    Sum(IIf(S.ListName Like C.OfcName & "*" And Nz(S.SellName) Not Like C.OfcName & "*",1,0)) AS TSO, 
    Sum(IIf(S.ListName Like C.OfcName & "*" And Nz(S.SellName) Not Like C.OfcName & "*",S.SalePrice,0)) AS TSODV, 
    Sum(IIf(S.ListName Not Like C.OfcName & "*" And S.SellName Like C.OfcName & "*",1,0)) AS WST, Sum(IIf(S.ListName Not Like C.OfcName & "*" And S.SellName Like C.OfcName & "*",S.SalePrice,0)) AS WSTDV, 
    WSO+TSO+WST AS [Total Transactions], Sum(IIf(S.ListName Like C.OfcName "*",S.SalePrice,0)) AS [Listing Dollar Volume], 
    WSODV+TSODV+WSTDV AS [Listing and Sales Dollar Volume]
    FROM CBAll AS C, SCMLS AS S
    GROUP BY C.OfcName
    ORDER BY 10 DESC;
    You had much input on this. See pages around 42 in Tek-tips Access Queries forum.

    SCMLS has all of the data which changes only once a month.

    The CBAll table has about 18 office names. CBALL is one client. Each of my clients have their own small table with their respective offices, usually no more than about 10.

    I just need to run each clients table against the SCMLS table and print the results.

    What do you think?

    Rick

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    create a separate query for each client
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    How do you propose to have the user pick the table names on the fly?
    Probably with a form with a few combo boxes that contain the table names.
    In that case, you could use VBA to build a string containing the valid SQL,
    and put that string in the QueryDef SQL Look at Querdef in BOL ( or in your VB book) for a good explanation.
    Inspiration Through Fermentation

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by RedNeckGeek
    How do you propose to have the user pick the table names on the fly?
    he's trying to do it like this -- ListBox of table names
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the ONLY way I can see of doing this is to have a filter form which allows the user to select whatever parameters they require, then write the SQL on the fly, pulling accross those values when the report opens and use that SQL as the reports source. messy but it should work, ineffocient.. heck yes......

    ...mind you it samcks of poor design to do it htat way... but whatever works is often good enough.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Separate Quote . . .

    Quote Originally Posted by r937
    create a separate query for each client
    That is what I have in Paradox.

    I think this is a bad idea. If I have 25 clients, then I have 25 queries, 25 reports, 25 client tables, etc.

    I think it would be far easier and less suseptible to errors if I used one section of code for all of my clients. That way, I would only have to maintain one program, one report, and 25 small client tables of no more than maybe 12 ofcNames and one field per client table.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the source of your woes is the (in my opinion mistaken) design where each client gets a separate table

    use a single table and all your troubles simply evaporate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    One Client Table

    So you are saying that I should have ALL of my client office names in one table and either select or filter them according to each client? I like the idea!

    How am I going to know which office names are used by each of my clients?

    rick

  13. #13
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    To create a variable that is an object: BL

    Use the Dim statement:
    Dim c as clipboard

    Now you can access the properties of this object:
    me.text = c.text

    Is an Access Table considered an object?

    Rick

  14. #14
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    A table absolutely is an object. You can get ahold of it a variety of ways, one of which would be to check the AllTables() collection, another is via DAO.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Rick Schreiber
    How am I going to know which office names are used by each of my clients?
    couldn't you ask them?

    i'm sorry, i don't understand the question
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •