Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Criteria in crosstab query. (Access 2000)

    I have a crosstab query that works fine in a given office. However when I collect all the data from the all offices I have to introduce additional criterion that selects the data only for a given office.I accomplish this with the field afid from the table customers.
    I have no problem in other cases, but in case with this query I have problems.The criteria seems not to work.I put the criteria for examle afid = 7 and collect the data from the other offices, nothing works properly. It is complicated because of my other criteria >=DLookUp("orderid";"orders";"audit=True") which are different for the different offices.In the end result I get a mess.Can you help ?
    In my example i have shown the data only for the one office.
    Attached Files Attached Files

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Criteria in crosstab query. (Access 2000)

    1) You write that the problem occurs if you have data from multiple offices, but the database that you attached contains data for a single office only. This means that we cannot test realistically.
    2) You write "nothing works properly" and "In the end result I get a mess" but you don't explain what you want the end result to be and in what way exactly it is wrong.
    3) What is the condition >=DLookUp("orderid","orders","audit=True") supposed to do? DLookup will look up an arbitrary orderid for which audit = True, you have no control over which one it looks up.

  4. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria in crosstab query. (Access 2000)

    Your questions made me think about my design and i am reconsidering it. Thank you.

  5. #4
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria in crosstab query. (Access 2000)

    The criteria
    >=DLookUp("orderid";"orders";"audit=True")

    was bult in order to start enumerating all the orderrs after the order with audit = True.I have built this query with your help in the past and it is an excellent query.In this way I get all the orders listed after I have made a revision, that is when the audit = True.
    I have tried to include in the attachement also cases from other offices, with different afid and then the results are different.
    Attached Files Attached Files

  6. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Criteria in crosstab query. (Access 2000)

    There are no relationships between the tables in your database, and as a result there are orders with customerids that do not exist in the customers table, and order detail records with productids that do not exist in the products table. Please see <post:=552,985>post 552,985</post:> and <post:=513,538>post 513,538</post:>.

    I think that you want to find the orderid for which audit = True and for which the afid of the customer of the order is equal to the specific afid you're filtering on. This can be done by using a subquery:

    PARAMETERS [Enter afid] Long;
    TRANSFORM Sum([order details].Quantity) AS Sum1
    SELECT [order details].ProductID, orders.orderid
    FROM products INNER JOIN ((Customers INNER JOIN orders ON Customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID) ON products.Productid = [order details].ProductID
    WHERE orders.orderid>=(SELECT Max(orders.orderid) FROM Customers INNER JOIN orders ON Customers.Customerid = orders.customerid WHERE orders.Audit=True AND Customers.afid=[Enter Afid]) AND Customers.afid=[Enter Afid]
    GROUP BY [order details].ProductID, orders.orderid, orders.orderid, orders.[required date]
    PIVOT orders.SubOrder;

    See the attached version (I have removed the orders for non-existing customers etc., and created relationships between the tables).
    Attached Files Attached Files

  7. #6
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria in crosstab query. (Access 2000)

    Thank you Hans for your remarks and notes for imporvement of the relationship.I think I have omitted to mention also the coloumn 0 in my query,since it is built with two coloumns, 1 and 0.
    I would like to explain why I need this query, which in fact for only one office works excellent. It is a great query and it was build with your help.
    My query refers to the warehouse. All the orders having audit = -1 or Suborder = -1 are considered as input, as goods having entered the warehouse and in the report are under the heading In.All the other orders are considered as having left the warehouse
    In my example : order 601201 and 601204 are with Audit = True and therefore it means that we have delivered in the warehouse 552 pieces.You can see that on my query in the attachement. On the other hand we have sold goods with orders 601206 and 601205 for 48 pieces and it means that in the warehouse we must have total 504 pieces fo this product.
    It is a great query and I owe it to you, it is so great and works without problems. The problems occur when I collect the data from different offices.I think if I could add the coloumn 0 somehow thing will get started here too.I do not understand quite how could it be done but it must be in the same way.
    In my attachement I do not need the table customers, but it is from there that I should take the afid and maybe the problems start form there ?
    Attached Files Attached Files

  8. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Criteria in crosstab query. (Access 2000)

    Since you need the afid, the database that you attached is useless because it doesn't contain afid.

  9. #8
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria in crosstab query. (Access 2000)

    Dear Hans,thank you for your reply and especially for your patience with me. I think that the whole solution is contained in your query which will do an excellent job and all I need is to adjust it a bit to my application.
    On my form I have the option box with the number of the offices and on choosing the office I press the control with the following event :
    DoCmd.OpenReport stDocName, acPreview, , "Afid = " & Me!Office
    The report is based on the query under question,in this case the query a you have sent to me.
    How could I rewrite your query so as instead of choosing the afid from the query to refer it to the afid chosen in the option box of the form ?

  10. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Criteria in crosstab query. (Access 2000)

    In the database I attached to , the query used a parameter [Enter Afid]. You can replace this with a reference to the option box on the form:

    [Forms]![frmSomething]![Office]

    where frmSomething is the name of the form. Note that you will have to declare this in Query | Parameters... too (while the query is open in design view).

  11. #10
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria in crosstab query. (Access 2000)

    Thank you very much for your suggestion. I have copied your query, renamed it qryDiff and tried to open the report rptDiff on the form FSurvey.I got the following message :
    The Microsoft Jet database does not recognize “ as a valid field name or expression
    Further, i think the field afid should also exist in the report in order to refer to it.However it is not possible for me to place this control. When i open the report in design view and look at the field list, it does not appear in this list.I tried to enter the afid once more in the query but the query does not accept it as a coloumn heading. so there is problem with entering the field afid in the report.How could i solve it ?
    Attached Files Attached Files

  12. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Criteria in crosstab query. (Access 2000)

    The condition is already included in the query, so you don't need to specify it in DoCmd.OpenReport:

    DoCmd.OpenReport StDocName, acViewPreview

    The problem is that depending on the data, the crosstab query might lack one of the column headings -1 and 0 but the report expects both to be present. The solution for this is to set the Column Headings property of the query:
    - Open the query in design view.
    - Click in an empty part of the upper half of the query window.
    - Activate the Properties window.
    - Enter the following in the Column Headings property: -1;0
    - Save and close the query.

    Note: your report is too wide for A4 paper. You should make it less wide (or decrease the left and right margins).

  13. #12
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria in crosstab query. (Access 2000)

    Thank you so much ! I have followed all your instructions, with the comand in hte form i open now the report, but it is empty with errors, while yor original query a, also included, is all right. I think the reason is that there is no control afid in the report. I canno place it, becasue i cannot find it in the field list. The field list contains only productid,orderid, -1 and 0.I cannot build another afid in the query as coloun heading since Access does not allow me.are there some other ways ?
    Attached Files Attached Files

  14. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Criteria in crosstab query. (Access 2000)

    The report is OK, but now you have removed the form FBenchmark which is necessary for the query (at your own request).

    The only afid for which the query returns any data is afid = 10. For all other values, the query returns no data and hence the report will be empty.

  15. #14
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria in crosstab query. (Access 2000)

    I am sorry i didnt include the form. I am sending now agan the db with the form.Customer 10 has indeed data, bu the customer under question id afid 7.
    If you click you query a and enter 7 then you will get the results. However if you choose option 7 from the office then i get no results in the report. Where am i wrong ?
    Attached Files Attached Files

  16. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Criteria in crosstab query. (Access 2000)

    You have forgotten to adjust the criteria for the orderid field in the query qryDiff. It should be

    >=(SELECT Max(orders.orderid) FROM Customers INNER JOIN orders ON Customers.Customerid = orders.customerid WHERE orders.Audit=True AND Customers.afid=Forms!FBenchmark!Office)

Page 1 of 2 12 LastLast

Posting Permissions

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