Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>First step to increase readability is to create a View for your tblItems that includes the fancy logic for Price, eg:</p> <p>View [vwItemsWithAdjustedCost]</p> <pre><code>SELECT ProductID, TradeApproved, IsAccessory, Cost, ModelNumber, ItemID, IIf( ( SupplierDiscount / 100 ) &lt;&gt; 0, Cost - ( Cost * ( SupplierDiscount / 100 ) ), Cost ) AS AdjustedCost FROM tblItems </code></pre> <p>View [vwItemsWithPrice]</p> <pre><code>SELECT ProductID, TradeApproved, IsAccessory, Cost, ModelNumber, ItemID, IIf( ( Markup / 100 ) &lt;&gt; 0, AdjustedCost * ( Markup / 100 ), 0 ) + AdjustedCost IIf( TradeApproved, TradeApprovedCost, 0 ) + ShippingCost AS Price FROM vwItemsWithAdjustedCost </code></pre> <p>Next you have to decide what the criteria is for picking one item out of the many that match the same ProductID, if 3 items have the same ID which one do you want to show!?</p> <p>As stated by Tom, an easy way is to just get the first (lowest) ID that matches, something like this:</p> <pre><code>SELECT P.ProductID, P.CategoryCode, P.ScaleTitle, P.Picture, IP.Cost, IP.ModelNumber, IP.ItemID, IP.Price FROM tblProducts P INNER JOIN ( SELECT ProductID, MIN( ItemID ) AS MinItemID FROM tblItems I GROUP BY ProductID ) S ON S.ProductID = P.ProductID INNER JOIN vwItemsWithPrice IP ON IP.ItemID = S.MinItemID WHERE P.CategoryCode = 'BS' AND IP.TradeApproved = 0 AND IP.IsAccessory = false ORDER BY IP.Price </code></pre> <p>This says for each ProductID, give me the first (lowest) ItemID from tblItems, and using that join to my view.</p> <p>Hope this helps!</p>
    singulars
    1. This table or related slice is empty.
    1. POSelect rows from query with a distinct foreign key?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USTimothy Walters
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for the answer, it's helping. I have tried your method but I'm still a little stuck. I have your last snippet but without the where and the order by and just select * for simplicity. I get a syntax error in access pointing to the final inner join on the view. I even tried substituting the view for just the items table so I knew there wouldn't be a problem but still the same error.
      singulars
      1. PO
      1. USddd
    2. COThe error is syntax error operator missing and shows the code S ON S.ProductID = P.ProductID INNER JOIN (SELECT ItemID from tblItems) IP ON IP.ItemID = S.MinItemID
      singulars
      1. PO
      1. USddd
    3. COYou're using Access, and I think it requires nesting of the joins (been a while since I used it, could be wrong), simply put brackets around the first join, FROM ( tblProducts P ... ON S.ProductID = P.ProductID ) INNER JOIN ...
      singulars
      1. PO
      1. USTimothy Walters
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload