Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query problem (2000)

    Thanks for all the previous help. Here I am again folks. First can anyone tell me how I can attach a query to this forum? If I knew how to do that I would just let you look at my can of worms.

    On to an explanation: I created a query that will give me Item Name, Count of those Items sold, The cost of the item, the price of the item, I then subtract the cost from the price to give me a number I call "Item Contribution", then it gives me "Menu Mix" as a %, which is the number of that one item/s sold divided by all the items sold, then it gives me "Contribution to Margin" which is the quantity*Item Contribution/Total Sales.

    All seems to work well except, I get three of every Item and all three have a different quantity count. By building a simple query that give me the sum of count the I know that the three added together does equal the total number sold. I know this isn't much to go on, I am hoping that I have made a typical beginners error and you can give me a quick fix.

    Thanks,
    Bret

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query problem (2000)

    <hr>First can anyone tell me how I can attach a query to this forum? <hr>
    You can't attach a query. However, you can post the SQL for the query in the body of your post. Just open the query in design view, switch to the SQL view, copy the SQL you see and paste it into the post. From the rest of your post, I suspect your grouping on the wrong field, but you'll have to post the SQL before anyone will be able to say for certain what your problem is.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (2000)

    Okay here is the SQL from the problem query. The error that I get is that it does not give me the total of the individual items in one total. They may be listed two or three times.

    SELECT Inventory.ItemName, Sum(Invoice_Itemized.Quantity) AS Quantity, Inventory.Cost, Inventory.Price, [Price]-[Cost] AS ItemCM, [Quantity]/[Total units] AS [Menu Mix], ([Quantity]*[ItemCM])/[SumOfTotal_Price] AS [Contribution to Margin]
    FROM [# of units sold], [Total Sales], Invoice_Totals INNER JOIN (Invoice_Itemized INNER JOIN Inventory ON Invoice_Itemized.ItemNum = Inventory.ItemNum) ON Invoice_Totals.Invoice_Number = Invoice_Itemized.Invoice_Number
    GROUP BY Inventory.ItemName, Inventory.Cost, Inventory.Price, [Quantity]/[Total units], [Total Sales].SumOfTotal_Price, [# of units sold].[Total units];

    Thanks for the help. I am totally stumped.

    Bret

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query problem (2000)

    The problem is that every field you add to a totals query can cause multiple rows to be returned, such as when the same item may sell for different prices at different times. Including a price field then would cause a row to be returned for every different price for the same item name.

    Since you also appear to have a couple of unconnected tables in there ([# of units sold], [Total Sales]) You may be getting a cartesian product as well, especially since you don't have any implied joins on these tables.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (2000)

    Wow! Most of what you said just whizzed by. I can see what you are saying about the different prices and when I look at the results of the query you are correct the do return a different value. How do I fix this? Is there some method that I can tell it I don't care how much it sold for but that I do want the amount calculated, just tell me how many of this item name have been sold?

    Thanks,

    Bret

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query problem (2000)

    OK, to simplify it, don't have any tables in your query unless they have an explicit join to another table in that query. The SQL that you posted had two tables in it without any apparent join, so either your SQL got garbled or you have some independent tables in there. When you have tables that aren't restricted by a join to another table and that don't use criteria to limit the rows they return, then for each record from the rest of the query you will get that same record times however many rows there are in the unjoined table. That's a cartesian product, basically the number of records in one table or query times the number of records in the other.

    Try changing this part of the query<pre>Inventory.Cost, Inventory.Price, [Price]-[Cost] AS ItemCM</pre>

    to <pre>Sum([Inventory.Price - Inventory.Cost) AS ItemCM</pre>

    Dropping the individual Price and Cost fields will eliminate variations in those fields that could be causing multiple records.
    Charlotte

Posting Permissions

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